Re: Re: query optimizer questions

Allan Engelhardt writes:

> I find locale support in PostgreSQL confusing.  The locale of the
> current user at the time of initialising the database determines the
> collating order (e.g. is 'a' < 'A').  This in turns affect things like
> indexing (as described below).

This is because if you were allowed to change this setting after the
database initialization, your indexes would get corrupted.  (Recall that
(some) indexes are sorted, and the index scanning code relies on indexes
being sorted a certain way.)

> The locale of the account that is running the current postmaster
> defines format conversions (e.g. the currency symbol in
> to_char(1,'L99')).

This is a courtesy because the above contraints do not apply to this
locale category.  I agree it's confusing, but I'm not sure what can be
done about it.

> The locale of the current process requesting a query defines how the
> user expects to see the result, and is (of course) ignored by
> PostgreSQL.

I guess it would make some sense if the client would pass along its locale
settings (in the allowable categories) to the server.

> To make matters worse: On Red Hat 7.1 (US boxed set) the default
> locale is set in /etc/sysconf/i18n to en_US, i.e. American English.
> Fine, except some Americans may be surprised to learn that 'A' > 'a'.

What's so surprising about that?  Yeah, ASCII order, blah.  Remember that
computers are for humans, not for computers.

Btw., actually the rules are more complicated:  ab < Ab < ac

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter