Re: Primary Key Problems

Phill and Glen,

We've just tracked down one mechanism that allows duplicate rows to be
spawned --- see http://fts.postgresql.org/db/mw/msg.html?mid=1078374
and following discussion.  In the example given by Brian Hirt, VACUUM's
creation of a duplicate row causes a unique-key violation to be
reported, but I think if he'd made the indexes in the other order,
the error would go undetected, leaving duplicate rows in the table.

What I'm currently puzzling over is whether this bug explains your
recent problem reports, or whether there are still more bugs lurking.

The bug is actually fairly general: checking the validity of a tuple
while a VACUUM is in process on the table can lead to the tuple being
marked good when it shouldn't be.  But I do not currently see any way
to trigger the bug other than the one Brian reported, namely creating
a functional index with a function that tries to scan its own table.

Neither of you mentioned having done any such thing in your reports,
but I wonder whether you'd ever had such an index on the tables that
you saw problems with.

            regards, tom lane