Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0NGtW076912 for ; Tue, 23 Jan 2001 11:55:32 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f0NGsw906605; Tue, 23 Jan 2001 11:54:58 -0500 (EST) To: Attila Kevei cc: Postgres SQL list Subject: Re: select returns no line In-reply-to: <980253179.3a6d79fb29871@www.goodwill.hu> References: <980253179.3a6d79fb29871@www.goodwill.hu> Comments: In-reply-to Attila Kevei message dated "Tue, 23 Jan 2001 13:32:59 +0100" Date: Tue, 23 Jan 2001 11:54:58 -0500 Message-ID: <6602.980268898@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200101/298 X-Sequence-Number: 967 Attila Kevei 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