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

Tom Lane wrote:

> Jon Lapham <lapham(at)extracta(dot)com(dot)br> writes:
>
>>When running a routine VACUUM ANALYZE on one of our databases (pg
>>v7.1.2) the following message appears:
>>
>
>>main_v0_8=# VACUUM ANALYZE ;
>>ERROR: Cannot insert a duplicate key into unique index admin_users_pkey
>>
>
> Looks like you have a corrupted index. What is that index on, exactly?
> And what's your platform?
>
> regards, tom lane
>

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.

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

Well, the corrupted index is due to the PRIMARY KEY restraint on the
"id" field. Oh, I just realized that the "\d admin_users" output I sent
before doesn't tell you where the pkey restraint is (that would be a
useful thing to show in "\d" output, no?).

Anyway, here is the relavent SQL used to create the table in question:

CREATE SEQUENCE admin_users_id_seq start 1;
CREATE TABLE admin_users (
id INT2 PRIMARY KEY DEFAULT nextval('admin_users_id_seq'),
name VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(20) NOT NULL,

email VARCHAR(255),
fullname VARCHAR(255),

usertype INT2 NOT NULL DEFAULT 1,
mygroup INT2 NOT NULL DEFAULT 1,
active BOOLEAN NOT NULL DEFAULT 't',

-- Authorization codes
remoteip VARCHAR(15) DEFAULT NULL,
sessioncode VARCHAR(20) DEFAULT NULL,

-- Record of last connection time and place
firstconnect TIMESTAMP,
lastconnect TIMESTAMP,
lastip VARCHAR(15)
);

Finally, I don't know if this is of help:

main_v0_8=# select * from admin_users_id_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
--------------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
admin_users_id_seq | 28 | 1 | 2147483647 |
1 | 1 | 0 | f | t
(1 row)

main_v0_8=# select count(*) from admin_users;
count
-------
28
(1 row)

--

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