[HACKERS] ORDER BY different locales for 8.0
Hello, the nls_string function that makes it possible to sort by arbitrary locale has been updated to reflect the changes in error handling in PostgreSQL 8.0, due to users using the nls_string sorting on 7.4 and requesting it for 8.0 as well. The distribution can be downloaded from http://www.fi.muni.cz/~adelton/l10n/ http://www.fi.muni.cz/~adelton/l10n/postgresql-nls-string/postgresql-nls-string-8.01.tar.gz I'll appreciate any comments. The README is as follows: - Why this function: PostgreSQL, at least until version 8.0, has rather weak support for various collating sequences -- what you get when you do select ... order by column. The sorting is closely tied to indexes used throughout the database cluster and is specified by locale settings at the initdb time. Yet, people asked for ways of specifying the collating rules at runtime, even if the sorting will not use indexes. Just take the records and sort them. It is reasonable request to want one select to order by using English rules, another one to run with German rules and yet another with Czech ones, without having to dump, initdb, restore. How it works: In this distribution you will find file nls_string.c. It contains the definition of function nls_string(text, text) which takes a string parameter and a locale name and returns string describing the ordering. So you can run select * from table order by nls_string(name, 'en_US.UTF-8') or select * from table order by nls_string(name, 'cs_CZ.UTF-8') or select * from table order by nls_string(name, 'C') and get what you expect -- the result is sorted the same way as it would be with LC_COLLATE=locate sort on the command line. Internally, the function sets the locale for LC_COLLATE category, runs strxfrm on the first parameter and encodes the result as octal values. Thus, it depends on your PostgreSQL collate setting (that which you did upon initdb, you can check it with show lc_collate) to sort numbers in the natural way. I believe this is reasonable assumption. Installation: Please check the INSTALL file. - Versions: This version of nls_string targets PostgreSQL server in version 8.0+. To use nls_string on version 7.4, download nls_string 0.53. - Bugs and ToDo: If your default collation settings does not sort numbers in the natural way (eg., 0123 is not sorted before 1234), the nls_string will not work. Nonetheless, the function does the work for me. Support, bug reports: This piece of software is provided as-is, in the hope that you will find it useful. However, no warranty is provided. I appreciate any bug reports, enhancement suggestions and patches. Please, _please_, use a meaningful Subject line and describe the situation in detail. Also make sure you've read and understood this README and the PostgreSQL documentation concerning C-language functions. I will not be helpful with installation problems if you did not read the documentation. --- If it works for you: If the function works for you, I'd appreciate a message from you. Just curious for what tasks people use the software. - Available: http://www.fi.muni.cz/~adelton/l10n/ -- Author: Copyright: (c) 2004--2005 Jan Pazdziora, [EMAIL PROTECTED] All rights reserved. Permission to use, distribute, modify, an copy this software and this documentation for any purpose is hereby granted. Contributors: Karel Zak [EMAIL PROTECTED] ftp://ftp2.zf.jcu.cz/users/zakkr/pg/ -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
On Tue, Aug 17, 2004 at 02:56:19PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Doing it only for interactive mode seems too error-prone to me (it works in psql, but not from my script). You're missing the point: a script cannot safely work this way. A human typing at the terminal can notice that his command failed and react to that, but a psql script cannot. It is not just a typo that you make in the SQL command. You often need to do insert into table which has primary key if the insert failed, do update of the existing record It should be upto the application to decide if the failed insert should lead to abortion of the transaction, or if it should be silently ignored and based on the return value of the insert proceed with update. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Does psql use nested transactions?
On Wed, Aug 18, 2004 at 02:47:26PM +0800, Christopher Kings-Lynne wrote: It is not just a typo that you make in the SQL command. You often need to do insert into table which has primary key if the insert failed, do update of the existing record Do the update first then the insert. That can still fail as concurrent session might run the same series of update (which affects 0 records) and insert (which will fail). Anyway, this was just an example of ways of using the database server to do part of the work -- letting the database server do the checks for you, raise an exception for you which you (your application) can test and happily ignore. It's not just the insert / update thing. The same goes for foreign keys, checks, anything where you knowingly run a statement which can fail, and you act based on the exception you get, _continuing_ with your transaction. This way, large part of the application logic is shifted to the server and to the database schema. The client just tries if the command will run OK. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? Oracle uses savepoints: SAVEPOINT savepointname; creates a savepoint or shifts existing savepoint of the same name; ROLLBACK TO savepointname; rolls back to savepoint (more verbose syntax also available); The syntax of handling exceptions is (in PL/SQL): BEGIN some code, for example a bunch of SQL commands; EXCEPTION WHEN nameofexception THEN handle the exception, maybe ROLLBACK; END; There are predefined exceptions like INVALID_NUMBER, NO_DATA_FOUND, ZERO_DIVIDE, or OTHERS. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Improving postgresql.conf
On Wed, Jun 09, 2004 at 09:13:05PM +0530, Shridhar Daithankar wrote: Well that is easy. In the service file just say [Cluster1] datapath=/data/foo [Cluster2] datapath=/data/foo1 and postgresql.conf could still reside inside each cluster to provide specific configuration. Thenhave a script which can say 'service postgresql cluster1 start' This is awfull way of doing configuration. Why should different installation share anything, in one file? Running /usr/bin/pg_ctl -C /etc/postgres.isp1.conf start seems much more maintainable. And /etc/postgres.isp1.conf can specify that the data files are in /bigdisk/data/isp1x or wherever you please. Postgresql as a database server is a service. A cluster is an service instance. A service configuration file documents all service instances and their parameters required for all tuning and control purposes. Add a possibility of multiple versions of postgresql on same box. That sums it up One file does not add possibility of multiple versions of postgresql on same box, it merely makes it harder. Well, I wish I could have some archives link handy but suffice to say that Tom has rejected this idea many times before.. That does not necessarily mean the idea is broken. Tom's main objection (IIRC) was that he needs to be able to have multiple postgresqls on one machine. That can easily be achieved, either by specifying datadirectory in the configuration file, or even defaulting to the same directory where the .conf file is stored when no datadirectory option is used. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Improving postgresql.conf
On Wed, Jun 09, 2004 at 07:53:19PM +0530, Shridhar Daithankar wrote: Well, the statement 'postgresql.conf outside data directory' isn't going to win I think. One day there won't be any data directory because the data will be on raw partitions. Then you will _have_ to have the configuration somewhere else. Ideally, only the absolute minimum of parameters needed to start the server ought to be in the external configuration. Everything else may happily reside within the database storage. Let me put it in a different way. What you are asking is a service configuration file. It is *not* same as current postgresql configuration file. It will/should be unique to a perticular installation of postgresql. i.e. something like /etc/postgresql/7.4.2/service.conf Why? The administrator may want to run second cluster on the same machine, share a couple of options using include directive while preserving separate configuration, including the location of data store, for things that should be different for each of these clusters. I think it is a rather good idea to add service configuration to default What is that service you mention? Also pulling postgresql.conf out of cluster has a drawback. All the clusters would have to share same tuning parameters which is not exactly ideal. Why would they _have_ to? Pulling postgresql.conf out of cluster only means that the file resides somewhere else and in it the location of the data directory is specified. It does not mandate there will only be one cluster and it does not mean that each cluster cannot have completely different configuration file. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Refined LC_COLLATE or multiple database clusters?
On Wed, Jun 09, 2004 at 12:33:03PM +0200, Grega Bremec wrote:
Collate order for those databases, however, needs to be different. Obviously,
[...]
Is it possible to do either of these things that could solve this problem
adequately:
- somehow manage to make one postmaster run on top of two separate
database clusters that would each have a different collate ordering
scheme
- use some other method of initializing one database from a different
template and taking with it LC_COLLATE setting (I suppose not, as
the ${PGDATA}/global/ directory is global to the cluster)
- use a patch that would add such functionality or upgrade to a version
(even if release-candidate, beta is not really an option, i gather)
of PostgreSQL that supported it
- in absence of any other viable solution, change the global setting of
the database cluster without having to dump/reinitdb/restore it
If you do not need the collating sequence to affect index operations,
you can use nls_string function to sort using
order by nls_string(column, 'sl_SI.utf-8')
where any locale can be specified in the runtime. The nls_string
result can also be used to compare strings in collating manner, however,
indexes will not be used in that case, which may or may not be a
problem for your usage.
The source of nls_string with installation instructions can be found
at
http://www.fi.muni.cz/~adelton/l10n/
--
Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/
.project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
Only self-confident people can be simple.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL configuration
On Thu, Apr 08, 2004 at 11:32:19AM -0400, Tom Lane wrote: A counterexample of Apache shows that you can easily use -f or another command line option to point the server to alternate master config file (which I believe is the same with MySQL). According to http://www.mysql.com/documentation/mysql/bychapter/manual_Using_MySQL_Programs.html#Option_files /etc/my.cnf will be read if it exists, no matter what you say on the command line. So AFAICS the only way to make a private installation is to make sure that you have overridden each and every setting in :-) I never used that feature so was never bitten by it. Anyway, Apache HTTP server seems to do it the right way, doesn't it? -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] locale
On Wed, Apr 07, 2004 at 03:40:57PM -0400, Tom Lane wrote: In practice, we know that we have seen index failures from altering the locale settings (back before we installed the code that locks down LC_COLLATE/LC_CTYPE at initdb time). I do not recall having heard any Cannot the same failure happen if one upgrades their glibc / locales and the new version implements the locale differently? Perhaps fixing previous bug, or simply producing different results for strcoll / strxfrm? If PostgreSQL depends on external locale information for something as important as indexes, shouldn't it make elementary checks (upon startup, perhaps) that the current locale settings and the current locale version produces results compatible with the existing indexes? And if it does not, reindex? -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL configuration
On Thu, Apr 08, 2004 at 10:31:44AM -0400, Tom Lane wrote: I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. A counterexample of Apache shows that you can easily use -f or another command line option to point the server to alternate master config file (which I believe is the same with MySQL). From that config files, another files can be included, making it easy to share pieces of configuration, or separate them in any way. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Arbitrary collation support for PostgreSQL
Hello all, PostgreSQL, at least until version 7.4, has rather weak support for various collating sequences. What you get when you do select * from table order by column is hardcoded in the database cluster at the initdb time. Yet, it is reasonable request to want one select to order by using English rules, another one to run with German rules and yet another with Czech ones, without having to dump, initdb, restore. The distribution http://www.fi.muni.cz/~adelton/l10n/postgresql-nls-string-0.50.tar.gz defines a function nls_string which allows collation to be set at runtime: select * from table order by nls_string(name, 'en_US.UTF-8') select * from table order by nls_string(name, 'cs_CZ.UTF-8') select * from table order by nls_string(name, 'C') The README and INSTALL files with more details are included in the distribution. I'd appreciate comments about the viability of the goal, name, and / or solution presented. Yours, -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
