problems with table corruption continued

From: "Brian Hirt" <bhirt(at)mobygames(dot)com>
To: "Postgres Hackers" <pgsql-hackers(at)postgresql(dot)org>
Cc: "Brian A Hirt" <bhirt(at)berkhirt(dot)com>
Subject: problems with table corruption continued
Date: 2001-12-12 18:30:47
Message-ID: 001701c1833b$249c0b80$640b0a0a@berkhirt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 | Smith
48402 | 35682 | 40869 | Donald "Squirral" | Fisk
48425 | 4209 | 10025 | Mike | Glosecki
48426 | 1916 | 9789 | Chris | Smith
48427 | 35682 | 40869 | Donald "Squirral" | Fisk
48428 | 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Permaine Cheung 2001-12-12 20:23:40 Re: Third call for platform testing
Previous Message Tom Lane 2001-12-12 18:01:28 Re: ACK table corrupted, unique index violated.