database was created with locales (ctype, collate), and uses indexes on varchar. after migration from old RH to gentto, strange things happend with indexes. generaly, they were totaly broken. env (LC_ALL,LANG and so on) were configured (in /etc/env.d directory and /etc/conf/postgresql file). i was sure, that postgres is starting with apropiate enviorement: su - postgresql postgres ~ $ echo $LANG pl_PL postgres ~ $ but: su - postgres -c "echo $LANG;bash" pl_PL postgres ~ $ echo $LANG postgres ~ $ there's no env in shell started with su and -c option! and with such schema postgres starting by /etc/init.d/postgresql script. that's why indexes was broken! i'v additionaly tests, by writing siplme C program, which printfs out getenv() function. there was no local env, which is required by pg_ctl program. this is gentoo specific, because on RH and Debian, i have not seen such behavior. to resolve this problem, i'v added "-l" before "-c" in postgres starting script. this forces to load /etc/profile script and sets env. Reproducible: Sometimes Steps to Reproduce:
I haven't observed that problem running a german, hungarian and slovenian databases (separate pgsql instances on one server, to have correct indexes for each language). Did you initialize your database using the ebuild? If so you probably need to redo it running 'initdb --locale=pl_PL' by hand, because the ebuild will default to LC_ALL=C iirc. You may also want to make sure the correct locale settings are setup as pl_PL in $PGDATA/postgresql.conf (lc_messages, lc_numeric,...) I don't think this is a "missing environment" issue, but maybe i misread your explanation.
(In reply to comment #1) > I haven't observed that problem running a german, hungarian and slovenian > databases (separate pgsql instances on one server, to have correct indexes for > each language). Did you initialize your database using the ebuild? If so you > probably need to redo it running 'initdb --locale=pl_PL' by hand, because the > ebuild will default to LC_ALL=C iirc. i'v manualy initalized my database with --locale=pl_PL > You may also want to make sure the correct > locale settings are setup as pl_PL in $PGDATA/postgresql.conf (lc_messages, > lc_numeric,...) those parameters are in my postgresql.conf. additionaly, i am almost sure, that lc_message , ln_numeric , lc_monetary and so on (but ln_ctype and lc_collate) have no matter on how indexes works. those parameters may be changed from one run of database to another. but lc_ctype and lc_collate may be set only once, while initalizing database. and every time, postmaster starts, it should also those env vars be set properly. > I don't think this is a "missing environment" issue, but maybe i misread your > explanation. may You do little test ? please do: su - postgres -c bash and when bash starts, please echo LANG, LC_CTYPE LC_COLLATE and other vars. or just past "env" command output. what shell is postmaster user using ? (bash, zsh ?) please tell me, how do You starts postmasters of this all database instances? /etc/init.d/postgresql ? i will say it again, if I do: su - postgres pg_ctl -D /path/to/postgres start everything works fine. but when i do /etc/init.d/postgresql start as root, strange things happens. very strange.
This was fixed in the newest PostgreSQL init scripts. They now use the start-stop-daemon's features to not anymore have to su -, and you can define env-vars to be passed to the daemon in /etc/conf.d/postgresql. Best regards, CHTEKK.
