Thread: Re: [GENERAL] Weird ..... (a=1 or a=2) <> (a=2 or a=1)
Many thanks for allowing me to trace through your problem case.
It's a real Postgres bug, and a nasty one. The problem is a thinko in
nodeIndexscan.c's code that tests whether the same tuple has already
been emitted in a previous OR'd scan: it is looking for a match on
tuple->t_data->t_ctid, when what it should really be looking at is
tuple->t_self. What I find is that the indexscan for status == open
is returning TID (880,5), which has XMAX_INVALID and a t_ctid pointing
at (880,18). (This is perfectly normal, it just indicates that
somebody tried to update the row but the updating transaction rolled
back, and the updated version at 880,18 was later recycled by VACUUM.)
So this causes a bogus rejection when TID (880,18) is scanned during the
second indexscan.
This only affects the 7.4 and 8.0 branches, because earlier and later
versions of Postgres don't use this technique for detecting duplicates.
But it's surprising we didn't find it before.
Patches will appear in next week's releases. Thanks again!
regards, tom lane
On Fri, 2006-05-19 at 11:34 -0400, Tom Lane wrote: [.......] > > This only affects the 7.4 and 8.0 branches, because earlier and later > versions of Postgres don't use this technique for detecting duplicates. > But it's surprising we didn't find it before. > > Patches will appear in next week's releases. Thanks again! > Thanks to you for finding and fixing the problem :-) It looks like you are finish so I will update the server and you will lose access to it. regards -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On 5/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > This only affects the 7.4 and 8.0 branches, because earlier and later > versions of Postgres don't use this technique for detecting duplicates. > But it's surprising we didn't find it before. hm. about a year ago I reported a case where the database allowed multiple records with the same p-key which were causing problems with dump/reload from 8.0->8.1. It was pretty rare, but it looked like under certain circumstances unique constraint was not getting applied.I was unable to reproduce it, though. Is it possible that this bug was the cause of that particular problem? Merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 5/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This only affects the 7.4 and 8.0 branches, because earlier and later
>> versions of Postgres don't use this technique for detecting duplicates.
>> But it's surprising we didn't find it before.
> hm. about a year ago I reported a case where the database allowed
> multiple records with the same p-key which were causing problems with
> dump/reload from 8.0->8.1. It was pretty rare, but it looked like
> under certain circumstances unique constraint was not getting applied.
> I was unable to reproduce it, though.
Yeah, I remember.
> Is it possible that this bug was the cause of that particular problem?
No, this is unrelated. It only occurs in a query that's fetching rows
using OR'd indexscans, eg
SELECT ... WHERE indexedcol = 42 OR indexedcol = 47 OR ...;
(you can spell it "indexedcol IN (42,47,...)" with same results) and
the problem is basically incorrect detection of fetching the same row
more than once, ie, a bug in the code that's in charge of not returning
rows multiple times if query is like
SELECT ... WHERE indexedcol = 42 OR indexedcol = 42 OR ...;
This is nowhere near the unique-constraint enforcement mechanism.
regards, tom lane