Re: More than one pg_database entry for database - Mailing list pgsql-admin

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 05 July 2007 15:53
To: James Wilford
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] More than one pg_database entry for database

"James Wilford" <jwilford@mistral.net> writes:
> This shows 2 entries for "misp" with different OIDs. Only the first
> one (oid 31238435) exists in the data/base directory. So I tried to
> delete the other row but it doesn't work:

> misp=# DELETE from pg_database where oid = 6790290; DELETE 0

That's pretty strange.  I wonder if the index on pg_database.oid is
corrupt.  It certainly seems like the one on datname must be corrupt,
else it should have disallowed two such entries.

You could try deleting the bogus row by selecting it by ctid instead of
oid.  Also see if you can REINDEX pg_database (this will only work in
standalone mode I think).

What PG version is this exactly?

            regards, tom lane

Hi Tom,

Its PG version 7.3. I've just tried all your suggestions and
unfortunately I still can't delete the bogus row. I think I managed to
reindex the system tables:

backend> reindex database misp force
NOTICE:  relation 16416 was reindexed
NOTICE:  relation 1261 was reindexed
NOTICE:  relation 1255 was reindexed
NOTICE:  relation 16410 was reindexed
NOTICE:  relation 1247 was reindexed
NOTICE:  relation 1249 was reindexed
NOTICE:  relation 1259 was reindexed
NOTICE:  relation 16388 was reindexed
NOTICE:  relation 16390 was reindexed
NOTICE:  relation 16392 was reindexed
NOTICE:  relation 16394 was reindexed
NOTICE:  relation 16396 was reindexed
NOTICE:  relation 16398 was reindexed
NOTICE:  relation 16400 was reindexed
NOTICE:  relation 16402 was reindexed
NOTICE:  relation 16404 was reindexed
NOTICE:  relation 16406 was reindexed
NOTICE:  relation 16412 was reindexed
NOTICE:  relation 16418 was reindexed
NOTICE:  relation 16594 was reindexed
NOTICE:  relation 1260 was reindexed
NOTICE:  relation 16596 was reindexed
NOTICE:  relation 16598 was reindexed
NOTICE:  relation 16384 was reindexed
NOTICE:  relation 16386 was reindexed
NOTICE:  relation 1262 was reindexed
NOTICE:  relation 16408 was reindexed

However I still can't delete the row, even from standalone mode:


backend> delete from pg_database where oid = 6790290
blank
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
        ----

And trying ctid didn't work, assuming this syntax is correct:

backend> delete from pg_database where ctid = '(0,37)';
blank
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
        ----


I might try creating a new database cluster on a test box and copying
the data directory over, would this work? Could I then create a new
database and just rename the data directory to the new OID?

Thanks,

James