I meant to ask this the last time this came up on the list, but now is a
good time. Given what Tom describes below as the behavior in 7.1
(initdb stores the locale info), how do you determine what locale a
database is running in in 7.1 after initdb? Is there some file to look
at? Is there some sql statement that can be used to select the setting
from the DB?
thanks,
--Barry
Tom Lane wrote:
>
> Rob van Nieuwkerk <robn@verdi.et.tudelft.nl> writes:
> > Checking whith ps and looking in /proc reveiled that postmaster indeed
> > had LANG set to "en_US" in its environment. I disabled the system script
> > that makes this setting, restarted postgres/postmaster and reran my tests.
>
> > The problem query returns the *right* answer now !
> > Turning LANG=en_US back on gives the old buggy behaviour.
>
> Caution: you can't just change the locale willy-nilly, because doing so
> invalidates the sort ordering of btree indexes. An index built under
> one sort order is effectively corrupt under another. I recommend that
> you dumpall, then initdb under the desired LANG setting, then reload,
> and be careful always to start the postmaster under that same setting
> henceforth.
>
> (BTW, 7.1 prevents this type of index screwup by locking down the
> database's locale at initdb time --- the ONLY way to change sort order
> in 7.1 is to initdb with the right locale environment variables. But in
> 7.0 you gotta be careful about keeping the locale consistent.)
>
> > I know very little about this LANG, LOCALE etc. stuff.
> > But for our application it is very important to support "weird" characters
> > like "éõåÊ ..." etc. for names. Basically we need all
> > letter symbols in ISO-8859-1 (Latin 1).
>
> As long as you are not expecting things to sort in any particular order,
> it really doesn't matter what locale you run Postgres in. If you do
> care about sort order of characters that aren't bog-standard USASCII,
> then you may have a problem. But you can store 'em in any case.
>
> regards, tom lane