Skip site navigation (1) Skip section navigation (2)

Re: Duplicate primary keys

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jason Tan Boon Teck" <tanboonteck(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Duplicate primary keys
Date: 2008-01-18 03:22:55
Message-ID: 19944.1200626575@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Jason Tan Boon Teck" <tanboonteck(at)gmail(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Jason Tan Boon TeckDate: 2008-01-18 04:47:33
Subject: Re: Duplicate primary keys
Previous:From: Jason Tan Boon TeckDate: 2008-01-18 02:33:43
Subject: Duplicate primary keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group