Re: Index problem
От | Tom Lane |
---|---|
Тема | Re: Index problem |
Дата | |
Msg-id | 13864.974312385@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Index problem (Patrik Kudo <kudo@partitur.se>) |
Ответы |
Re: Index problem
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-sql |
Patrik Kudo <kudo@partitur.se> writes: > select * from elever where userid like 'walth%'; <-- Not OK! > Droping and recreating the index solves the problem, but that's not > good enough for me since the problem has reoccured on a different > machine with a different database. vacuum and vacuum analyze does not > report any problem with the table. > Both times the problem occured with userid's starting with a > "w". Postgres is running with a Swedish locale, and on FreeBSD this > means that "w" and "v" (among a number of other letter) are treated > equally when collating/sorting. I suppose this could be part of the > problem. > Is this a known problem? If so, is it fixed in 7.0.2? I've not seen > this happen on any of our postgres 7.0.2 systems, but as I can't even > reproduce it on the 6.5.3, that's no guarantee it's fixed... Hmm. I can think of two known issues that may be relevant. First off, there are indeed known problems with LIKE index optimization in non-ASCII locales. I believe that 7.0 fixes the problems for locales that just have a non-ASCII sort order of individual characters, but we've since realized that it can still do the wrong thing in locales where there are special rules for handling multi-character sequences. I don't know the Swedish rules offhand, so don't know if that's a problem for you. However, a LIKE optimization problem would not be fixed by dropping and recreating the index. This makes me think you are being bit by the other issue: if you compile with LOCALE support then it is critical that the postmaster *always* be started with the same LOCALE settings (at least the same LC_COLLATE value). If LOCALE varies that means your text sort ordering varies, which means that indexes on text columns may appear out-of-order, which causes index searches to miss entries they should have found. If you insert entries under different LOCALE settings then you may wind up with an index that is not consistent with *any* single LOCALE; the only cure for that is to drop and rebuild the index. Unfortunately, it's way too easy to get bit by this bug. The most common error is to start the postmaster by hand from a shell account whose LOCALE environment is different from what's supplied when the postmaster is started from a boot-time script. Best bet is to set the correct LOCALE values in a wrapper script that you use in both cases. We have talked about fixing this by saving the active LOCALE variables at initdb time, and having the postmaster adopt those values whenever it's started. But it hasn't got done yet. (Peter, would it be easy to make GUC handle this? There'd need to be some way to cause guc.c to do a putenv() ...) regards, tom lane
В списке pgsql-sql по дате отправления:
Предыдущее
От: Josh BerkusДата:
Сообщение: Re: [PHP] Re: FTI, paged, ranked searching and efficiency.