Postgres Pro
Downloads
Home   >   mailing lists

Thread: Corrupt indices on already-dropped table (could not open relation with OID ...)

Corrupt indices on already-dropped table (could not open relation with OID ...)

From
Craig de Stigter
Date:
Hi folks

Somehow we've ended up with a few corrupt indices in our database. We've previously dropped the table they were on, but the indices are still there (kind of):


Trying to drop the indices gives us:

drop index "v_0000038e_GEOMETRY";
ERROR:  could not open relation with OID 9590980


Looking up that index in pg_class:

select oid, relname from pg_class where relname = 'v_0000038e_GEOMETRY';
   oid   |       relname       
---------+---------------------
 9590993 | v_0000038e_GEOMETRY


And looking up those OIDs in pg_depend:

select * from pg_depend where objid = 9590993;
 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+---------+----------+------------+----------+-------------+---------
    1259 | 9590993 |        0 |       1259 |  9590980 |           3 | a
    1259 | 9590993 |        0 |       2616 |    20506 |           0 | n


But that table doesn't exist anymore (that's okay, we dropped it earlier):

select * from pg_class where oid = 9590980 or relname = 'v_0000038e'; 
(0 rows)


Restarting the database didn't help, unfortunately.

I'm a bit hesitant to try the fix mentioned at the following URL since it involves deleting things from system tables:

Any suggestions for a nicer approach? Or can someone who knows tell me if its okay to follow the instructions at that url, without breaking anything?

Thanks

Craig de Stigter

--
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: Corrupt indices on already-dropped table (could not open relation with OID ...)

From
Tom Lane
Date:
Craig de Stigter <craig.destigter@koordinates.com> writes:
> Somehow we've ended up with a few corrupt indices in our database.

What PG version is this exactly?  Do you have any idea how you got into
this state?  (Database crashes, system crashes, whatever?)  We've seen
a few similar reports before, but never with enough clarity to identify
the bug, if it is a bug.

> I'm a bit hesitant to try the fix mentioned at the following URL since it
> involves deleting things from system tables:
> http://javadave.blogspot.com/2005/06/could-not-open-relation-in-postgresql.html

It'd be safer to dump and reload the database.  However, given that you
already removed the underlying table, I don't see a reason to be
terribly concerned about the consistency of the entries about this
index.

            regards, tom lane

Re: Corrupt indices on already-dropped table (could not open relation with OID ...)

From
Craig de Stigter
Date:

What PG version is this exactly?  Do you have any idea how you got into
this state?

Using PostgreSQL 8.3.7-0ubuntu8.10.1  from the Intrepid repository.
version() is PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.2-1ubuntu11) 4.3.2

No database crashes or system restarts were involved. We accidentally had two transactions open which were writing new rows to the table and then creating the same indices. One failed with a 'could not open relation with OID X' error and the other continued but failed for some unrelated reason. When we dropped the table and tried to recreate the table we noticed the indices were still there.


I don't see a reason to beterribly concerned about the consistency of the entries about this index.

The only issue is that we do want to be able to create that table again...

Thanks a bunch
Craig de Stigter
--
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com