Re: Duplicate key existant/index visibility bug in 9.3.3

Erik Jones <ejones@engineyard.com> writes:
> Today I had a client report an issue restoring a dump made from their
production db on a testing server wherein there existed two rows for a
table with identical primary key values.

FWIW, the simplest explanation for this situation is corruption in your
primary key index.  That's necessary to explain the rows not being visible
through an indexscan, and it's probably sufficient to explain how two rows
with the same pkey managed to get in without a unique-index failure.

So what I'd suggest is getting rid of whichever row you don't want (DELETE
WHERE ctid = something is the best way) and then doing a REINDEX on that
index.  Now, the REINDEX is going to lock out updates on the table, so
it's not a zero-downtime solution ... but it's surely better than a dump
and reload.

You should definitely also update to 9.3.5 (or next week, 9.3.6) ASAP.
Even if this specific problem isn't fixed, there's a bunch of things
that *are* fixed and will eventually bite you.  9.3.X has not been
one of our most stable release branches :-(

            regards, tom lane