Attila Kevei <attila.kevei@mail.goodwill.hu> writes:
> Our database has a SELECT problem using varchar columns in WHERE clause
> (but not in all rows!!!).
> We can fix the whole table (or just the row) as shown below but later it
> seems wrong again (and after the fix the row isn't UPDATEd).
Very bizarre. Is the bogus SELECT using an index? (Check with EXPLAIN
if you're not sure.) I am suspicious that it is using a corrupted
index. Try dropping and rebuilding the index and see if the problem
persists.
If you have built Postgres with locale support, an easy way to get
corrupted indexes on text/char/varchar columns is to start the
postmaster with different locale environment variables at different
times. Different locales mean different logical sort orders, and
a btree index that is out of order is corrupt by definition. The
most common way of shooting yourself in the foot is to sometimes start
the postmaster from a boot script, and sometimes start it by hand from
a user login that has different locale environment than the boot script.
7.1 will have some defenses against this, but in older releases you
have to be careful.
regards, tom lane