From: | "Paul B(dot) Anderson" <paul(dot)a(at)pnlassociates(dot)com> |
---|---|
To: | "[ADMIN]" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Vacuum error on database postgres |
Date: | 2006-09-01 12:02:24 |
Message-ID: | 44F82150.5000809@pnlassociates.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Luís Sousa | 2006-09-01 14:40:09 | Problem using pg_restore with -a option |
Previous Message | RW | 2006-09-01 08:21:47 | Re: pg_dump: schema with OID 16396 does not exist |
From | Date | Subject | |
---|---|---|---|
Next Message | Bernd Helmle | 2006-09-01 12:31:51 | Re: [PATCHES] Updatable views |
Previous Message | Michael Glaesemann | 2006-09-01 11:49:07 | Re: [HACKERS] Interval aggregate regression failure |