Vacuum error on database postgres
От | Paul B. Anderson |
---|---|
Тема | Vacuum error on database postgres |
Дата | |
Msg-id | 44F82150.5000809@pnlassociates.com обсуждение исходный текст |
Ответы |
Re: Vacuum error on database postgres
|
Список | pgsql-admin |
I'm running postgreSQL 8.1.4 on Red Hat Enterprise Linux 3. Things have been working well for a while but in the last few days, I've gotten the following error during a nightly vacuum. postgres=# vacuum analyze; ERROR: duplicate key violates unique constraint "pg_statistic_relid_att_index" I can vacuum that table individually without problems. postgres=# vacuum pg_statistic; VACUUM postgres=# vacuum analyze pg_statistic; VACUUM postgres=# I found a posting from 2004 that suggested the following query. postgres=# select starelid, staattnum, count(*) from pg_statistic group by 1,2 having count(*) > 1; starelid | staattnum | count ----------+-----------+------- 2608 | 3 | 2 10723 | 7 | 2 10723 | 4 | 2 10723 | 5 | 2 10723 | 2 | 2 10723 | 3 | 2 10728 | 1 | 2 10728 | 2 | 2 10728 | 3 | 2 10728 | 4 | 2 10728 | 5 | 2 10738 | 1 | 2 (12 rows) I did delete exactly one of each of these using ctid and the query then shows no duplicates. But, the problem comes right back in the next database-wide vacuum. I think the objects are as given below. postgres=# select relname,oid,reltype from pg_class where oid in (2608,10723,10728,10738); relname | oid | reltype -------------------------+-------+--------- sql_features | 10723 | 10724 sql_implementation_info | 10728 | 10729 sql_packages | 10738 | 10739 pg_depend | 2608 | 10277 (4 rows) I also tried reindexing the table. postgres=# reindex table pg_statistic; ERROR: could not create unique index DETAIL: Table contains duplicated values. postgres=# Help! Paul
В списке pgsql-admin по дате отправления: