Thread: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
Hi, i have the following problem, text ordering seems to behave incosistently across various lc_collate values, OS'es, PostgreSQLversions. Some behaviour might be expected, some not, thats why i am asking to see where i stand with this. Test Data postgres@dynacom=# SELECT * from test_sort_order; fooname ------------------------- Cylinder head cover No1 Cylinder Liner No1 Cylinder head No1 (3 rows) Now the query # SELECT * from test_sort_order order by fooname; in PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu (lc_collate=en_US.UTF-8) gives fooname ------------------------- Cylinder head cover No1 Cylinder head No1 Cylinder Liner No1 while in all of PostgreSQL 8.3.3 on i686-pc-linux-gnu (lc_collate=C) PostgreSQL 8.3.7 (lc_collate=el_GR.UTF-8) on i386-unknown-freebsd6.3 PostgreSQL 8.4.1 on x86_64-unknown-freebsd8.0 (lc_collate=en_US.UTF-8), gives fooname ------------------------- Cylinder Liner No1 Cylinder head No1 Cylinder head cover No1 Database encoding is SQL_ASCII in all four cases. -- Achilleas Mantzios
It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD are two different stories, hence the seen results. (Switching LC_COLLATE between en_US.UTF-8 and POSIX, in FreeBSD it does not have any impact on orderring while in Linux does) I think i have resolved the issue, so the solution boils down to setting LC_COLLATE=C in our linux central production postgresqlserver. What is the shortest way to change the default locale on a production installation running PostgreSQL-8.3.9? Is there anything less painful than dump, initdb,restore? I know 8.4 allows for per-db locale settings, i am just asking if there is a way to solve the issue now without waiting till the migration to 8.4 Thanx! Στις Tuesday 02 February 2010 14:26:44 ο/η Achilleas Mantzios έγραψε: > Hi, i have the following problem, text ordering seems to behave incosistently across various lc_collate values, OS'es,PostgreSQL versions. > Some behaviour might be expected, some not, thats why i am asking to see where i stand with this. > Test Data > postgres@dynacom=# SELECT * from test_sort_order; > fooname > ------------------------- > Cylinder head cover No1 > Cylinder Liner No1 > Cylinder head No1 > (3 rows) > > Now the query > # SELECT * from test_sort_order order by fooname; > in PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu (lc_collate=en_US.UTF-8) gives > fooname > ------------------------- > Cylinder head cover No1 > Cylinder head No1 > Cylinder Liner No1 > > while in all of > PostgreSQL 8.3.3 on i686-pc-linux-gnu (lc_collate=C) > PostgreSQL 8.3.7 (lc_collate=el_GR.UTF-8) on i386-unknown-freebsd6.3 > PostgreSQL 8.4.1 on x86_64-unknown-freebsd8.0 (lc_collate=en_US.UTF-8), gives > fooname > ------------------------- > Cylinder Liner No1 > Cylinder head No1 > Cylinder head cover No1 > > Database encoding is SQL_ASCII in all four cases. > > -- > Achilleas Mantzios > -- Achilleas Mantzios
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > What is the shortest way to change the default locale on a production installation running PostgreSQL-8.3.9? > Is there anything less painful than dump, initdb,restore? No :-( regards, tom lane
Στις Tuesday 02 February 2010 17:37:05 ο/η Tom Lane έγραψε: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > > What is the shortest way to change the default locale on a production installation running PostgreSQL-8.3.9? > > Is there anything less painful than dump, initdb,restore? > > No :-( > Thanx Tom, taking into account that the large and most important DB in our installation (over 100Gb as SQL dump) lives in a tablespaceof its own, is there a way to "cheat" postgresql after initdb in order to see this tablespace and the database in it, avoiding the wholecycle? i think this is not a good idea, i just thought i could ask, just to make sure. > regards, tom lane > -- Achilleas Mantzios
On tis, 2010-02-02 at 17:28 +0200, Achilleas Mantzios wrote: > It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD > are two different stories, hence the seen results. The UTF-8 locales on FreeBSD and Mac OS X are pretty much broken. This is becoming a FAQ ...
Στις Wednesday 03 February 2010 16:53:20 ο/η Peter Eisentraut έγραψε: > On tis, 2010-02-02 at 17:28 +0200, Achilleas Mantzios wrote: > > It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD > > are two different stories, hence the seen results. > > The UTF-8 locales on FreeBSD and Mac OS X are pretty much broken. > > This is becoming a FAQ ... This is a libc/locale combination issue. Care to elaborate? AFAICT for my case in FreeBSD, the greek UTF locale el_GR.UTF-8 behaves as expected without problems. > > -- Achilleas Mantzios
On ons, 2010-02-03 at 17:05 +0200, Achilleas Mantzios wrote: > Στις Wednesday 03 February 2010 16:53:20 ο/η Peter Eisentraut έγραψε: > > On tis, 2010-02-02 at 17:28 +0200, Achilleas Mantzios wrote: > > > It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD > > > are two different stories, hence the seen results. > > > > The UTF-8 locales on FreeBSD and Mac OS X are pretty much broken. > > > > This is becoming a FAQ ... > > This is a libc/locale combination issue. Care to elaborate? > AFAICT for my case in FreeBSD, the greek UTF locale el_GR.UTF-8 behaves as expected without problems. Well, """ while in all of PostgreSQL 8.3.3 on i686-pc-linux-gnu (lc_collate=C) PostgreSQL 8.3.7 (lc_collate=el_GR.UTF-8) on i386-unknown-freebsd6.3 PostgreSQL 8.4.1 on x86_64-unknown-freebsd8.0 (lc_collate=en_US.UTF-8), gives fooname ------------------------- Cylinder Liner No1 Cylinder head No1 Cylinder head cover No1 """ is not a correct sorting result in my book, unless your measure of correctness is ASCII byte value order. And then it's left as an exercise to determine what the results would be for texts involving non-ASCII letters.
Στις Wednesday 03 February 2010 21:16:36 ο/η Peter Eisentraut έγραψε: > On ons, 2010-02-03 at 17:05 +0200, Achilleas Mantzios wrote: > > Στις Wednesday 03 February 2010 16:53:20 ο/η Peter Eisentraut έγραψε: > > > On tis, 2010-02-02 at 17:28 +0200, Achilleas Mantzios wrote: > > > > It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD > > > > are two different stories, hence the seen results. > > > > > > The UTF-8 locales on FreeBSD and Mac OS X are pretty much broken. > > > > > > This is becoming a FAQ ... ^^^^ I guess you are right. In my new FreeBSD system i get: postgres@smadevnew:~> uname -sr FreeBSD 8.0-RELEASE-p2 postgres@smadevnew:~> ls -l /usr/share/locale/en_US.UTF-8/ total 0 lrwxr-xr-x 1 root wheel 28 Nov 21 16:30 LC_COLLATE -> ../la_LN.US-ASCII/LC_COLLATE lrwxr-xr-x 1 root wheel 17 Nov 21 16:30 LC_CTYPE -> ../UTF-8/LC_CTYPE lrwxr-xr-x 1 root wheel 30 Nov 21 16:30 LC_MESSAGES -> ../en_US.ISO8859-1/LC_MESSAGES lrwxr-xr-x 1 root wheel 30 Nov 21 16:30 LC_MONETARY -> ../en_US.ISO8859-1/LC_MONETARY lrwxr-xr-x 1 root wheel 29 Nov 21 16:30 LC_NUMERIC -> ../en_US.ISO8859-1/LC_NUMERIC lrwxr-xr-x 1 root wheel 26 Nov 21 16:30 LC_TIME -> ../en_US.ISO8859-1/LC_TIME > > -- Achilleas Mantzios