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

Re: Duplicate primary keys

From: "Jason Tan Boon Teck" <tanboonteck(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Duplicate primary keys
Date: 2008-01-18 04:47:33
Message-ID: dd8f6c970801172047m33692f69na82704491920d8a1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I will make an upgrade on my next opportunity.

Thanks, Tom.

jason

On Jan 18, 2008 11:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "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
>



-- 
Jason Tan Boon Teck

In response to

pgsql-novice by date

Next:From: normanDate: 2008-01-18 17:55:23
Subject: Synchronization of records in multiple databases
Previous:From: Tom LaneDate: 2008-01-18 03:22:55
Subject: Re: Duplicate primary keys

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