Re: ERROR: cannot insert duplicate... on VACUUM ANALYZE

Jon Lapham <lapham(at)extracta(dot)com(dot)br> writes:
> Tom, before answering your questions, I should also say that the *first*
> time I ran VACUUM ANALYZE I actually received 2 messages, the one I've
> already reported and also "NOTICE: Rel pg_type: TID 3/3: OID IS INVALID.
> TUPGONE 1.". The second and subsequent runs of VACUUM ANALYZE did not
> include this second message.

Hmm, this is disturbing; it suggests data's been clobbered on disk
somehow.

> The platform is linux, RH7.1 with all errata patches applied, running on
> an AMD 1300. Postgresql v7.1.2, compiled thusly: " --with-tcl
> --with-perl --with-odbc --enable-hba --enable-locale" (so I am using
> locale, if that matters). I am running the postmaster with "-B 1000".

An update to 7.1.3 might be well-advised, but I am not sure that I can
connect this problem to any of the bugs fixed in 7.1.3. On the locale
front, I sure hope you have glibc 2.2.3 or later installed, else you
are subject to the known problems with 2.2.2's strcoll().

However, since the index in question is on an int2 column, it wouldn't
be affected by strcoll(). So that still leaves us with no good theory
about what happened.

You can probably recover from the immediate problem by rebuilding the
damaged index (use REINDEX, or just drop and recreate the index).
However, that won't do anything to prevent it from happening again...

regards, tom lane