Thread: Cannot create collation?

hi
i'm trying to test collation per column on newest pg head, but I can't
seem to get it working.

my normal locale is en_US.UTF-8, but in database, for test purposes I
used pl_PL.UTF-8.

Which works.

When I try to create french collation, it seems to work, but fails soon:

$ CREATE COLLATION french (LOCALE = 'fr_FR.UTF-8');
CREATE COLLATION

$ create table test3 ( x text COLLATE "french" );
CREATE TABLE

$ copy test3 from '/tmp/french';
COPY 55

$ select * from test3;
         x
-------------------
 d'accord
 déclassé
 décolleté
 décor
 découpage
 déjà vu
 demi-glace
 demi-sec
 dénouement
 de nouveau
 dérailleur
 de règle
 de rigueur
 dernier cri
 derrière
 déshabillé
 désolé
 détente
 de trop
 diablerie
 directeur sportif
 divertissement
 dossier
 doyen
 dressage
 du jour
 écarté
 échappé
 éclair
 éclat
 écorché
 élan
 élan vital
 éminence grise
 en bloc
 en escalier
 en famille
 enfant terrible
 en garde
 ennui
 en passant
 en pointe
 en route
 entente
 entrée
 entremets
 entre nous
 entrepreneur
 escargot
 escritoire
 et toi?
 étude
 étui
 excusez-moi
 extraordinaire
(55 rows)

$ select * from test3 order by x;
ERROR:  could not create locale "fr_FR.UTF-8": Success


What is wrong in here?

In shell, I can do:
LC_COLLATE=fr_FR.UTF-8 sort /tmp/french
and it works as expected.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Wed, Mar 02, 2011 at 01:58:02PM +0100, hubert depesz lubaczewski wrote:
> $ select * from test3 order by x;
> ERROR:  could not create locale "fr_FR.UTF-8": Success
> What is wrong in here?

never mind. sort looked like ok, but in fact apparently was't, and after
adding "language-support-fr" package in ubuntu, and restarting pg - pg
works.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On 02.03.2011 14:58, hubert depesz lubaczewski wrote:
> When I try to create french collation, it seems to work, but fails soon:
>
> $ CREATE COLLATION french (LOCALE = 'fr_FR.UTF-8');
> CREATE COLLATION

Hmm, regardless of what's causing this, I wonder if we should check that
the locale works at CREATE COLLATION time, rather than fail when you try
to use it. Ie. add a call to pg_newlocale_from_collation() at the end of
DefineCollation(). Or would that just make it more difficult to restore
a database with invalid collations?

> $ select * from test3 order by x;
> ERROR:  could not create locale "fr_FR.UTF-8": Success

Looks like newlocale() call returned 0, but didn't set errno. I don't
think that's supposed to happen. Which platform is this? Which operating
system and version?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

On Wed, Mar 02, 2011 at 06:00:45PM +0200, Heikki Linnakangas wrote:
> On 02.03.2011 14:58, hubert depesz lubaczewski wrote:
> >When I try to create french collation, it seems to work, but fails soon:
> >
> >$ CREATE COLLATION french (LOCALE = 'fr_FR.UTF-8');
> >CREATE COLLATION
>
> Hmm, regardless of what's causing this, I wonder if we should check
> that the locale works at CREATE COLLATION time, rather than fail
> when you try to use it. Ie. add a call to
> pg_newlocale_from_collation() at the end of DefineCollation(). Or
> would that just make it more difficult to restore a database with
> invalid collations?
>
> >$ select * from test3 order by x;
> >ERROR:  could not create locale "fr_FR.UTF-8": Success
>
> Looks like newlocale() call returned 0, but didn't set errno. I
> don't think that's supposed to happen. Which platform is this? Which
> operating system and version?

linux ubuntu 10.10.
I was missing language-pack-fr and language-support-fr.

interestingly, I'm getting weird results. with just language-pack-fr i
can order by column, but result is not the one i'd expect.

when I added language-support-fr - ordering works, and returns data as
expected.

weird.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On ons, 2011-03-02 at 18:00 +0200, Heikki Linnakangas wrote:
> Hmm, regardless of what's causing this, I wonder if we should check
> that the locale works at CREATE COLLATION time, rather than fail when
> you try to use it. Ie. add a call to pg_newlocale_from_collation() at
> the end of DefineCollation().

Yeah, I was apparently hoping that check_encoding_locale_matches() would
catch invalid locales, but it doesn't, so we should put in an explicit
check.

On ons, 2011-03-02 at 21:59 +0200, Peter Eisentraut wrote:
> On ons, 2011-03-02 at 18:00 +0200, Heikki Linnakangas wrote:
> > Hmm, regardless of what's causing this, I wonder if we should check
> > that the locale works at CREATE COLLATION time, rather than fail when
> > you try to use it. Ie. add a call to pg_newlocale_from_collation() at
> > the end of DefineCollation().
>
> Yeah, I was apparently hoping that check_encoding_locale_matches() would
> catch invalid locales, but it doesn't, so we should put in an explicit
> check.

Fixed.