Re: [BUGS] BUG #14874: Dublicate values in primary key

CONCHY Elisabeth <elisabeth.conchy@dgfip.finances.gouv.fr> writes:
> Yesterday night, we found out that the problem affects not only one 
> table, but 5.
> We would like to understand how this is possible, whithout PosgreSQL 
> detecting any error.
> Can we be sure that couldn't happen again, if we upgrade the version (to 
> 9.4.12 for example) ?

Nobody's ever going to promise you that Postgres contains no bugs :-(.
What we *can* say confidently is that 9.4.4 contains several known
data-corruption-causing bugs that are fixed in the latest 9.4 minor
release.  So you really ought to upgrade.

Having said that ... the symptoms you are describing don't sound much
like a Postgres-induced bug.  I could believe a bug possibly having
caused the sequence generator to go backwards.  And I could believe
a bug having corrupted an index so that the index failed to notice
duplicate key insertions.  But it's harder to believe both of those
things happening at once, and even harder to believe that a heretofore
unnoticed bug would cause five different indexes to be corrupted at
the same time that a sequence generator went backwards.

What *does* fit that set of facts really well is an OS crash (either
a power failure or kernel crash), if Postgres's fsync calls were not
being honored properly so that some data file changes got to disk
ahead of the associated WAL log entries.  In that case, even after
WAL replay you would have an inconsistent database: there could be
rows in the tables with IDs past where the sequence generator thought
it was, and the indexes could be corrupt enough so that you didn't
get told about duplicate entries as the sequence generator got advanced
again.  (The "index corruption" might well simply amount to the indexes
not having been updated to match what was in the tables proper.)

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.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs