Re: Duplicate oid and primary key values

On Tuesday 10 February 2004 17:10, Jeff Bohmer wrote:
> I have a table in a PG 7.4.1 database with 380 duplicate rows,
> including duplicate oid and primary key values. Looking through our
> backups, the duplicates did not exist before Friday, 02/06/2004. I'm
> assuming neither pg_dumpall nor restoring from a pg_dumpall file will
> eliminate such duplicates. We upgraded from 7.3.4 to 7.4.1 on
> 02/02/2004.
>
> What can cause these duplicates?
>
> The server has had several system crashes over the past few days and weeks.

Hardware related? Or is it not clear yet?

> Below is my session with the DB showing an example of the duplicates,
> the table structure, and trigger functions.
>
> cos=> select oid, recordnumber from client where recordnumber = 10970;
> oid | recordnumber
> ---------+--------------
> 2427408 | 10970
> (1 row)
>
> cos=> select oid, recordnumber from client where recordnumber < 10971
> and recordnumber > 10969;
> oid | recordnumber
> ---------+--------------
> 2427408 | 10970
> 2427408 | 10970
> (2 rows)

In the absence of Tom or some other more knowledgable source, try these out.

SELECT xmin,cmin,xmax,cmax,tid,oid,* FROM ... to see if these are two versions
of the same row
Perhaps stick an EXPLAIN ANALYSE on the front of those and see if one is using
an index and the other not.

It might be a corrupted INDEX, in which case REINDEX should fix it.

PS - you probably want now() or CURRENT_DATE etc. in the trigger functions
rather than 'now'.
--
Richard Huxton
Archonet Ltd