Corrupt indices on already-dropped table (could not open relation with OID ...)
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:
http://javadave.blogspot.com/2005/06/could-not-open-relation-in-postgresql.html
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
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
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