"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