problems with table corruption continued - Mailing list pgsql-hackers

From Brian Hirt
Subject problems with table corruption continued
Date
Msg-id 001701c1833b$249c0b80$640b0a0a@berkhirt.com
Whole thread Raw
List pgsql-hackers
Okay, here's a follow up to my previous messages "ACK table corrupted,
unique index violated."

I've been trying to clean up the corruptions that i mentioned earlier.  I
felt most comfortable shutting down all my application servers, restarting
postgres, doing a dump of my database and rebuilding it with a pginit and
complete reload.  So far so good.  I went to fix one of the corrupted tables
and i have another strange experience.  I'm still looking into other
possibilities such as a hardware failure; but i thought this might be
interesting or helpful in the context of my previous post:  Basically the
table with duplicate oid/id now has unique oid from the relead, so I'm going
to delete the duplicate rows and recreate the unique index on the identity
column.

basement=# select count(*),developer_aka_id from developer_aka group by
developer_aka_id having count(*) <> 1;count | developer_aka_id
-------+------------------    2 |             9789    2 |            10025    2 |            40869
(3 rows)

basement=# select oid,* from developer_aka where developer_aka_id in
(9789,10025,40869); oid  | developer_id | developer_aka_id |    first_name     | last_name
-------+--------------+------------------+-------------------+-----------48390 |         1916 |             9789 |
Chris            | Smith48402 |        35682 |            40869 | Donald "Squirral" | Fisk48425 |         4209 |
   10025 | Mike              | Glosecki48426 |         1916 |             9789 | Chris             | Smith48427 |
35682 |            40869 | Donald "Squirral" | Fisk48428 |         4209 |            10025 | Mike              |
Glosecki
(6 rows)

basement=# delete from developer_aka where oid in (48390,48402,48425);
DELETE 3
basement=# select count(*),developer_aka_id from developer_aka group by
developer_aka_id having count(*) <> 1;count | developer_aka_id
-------+------------------
(0 rows)

basement=# create unique index developer_aka_pkey on
developer_aka(developer_aka_id);
CREATE
basement=# VACUUM ANALYZE developer_aka;
ERROR:  Cannot insert a duplicate key into unique index developer_aka_pkey




pgsql-hackers by date:

Previous
From: "Ross J. Reedstrom"
Date:
Subject: Re: Explicit configuration file
Next
From: Tom Lane
Date:
Subject: Re: Intermediate report for AIX 5L port