Re: [GENERAL] Weird ..... (a=1 or a=2) <> (a=2 or a=1)

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 5/19/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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