From: Paul B. Anderson <paul.a@pnlassociates.com>
To: [ADMIN] <pgsql-admin@postgresql.org>
Subject: Vacuum error on database postgres
Date: Fri, 01 Sep 2006 08:02:24 -0400
Message-ID: <44F82150.5000809@pnlassociates.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: paul.a@pnlassociates.com
Subject: Re: Vacuum error on database postgres
In-Reply-To: <44F82150.5000809@pnlassociates.com>
References: <44F82150.5000809@pnlassociates.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox