Re: BUG #14874: Dublicate values in primary key

On Fri, Oct 27, 2017 at 4:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> So ... have there been any server crashes lately?
>
> In any case, my advice is to take a hard look at whether your storage
> stack honors fsync all the way down. That won't get you out of the
> immediate problem --- as Peter said, manual cleanup of the data and
> then reindexing the indexes seems like your only way out of that.
> But it might save you from a recurrence.

I agree with your conclusion, but I still think that it's slightly
ambiguous. There didn't seem to be an issue with sequences going
backwards -- the actual complaint was about there being multiple
versions of the same logical row visible simultaneously (sequences
were mentioned in passing). This is probably a case of tuples within a
HOT chain experiencing some kind of "resurrection". Though that in
itself doesn't prove much of anything.

The latest example of a bug that made "tuple resurrection" possible is
the recent "freeze-the-dead" bug, but I can recall a couple of other
bugs in the history of 9.4 with the same symptom. Of course, storage
problems can also cause this symptom, and if you're using foreign
keys, chances are good that storage infelicities will result in
corruption with symptoms similar to the corruption caused by various
Multixact bugs (e.g., resurrected rows, VACUUM reporting apparent
MultiXactId wraparound, etc). That's why these symptoms don't tell us
much.

I don't want to make too much of this. It's clear that being on such
an old point release is needlessly risky, and it's also clear that the
OP has work to do to validate the reliability of storage as part of a
disaster recovery effort.

--
Peter Geoghegan