| 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: | Whole Thread | Raw Message | 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 |