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

Tom Lane wrote:

> 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.

Disturbing indeed.

> 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().

I have glibc v2.2.2 installed. Do you have a pointer to some info which
may explain the consequences of these "known problems" with v2.2.2?
Here in Brazil we make heavy use of locale.

> 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...

I'm probably doing something stupid here, but according to the
documentation for REINDEX, "In order to run REINDEX command, postmaster
must be shut down and stand-alone Postgres should be started instead
with options -O and -P (an option to ignore system indexes)." But the
postmaster doesn't like these options. Anyway, in the end I simple
started postmaster like I usually do and ran the REINDEX command on the
appropriate index:

main_v0_8=# REINDEX INDEX admin_users_pkey;
REINDEX
main_v0_8=# VACUUM ANALYZE ;
ERROR: No one parent tuple was found

So the error message has changed, but still exists. Interestingly
(gulp) the one user that was having a problem with the database now has
no problems. I love living on the edge!

I also tried REINDEX'ing the entire admin_users' table (which includes 1
other index, UNIQUE on 'name'), same result.

Any other suggestions?

(PS: yes, I'm d/ling pg v7.1.3 as I write).

Thanks (as usual) for your help Tom!

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Moléculas Naturais, Rio de Janeiro, Brasil
email: lapham(at)extracta(dot)com(dot)br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------