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
