Thread: Duplicate primary keys

"Jason Tan Boon Teck" < tanboonteck@gmail.com> writes:
> How reliable is PostgreSQL in *preventing* rows duplicate primary keys to be
> inserted?

When we find such a bug, we fix it ...

> I am not using any table inheritance and the db is ver 8.1.1

... but those fixes don't help people who are running ancient releases.
There are multiple data-corruption bugs fixed in the 8.1 branch since
8.1.1, including at least one that might explain your problem:

2007-03-14 14:48  tgl

       * src/backend/commands/: vacuum.c (REL7_3_STABLE), vacuum.c
       (REL7_4_STABLE), vacuum.c (REL8_1_STABLE), vacuum.c
       (REL8_0_STABLE), vacuum.c (REL8_2_STABLE), vacuum.c: Fix a
       longstanding bug in VACUUM FULL's handling of update chains.  The
       code did not expect that a DEAD tuple could follow a RECENTLY_DEAD
       tuple in an update chain, but because the OldestXmin rule for
       determining deadness is a simplification of reality, it is possible
       for this situation to occur (implying that the RECENTLY_DEAD tuple
       is in fact dead to all observers, but this patch does not attempt
       to exploit that).  The code would follow a chain forward all the
       way, but then stop before a DEAD tuple when backing up, meaning
       that not all of the chain got moved.  This could lead to copying
       the chain multiple times (resulting in duplicate copies of the live
       tuple at its end), or leaving dangling index entries behind (which,
       aside from generating warnings from later vacuums, creates a risk
       of wrong query results or bogus duplicate-key errors once the heap
       slot the index entry points to is repopulated).

       The fix is to recheck HeapTupleSatisfiesVacuum while following a
       chain forward, and to stop if a DEAD tuple is reached.  Each
       contiguous group of RECENTLY_DEAD tuples will therefore be copied
       as a separate chain.  The patch also adds a couple of extra sanity
       checks to verify correct behavior.

       Per report and test case from Pavan Deolasee.

The current release in that branch is 8.1.11.  I recommend updating
and reindexing.

                       regards, tom lane