Cannot insert a duplicate key into unique index pg_calss_oid_index

[Stephane Charette]

I'm running into a few database problems, and having a hard time
solving them. Version() tells me PostgreSQL 7.2.3 on
i686-pc-linux-gnu, compiled by GCC 2.95.2.

The problem: vacuum fails (returns 1) and logs "Cannot insert a
duplicate key into unique index pg_class_oid_index". This is on a new
installation, after a few reboots and a new database having been
created.

I followed the steps to start a standalone backend in the reference
guide under "REINDEX", but it doesn't seem to be working. This is
what I tried:

$ /usr/local/pgsql/bin/postgres -D /usr/local/stuff/data -O -P log
DEBUG: database system was shut down at 2003-01-31 22:10:57 GMT
DEBUG: checkpoint record is at 0/1AF664
DEBUG: redo record is at 0/1AF664; undo record is at 0/0; shutdown
TRUE
DEBUG: next transaction id: 196; next oid: 16632
DEBUG: database system is ready

POSTGRES backend interactive interface
$Revision: 1.245.2.3 $ $Date: 2002/09/30 20:47:22 $

backend> reindex database log force
NOTICE: relation 1247 was reindexed
NOTICE: relation 1249 was reindexed
ERROR: Cannot create unique index. Table contains non-unique values
backend>

What does this mean, and how do I fix it?

Thanks,

Stephane Charette
stephanecharette <nospam> AT telus <nospam> .net

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Stephane Charette
backend> reindex database log force
NOTICE: relation 1247 was reindexed
NOTICE: relation 1249 was reindexed
ERROR: Cannot create unique index. Table contains non-unique values
backend>
What does this mean, and how do I fix it?

It would appear that you've actually got some duplicate rows (at least
with duplicate OIDs) in pg_class.

To find 'em, try
select oid from pg_class group by oid having count(*) > 1;
then you can do
select ctid,oid,* from pg_class where oid = <whatever>;

Assuming they are actually duplicates (everywhere except for the ctid
field), you should be able to do
delete from pg_class where ctid = '<whatever>';
to zap all but one. Then reindex.

I would advise an update to 7.2.4, which fixes one known possible cause
of duplicate rows.

If you can figure out a sequence that reproduces the problem, btw, I
would like to know about it. The known cause involves a system crash
at just the wrong time; it's not clear if your "reboots" were due to
system failure or not.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org