Re: ERROR: cannot insert duplicate... on VACUUM ANALYZE - Mailing list pgsql-general

Tom Lane wrote:

> Jon Lapham <lapham@extracta.com.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@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------