Thread: ERROR: cannot insert duplicate... on VACUUM ANALYZE
Hello-
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
This primary key is referring to the following table:
main_v0_8=# \d admin_users
Table "admin_users"
Attribute | Type |
Modifier
--------------+--------------------------+------------------------------------------------------
id | smallint | not null default
nextval('admin_users_id_seq'::text)
name | character varying(255) | not null
password | character varying(20) | not null
email | character varying(255) |
fullname | character varying(255) |
usertype | smallint | not null default 1
mygroup | smallint | not null default 1
active | boolean | not null default 't'
remoteip | character varying(15) |
sessioncode | character varying(20) |
lastconnect | timestamp with time zone |
lastip | character varying(15) |
firstconnect | timestamp with time zone |
Indices: admin_users_name_key,
admin_users_pkey
Any suggestions on how to track down what is happening? I am not
actually trying to insert anything to this table, so this error message
appears a bit strange to me.
One other bit of info, one (of the approx 40) users of this database is
receiving the same error message and is unable to work, while the other
users are having no problems. Strange.
--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
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:
> 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/
***-*--*----*-------*------------*--------------------*---------------
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
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/ ***-*--*----*-------*------------*--------------------*---------------
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
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:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster