Thread: Mac ordering with locales
Hello
We are using different OS (Mac, win (with a small w!) and Linux).
We had some problems when doing some ordering on names.
I found that it seemed to be related on locale, While on Linux it used
default locales (en_US.UTF-8 or something like that), I had a nice
behavior).
I learned to check th lc_collate value to know on which locale ordering
was based.
I managed to create a DB under windows setting the right locale to
replace the default (en_US.UTF-8 instead of C) and it worked fine.
I tried to do the same under Mac but it still does not work.
A quick list of the kind of tasks I did:
Init the DB Cluster - initdb -D /tmp/data0 --locale='C'
Start the cluster - postgres -D /tmp/data0
Execute a query:
psql -d template1
select * from (VALUES ('a'),('B'),('b'),('A')) as f(name) order by name;
name
------
A
B
a
b
(4 rows)
Init the DB Cluster - initdb -D /tmp/data1 --locale='en_US.UTF-8'
Start the cluster - postgres -D /tmp/data1
Execute a query:
psql -d template1
select * from (VALUES ('a'),('B'),('b'),('A')) as f(name) order by name;
name
------
a
A
b
B
(4 rows)
The fact is that works on Linux and win but under Mac I always get the
ordering with 'default' C locale (I displayed all the lc_* and all are
right set)
Did I something wrong ?
Thanks
Pascal Cohen <pcohen@wimba.com> writes: > The fact is that works on Linux and win but under Mac I always get the > ordering with 'default' C locale (I displayed all the lc_* and all are > right set) Yeah, this has been complained of before, eg here http://archives.postgresql.org/pgsql-general/2005-11/msg00047.php and here http://archives.postgresql.org/pgsql-general/2004-04/msg00564.php It seems to be a deficiency in Apple's locale support. The second message is interesting since it indicates that "smart" sorting is available somewhere/somehow under OS X, but nobody here knows how to get at it :-( regards, tom lane
On Feb 21, 2008, at 12:01 PM, Tom Lane wrote: > Pascal Cohen <pcohen@wimba.com> writes: >> The fact is that works on Linux and win but under Mac I always get >> the >> ordering with 'default' C locale (I displayed all the lc_* and all >> are >> right set) > > Yeah, this has been complained of before, eg here > http://archives.postgresql.org/pgsql-general/2005-11/msg00047.php > and here > http://archives.postgresql.org/pgsql-general/2004-04/msg00564.php > > It seems to be a deficiency in Apple's locale support. The second > message is interesting since it indicates that "smart" sorting is > available somewhere/somehow under OS X, but nobody here knows how > to get at it :-( The function is "CFStringCompareWithOptionsAndLocale()". http://developer.apple.com/documentation/CoreFoundation/Reference/ CFStringRef/Reference/reference.html#//apple_ref/c/func/ CFStringCompareWithOptionsAndLocale It is obviously not a portable function (beyond the Core Foundation sources being open and available), so there may not be any interest in having this in PostgreSQL. Also, which MacOS X version is under discussion here? Could the strcoll() bug have been fixed in Leopard? Cheers, M
"A.M." <agentm@themactionfaction.com> writes: > On Feb 21, 2008, at 12:01 PM, Tom Lane wrote: >> It seems to be a deficiency in Apple's locale support. The second >> message is interesting since it indicates that "smart" sorting is >> available somewhere/somehow under OS X, but nobody here knows how >> to get at it :-( > The function is "CFStringCompareWithOptionsAndLocale()". > http://developer.apple.com/documentation/CoreFoundation/Reference/CFStringRef/Reference/reference.html#//apple_ref/c/func/CFStringCompareWithOptionsAndLocale > It is obviously not a portable function (beyond the Core Foundation > sources being open and available), so there may not be any interest > in having this in PostgreSQL. Hmm. In principle we could replace strcoll() with a wrapper around this, using a src/port/ module. The real problem with this is that it's new in Leopard, and thus not even adequately portable within the OSX world. > Also, which MacOS X version is under discussion here? Could the > strcoll() bug have been fixed in Leopard? I just checked sort(1) on 10.5.2, and it seems to still behave the same as before, so nope they didn't change it. regards, tom lane
On Thu, 2008-02-21 at 12:01 -0500, Tom Lane wrote: > Pascal Cohen <pcohen@wimba.com> writes: > > The fact is that works on Linux and win but under Mac I always get the > > ordering with 'default' C locale (I displayed all the lc_* and all are > > right set) > > Yeah, this has been complained of before, eg here > http://archives.postgresql.org/pgsql-general/2005-11/msg00047.php > and here > http://archives.postgresql.org/pgsql-general/2004-04/msg00564.php > > It seems to be a deficiency in Apple's locale support. The second > message is interesting since it indicates that "smart" sorting is > available somewhere/somehow under OS X, but nobody here knows how > to get at it :-( I have looked for a standard related to the locale behavior and I was surprised that I couldn't find one. Is a given locale, e.g. en_US, supposed to have identical behavior on any platform for which it's available? If there is a standard of some kind, is apple violating it? Regards, Jeff Davis
On Thu, Feb 21, 2008 at 11:14:58AM -0800, Jeff Davis wrote: > I have looked for a standard related to the locale behavior and I was > surprised that I couldn't find one. Is a given locale, e.g. en_US, > supposed to have identical behavior on any platform for which it's > available? > > If there is a standard of some kind, is apple violating it? Nope. If there were we could complain about it. All we have now is many different implementations. The most commonly used ones are Java, ICU, glibc and Windows. AIUI all except Windows understand the xx_XX format. Java and ICU are essentially the same. I found a note that both Perl6 and PHP6 may use ICU. That would be an interesting change. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
On Feb 22, 2008, at 10:16 AM, Martijn van Oosterhout wrote: > On Thu, Feb 21, 2008 at 11:14:58AM -0800, Jeff Davis wrote: >> I have looked for a standard related to the locale behavior and I was >> surprised that I couldn't find one. Is a given locale, e.g. en_US, >> supposed to have identical behavior on any platform for which it's >> available? >> >> If there is a standard of some kind, is apple violating it? > > Nope. If there were we could complain about it. All we have now is > many > different implementations. The most commonly used ones are Java, ICU, > glibc and Windows. AIUI all except Windows understand the xx_XX > format. > Java and ICU are essentially the same. > > I found a note that both Perl6 and PHP6 may use ICU. That would be an > interesting change. Darwin also uses ICU extensively. Is it that time of year again to discuss using/linking against it? Cheers, M
On Thu, Feb 21, 2008 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Pascal Cohen <pcohen@wimba.com> writes: >> The fact is that works on Linux and win but under Mac I always get the >> ordering with 'default' C locale (I displayed all the lc_* and all are >> right set) > > Yeah, this has been complained of before, eg here > http://archives.postgresql.org/pgsql-general/2005-11/msg00047.php > and here > http://archives.postgresql.org/pgsql-general/2004-04/msg00564.php > > It seems to be a deficiency in Apple's locale support. The second > message is interesting since it indicates that "smart" sorting is > available somewhere/somehow under OS X, but nobody here knows how > to get at it :-( > > regards, tom lane Here's a "me too" report from Possible (Robert Ivens) on IRC: http://forum.servoy.com/viewtopic.php?f=4&t=11802 David Blewett