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
