problems with table corruption continued
От | Brian Hirt |
---|---|
Тема | problems with table corruption continued |
Дата | |
Msg-id | 001701c1833b$249c0b80$640b0a0a@berkhirt.com обсуждение исходный текст |
Список | 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 по дате отправления: