locales and indexes.

Mathieu Arnold <arn_mat@club-internet.fr> writes:

I ran into trouble with all my indexes when i upgraded postgresql from
7.0.2 to 7.0.2 with locales. I did not really found where the problem
was but the indexes created before were messing un all the queries using
them.
The thing i did was drop/create with my indexes, but it shouldn't be
necessary to do so, isn't it ?

If you're using locale support, it's essential to make sure the
postmaster is started with the same LOCALE environment variables
every time. Otherwise you are redefining the sort order of indexes
every time you change the environment, which is a one-way ticket
to messed-up indexes.

We've heard from several people who made this mistake in the form of
sometimes starting the postmaster from the command line and sometimes
from a boot script. Unfortunately their login environment wasn't like
the environment supplied by the boot script :-(

There is a TODO item to cause the postmaster always to adopt the LOCALE
settings that were in force when initdb ran, but it doesn't seem to be
very high priority for anyone... for now, just be careful.

regards, tom lane

Tom Lane wrote:

There is a TODO item to cause the postmaster always to adopt the LOCALE
settings that were in force when initdb ran, but it doesn't seem to be
very high priority for anyone... for now, just be careful.

regards, tom lane

i've done some more testing, and it appears that if i start for instance
:
LANG= postmaster

then :
LANG=fr_FR psql

the sort will be done in the fr_FR locale, not the default one.

--
Mathieu Arnold