Vacuum error on database postgres

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

Responses

Browse pgsql-admin by date

  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

Browse pgsql-hackers by date

  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