Thread: please help me recover from duplicate key in unique index
Please help me recover our database from what I think
is a duplicate key in unique index problem.
We have a table of the following form:
create table foo (id serial primary key, x
integer)
in our code we had, outside of any BEGIN/COMMIT block:
insert into foo (x) values (...)
Without the transaction block, I believe we have had
duplicate keys inserted. I've fixed the bug in the
code, but now I need to fix the database!
When I try to vacuum, I get this:
> vacuum;
NOTICE: Rel sessions: TID 5/2: OID IS INVALID.
TUPGONE 1.
NOTICE: Rel sessions: TID 5/6: OID IS INVALID.
TUPGONE 1.
NOTICE: Rel sessions: TID 13/2: OID IS INVALID.
TUPGONE 1.
NOTICE: Rel sessions: TID 13/6: OID IS INVALID.
TUPGONE 1.
ERROR: Cannot insert a duplicate key into a unique
index
When I try to dump it out (to manually repair it), I
get this:
$ pg_dump gtmd000103 > gtmd.1
pqWait() -- connection not open
PQendcopy: resetting connection
SQL query to dump the contents of Table 'content' did
not execute correctly. After we read all the table
contents from the backend, PQendcopy() failed.
Explanation from backend: 'pqWait() -- connection not
open
'.
The query was: 'COPY "content" TO stdout;
'.
Can anybody help me? I am desperate. Thanks.
Charles
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://messenger.yahoo.com
Try: SELECT oid, * FROM table GROUP BY oid HAVING count(*) > 1 that should show your duplicates, and you can remove them using table.oid = _bad_number_. > Please help me recover our database from what I think > is a duplicate key in unique index problem. > > We have a table of the following form: > > create table foo (id serial primary key, x > integer) > > in our code we had, outside of any BEGIN/COMMIT block: > > insert into foo (x) values (...) > > Without the transaction block, I believe we have had > duplicate keys inserted. I've fixed the bug in the > code, but now I need to fix the database! > > When I try to vacuum, I get this: > > > vacuum; > NOTICE: Rel sessions: TID 5/2: OID IS INVALID. > TUPGONE 1. > NOTICE: Rel sessions: TID 5/6: OID IS INVALID. > TUPGONE 1. > NOTICE: Rel sessions: TID 13/2: OID IS INVALID. > TUPGONE 1. > NOTICE: Rel sessions: TID 13/6: OID IS INVALID. > TUPGONE 1. > ERROR: Cannot insert a duplicate key into a unique > index > > When I try to dump it out (to manually repair it), I > get this: > > $ pg_dump gtmd000103 > gtmd.1 > pqWait() -- connection not open > PQendcopy: resetting connection > SQL query to dump the contents of Table 'content' did > not execute correctly. After we read all the table > contents from the backend, PQendcopy() failed. > Explanation from backend: 'pqWait() -- connection not > open > '. > The query was: 'COPY "content" TO stdout; > '. > > Can anybody help me? I am desperate. Thanks. > > Charles > > __________________________________________________ > Do You Yahoo!? > Talk to your friends online with Yahoo! Messenger. > http://messenger.yahoo.com > > ************ > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Tue, Jan 04, 2000 at 06:47:39PM -0500, Bruce Momjian wrote: > Try: > > SELECT oid, * > FROM table > GROUP BY oid > HAVING count(*) > 1 > > that should show your duplicates, and you can remove them using > table.oid = _bad_number_. ah, i thought each row had a unique oid, and thus, that query wouldn't show the duplicate content. i would do something like: SELECT keyfield FROM tablename GROUP BY keyfield HAVING COUNT(*) > 1; this will produce a list of rows where keyfield is not unique in tablename. then, for each of those entries, you want to do a: SELECT oid, keyfield, other fields FROM tablename WHERE keyfield = <value>; then you can select which duplicate you want to nuke, and do: DELETE FROM tablename WHERE OID = 999999; (i've found that creating an index, non-unique for performance sake) on oid will improve the preformance of duplicate nukes on really big tables) mind you, the above process, on a large table is gonna be slow anyways. -- [ Jim Mercer jim@reptiles.org +1 416 506-0654 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
> On Tue, Jan 04, 2000 at 06:47:39PM -0500, Bruce Momjian wrote: > > Try: > > > > SELECT oid, * > > FROM table > > GROUP BY oid > > HAVING count(*) > 1 > > > > that should show your duplicates, and you can remove them using > > table.oid = _bad_number_. > > ah, i thought each row had a unique oid, and thus, that query wouldn't show the > duplicate content. > Oh, I thought they were using oid's for keys. I see now. Yes, you are correct. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
With a SERIAL type, which translates to a unique index, I'm still wondering how this duplication could occur in the first place... Is this a known failure scenario? A bug? Cheers, Ed Loehr
Charles Martin wrote: > Please help me recover our database from what I think > is a duplicate key in unique index problem. This may not help, and forgive my asking the obvious, but have you done a SELECT on the table and actually *seen* duplicate 'id' values in the SERIAL column? That would surprise me because I didn't think it was possible to create duplicates in the scenario you describe. The SERIAL type is really just a sequence, and its values are unique across all transactions (I hope!). IIRC there is some opportunity for wierdness if the sequence cache setting has been "adjusted" incorrectly (http://www.postgresql.org/docs/postgres/sql-createsequence.htm). > When I try to vacuum, I get this: > > ERROR: Cannot insert a duplicate key into a unique > index > I didn't realize vacuum tried to insert anything. Maybe a system table insert? Cheers, Ed Loehr
> > Please help me recover our database from what I think > > is a duplicate key in unique index problem. > > This may not help, and forgive my asking the obvious, but have > you done a SELECT on the table and actually *seen* duplicate 'id' > values in the SERIAL column? That would surprise me because I > didn't think it was possible to create duplicates in the scenario > you describe. The SERIAL type is really just a sequence, and its > values are unique across all transactions (I hope!). IIRC there > is some opportunity for wierdness if the sequence cache setting > has been "adjusted" incorrectly > (http://www.postgresql.org/docs/postgres/sql-createsequence.htm). > > > When I try to vacuum, I get this: > > > > ERROR: Cannot insert a duplicate key into a unique > > index > > Try dropping all indexes on the table, do a vacuum (if it will let you). Dump the table out with pg_dump -t <t_name> if it will let you, sort it in emacs or with perl. Easiest thing would then be to write a little perl script that puts all duplicate rows into a separate file. Dropt the table and re-create it. Load the first lot up (with given sequence number!), fix your sequences (drop, create ..start <max-seq>) and then handle the duplicate rows (i.e. insert them with perl/DBI or something so that they get new sequence numbers assigned). I think you should be able to dump once you have dropped all indexes (probably one of them is <tab-name>_pkey). Your sequence may be called <tab-name>_<col-name>_seq if memeory serves me right. It is still a normal sequences and you can drop and recreate it safely. Good luck, Adriaan