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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Paul B. Anderson" <paul.a@pnlassociates.com> writes:
> 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.

That's pretty odd --- I'm inclined to suspect index corruption.

> I also tried reindexing the table.

Get rid of the duplicates (actually, I'd just blow away all the
pg_statistic entries for each of these tables) and *then* reindex.
Then re-analyze and see what happens.

Worst case you could just delete everything in pg_statistic, reindex it,
do a database-wide ANALYZE to repopulate it. By definition there's not
any original data in that table...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

I removed the duplicates and then immediately reindexed. All is well.
The vacuum analyze on the postgres database works now too. Thanks.

It is good to know the pg_statistic table can be emptied in case this
ever happens again.

Paul

Tom Lane wrote:
> "Paul B. Anderson" <paul.a@pnlassociates.com> writes:
>
>> 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.
>>
>
> That's pretty odd --- I'm inclined to suspect index corruption.
>
>
>> I also tried reindexing the table.
>>
>
> Get rid of the duplicates (actually, I'd just blow away all the
> pg_statistic entries for each of these tables) and *then* reindex.
> Then re-analyze and see what happens.
>
> Worst case you could just delete everything in pg_statistic, reindex it,
> do a database-wide ANALYZE to repopulate it. By definition there's not
> any original data in that table...
>
> regards, tom lane
>
> .
>
>

Tom Lane wrote:
> "Paul B. Anderson" <paul.a@pnlassociates.com> writes:
>> 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.
>
> That's pretty odd --- I'm inclined to suspect index corruption.
>
>> I also tried reindexing the table.
>
> Get rid of the duplicates (actually, I'd just blow away all the
> pg_statistic entries for each of these tables) and *then* reindex.
> Then re-analyze and see what happens.
>
> Worst case you could just delete everything in pg_statistic, reindex it,
> do a database-wide ANALYZE to repopulate it. By definition there's not
> any original data in that table...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Hello, newbe here..

I seem to have run across this same error. And I believe I can
re-create it too.

I'm running 8.1.4 on slackware-(almost 11).

I have two scripts, one create's tables and indexes, the other has lots
of "copy from stdin" statements. The very last line is

VACUUM VERBOSE ANALYZE;

which eventually gives me the same error. This is a test box, with test
data, and this seemed to work:

delete from pg_statistic;
reindex table pg_statistic;
vacuum analyze;

So I'm ok, but I tried it again, by dropping the database and re-running
both scripts and got the same error again. So thought I'd offer a test
case if there was interest.

The data is 46 meg compressed and 500'ish meg uncompressed. Its a
little bit sensitive, so I'd like to be a little discreet with it, but
can put it on a website for a developer to download.

Please contact me privately for a link:

andy@squeakycode.net

....Oh, also, this box has locked up on me a few times, so its not the
most stable thing in the world. It did not lock up during import of
this data, but there is flaky hardware in there someplace, so it could
very well be a hardware issue.

-Andy

andy <andy@squeakycode.net> writes:
> So I'm ok, but I tried it again, by dropping the database and re-running
> both scripts and got the same error again. So thought I'd offer a test
> case if there was interest.

Absolutely. I've seen just enough of these reports to make me think
there's an underlying bug.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Here are a couple of new data points on this issue.

Deleting all records in pg_statistic and then reindexing clears the
problem but I've had the problem in two of my own databases in two
separate postgresql instances as well as the postgres database in both
instances.

I have a cluster of machines and the databases are on shared disk
storage. I'm just getting this arrangement working and, while I've been
debugging my scripts, I've accidentally had two copies of postgresql
running against the same initdb directories at the same time on two
different machines. Needless to say, this causes problems and I'm
thinking that vacuuming in this condition may be at or near the root of
the problem.

Paul


Tom Lane wrote:
> andy <andy@squeakycode.net> writes:
>
>> So I'm ok, but I tried it again, by dropping the database and re-running
>> both scripts and got the same error again. So thought I'd offer a test
>> case if there was interest.
>>
>
> Absolutely. I've seen just enough of these reports to make me think
> there's an underlying bug.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> .
>
>

"Paul B. Anderson" <paul.a@pnlassociates.com> writes:
> I have a cluster of machines and the databases are on shared disk
> storage. I'm just getting this arrangement working and, while I've been
> debugging my scripts, I've accidentally had two copies of postgresql
> running against the same initdb directories at the same time on two
> different machines. Needless to say, this causes problems and I'm
> thinking that vacuuming in this condition may be at or near the root of
> the problem.

You're going to have much worse problems than this from that. Two
postmasters modifying the same data files is a recipe for complete
corruption. I would recommend abandoning any database you realize
this has happened to: just initdb and start over. The chances of
being able to fix it are minimal.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster