Re: Vacuum Error

I believe this error usually comes about due to OID wrapping.

I have experienced this error many times.  But not once since I changed all
tables definitions to "without oids".

The Fix Tom has suggested bellow is only temporary.  You will need to back
up your data base and reload.  The long term solution is to change every
table to "without oids", that is if your application is not using them.

The way I did it was to edit the output of PG_DUMP and make a global change
to the text changing every create table statement to include "without oids".
If any of your functions or client side code use temp tables, they also need
changing.

The longer you leave it the worse it gets.

Good luck

Thanks

Andrew Bartley

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, 13 January 2004 9:31 AM
To: Dave Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum Error


Dave Smith <dave.smith@candata.com> writes:
> I am running 7.2 and when doing a vacuum I am getting the following
> error....

> ERROR:  Cannot insert a duplicate key into unique index
> pg_statistic_relid_att_index

Hmm, if it were a slightly newer version I'd be interested in how you
got into this state, but since it's 7.2 I'll write it off as an old
bug.  The easiest way out, seeing that pg_statistic is all derived data,
is just
    DELETE FROM pg_statistic;
    (possibly VACUUM FULL pg_statistic here)
    re-ANALYZE everything
You should try the nearby suggestion to REINDEX first, but I'm betting
that that doesn't help.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings