ERROR: cannot insert duplicate... on VACUUM ANALYZE

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 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/
***-*--*----*-------*------------*--------------------*---------------

Hello,

I get the following error

pl=# select * from person where id !=-1;
ERROR: Unable to identify an operator '!=-' for types 'int8' and 'int4'
You will have to retype this query using an explicit cast

pl=# select * from person where id =-1;
id | name | last_update_time
----+------+------------------
(0 rows)

However this works fine

pl=# select * from person where id != -1;

This looks like a parser error, note the space added in the select that
works ??

Dave

Hello,

I get the following error

pl=# select * from person where id !=-1;
ERROR: Unable to identify an operator '!=-' for types 'int8' and 'int4'
You will have to retype this query using an explicit cast

pl=# select * from person where id =-1;
id | name | last_update_time
----+------+------------------
(0 rows)

However this works fine

pl=# select * from person where id != -1;

This looks like a parser error, note the space added in the select that
works ??

Dave

Jon Lapham <lapham@extracta.com.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

"Dave Cramer" <Dave@micro-automation.net> writes:

pl=# select * from person where id !=-1;
ERROR: Unable to identify an operator '!=-' for types 'int8' and 'int4'
You will have to retype this query using an explicit cast

This is not a bug. It's a consequence of wanting to allow user-definable
multicharacter operator names. See the rules about allowable operator
names,
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax.html#SQL-SYNTAX-OPERATORS

regards, tom lane

PS: no, != is not an SQL-standard operator name ... <> is.

Tom Lane wrote:

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

Jon Lapham <lapham@extracta.com.br> writes:

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?

Try searching the mailing list archives for "glibc" and "strcoll" over
the past year or so. We've seen core dumps and corrupted indexes that
trace to this.

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

I think that only applies if you need to reindex a system-catalog index.

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

This still looks like corrupted data, but one can't tell from this which
table it's in. Try VACUUM VERBOSE.

regards, tom lane

Tom Lane wrote:

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

This still looks like corrupted data, but one can't tell from this which
table it's in. Try VACUUM VERBOSE.

This database is huge, so I won't fill the archives with the entire
output of VACUUM VERBOSE, but below are a few snippets.

This is an example of what most of the "non admin_users" indexes look
like, notice the "Deleted 0":

NOTICE: Index plate_map_pkey: Pages 2; Tuples 18: Deleted 0. CPU

0.00s/0.00u sec.

These are the two "admin_users" indexes, notice the "Deleted 141":
NOTICE: Index admin_users_pkey: Pages 2; Tuples 29: Deleted 141. CPU
0.00s/0.00u sec.
NOTICE: Index admin_users_name_key: Pages 2; Tuples 29: Deleted 141.
CPU 0.00s/0.00u sec.
ERROR: No one parent tuple was found

These are the last 3 messages that appear when running the VACUUM
VERBOSE, the 2 NOTICE's and the final ERROR.

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Mol�culas Naturais, Rio de Janeiro, Brasil
email: lapham@extracta.com.br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

Hm, SQL92 like <> for the 'not equal' operator and that parses OK:

test=# select count(*) from foo where c<>-1;
count
-------
3
(1 row)

It's probably better to use <> ... I can sort of imagine the parser getting confused in your case...

Don't know if this helps?

Allan.

Dave Cramer wrote: