pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIssues with LC_COLLATE, across 8.3/8.4 on various platforms
8+ messages / 3 participants
[nested] [flat]
* Issues with LC_COLLATE, across 8.3/8.4 on various platforms
@ 2010-02-02 12:26 Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Achilleas Mantzios @ 2010-02-02 12:26 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2010-02-02 15:28 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:37 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Tom Lane <tgl@sss.pgh.pa.us>
2010-02-03 14:53 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2010-02-02 15:28 UTC (permalink / raw)
To: pgsql-admin
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 postgresql server.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2010-02-02 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-03 09:01 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2010-02-02 15:37 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:37 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-02-03 09:01 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2010-02-03 09:01 UTC (permalink / raw)
To: pgsql-admin; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Στις 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 tablespace of its own,
is there a way to "cheat" postgresql after initdb in order to see this tablespace and the database in it, avoiding the whole cycle?
i think this is not a good idea, i just thought i could ask, just to make sure.
> regards, tom lane
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2010-02-03 14:53 ` Peter Eisentraut <peter_e@gmx.net>
2010-02-03 15:05 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2010-02-03 14:53 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
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 ...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 14:53 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
@ 2010-02-03 15:05 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 19:16 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Achilleas Mantzios @ 2010-02-03 15:05 UTC (permalink / raw)
To: pgsql-admin; +Cc: Peter Eisentraut <peter_e@gmx.net>
Στις 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 14:53 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
2010-02-03 15:05 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2010-02-03 19:16 ` Peter Eisentraut <peter_e@gmx.net>
2010-02-04 09:00 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2010-02-03 19:16 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 14:53 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
2010-02-03 15:05 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 19:16 ` Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms Peter Eisentraut <peter_e@gmx.net>
@ 2010-02-04 09:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2010-02-04 09:00 UTC (permalink / raw)
To: pgsql-admin; +Cc: Peter Eisentraut <peter_e@gmx.net>
Στις 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-02-04 09:00 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-02 12:26 Issues with LC_COLLATE, across 8.3/8.4 on various platforms Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:28 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-02 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-03 09:01 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 14:53 ` Peter Eisentraut <peter_e@gmx.net>
2010-02-03 15:05 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-02-03 19:16 ` Peter Eisentraut <peter_e@gmx.net>
2010-02-04 09:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox