Обсуждение: Cannot insert a duplicate key into unique index pg_calss_oid_index
Cannot insert a duplicate key into unique index pg_calss_oid_index
От
charette@writeme.com (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
charette@writeme.com (Stephane Charette) writes: > 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