Re: BUG #2582: Duplicate entries which violate primary key and unique index on same table

"Steven Azar" <funkytuba(at)gmail(dot)com> writes:
> PostgreSQL version: 8.0.3
> Description: Duplicate entries which violate primary key and unique
> index on same table

8.0.3 is very old and has several known data-corruption-causing bugs.
Please update to the current release in that branch (8.0.8) --- or
consider migrating to 8.1.* --- and then see if the problem arises
again.

> select xmin, cmin, xmax, cmax, ctid, oid from soap_transmission where
> received_ts ='2006-06-02 06:57:26'
> -> ;
> xmin | cmin | xmax | cmax | ctid | oid
> -----------+--------+-----------+------+-----------+-----------
> 250830042 | 18 | 251208168 | 4 | (5985,30) | 152124702
> 251038216 | 398358 | 390469213 | 0 | (5985,60) | 152124702
> (2 rows)

Since the OIDs are the same, these are evidently two different states
of the same logical row, and only one of them should be considered good.
There are at least two post-8.0.3 bug fixes that might explain the
problem: one about premature destruction of t_ctid chains during VACUUM,
and one about corruption of transaction commit status.

It's possible you've found yet a different bug, but let's rule out
the already-fixed ones first.

As far as cleaning up your immediate corruption goes, you can do
something like "DELETE FROM ... WHERE ctid = '(...)'" to get rid
of whichever copy seems older. It might be worth searching the table
for other multiple occurrences of the same OID, too, just in case.

regards, tom lane