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
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. |