"Could not open relation" in PostgreSQL
A coworker was getting "ERROR: could not open relation with OID nnnnnnn" in PostgreSQL. I poked around, and indeed, the query he was issuing was trying to use an index that I had dropped the day before. Shutting down psql and restarting it fixed it.
It seemed odd that psql would apparently cache a query plan, rather than letting the server handle it. Unless the problem was in the connection, and not psql, and recycling psql just recycled the connection, which was probably the case, now that I think about it.
It seemed odd that psql would apparently cache a query plan, rather than letting the server handle it. Unless the problem was in the connection, and not psql, and recycling psql just recycled the connection, which was probably the case, now that I think about it.
posted by Dave at Wednesday, June 01, 2005
3 Comments:
Thank you for your comment, Dave! It proved useful.
Might be useful to add that if the same issue happens while using sql-postres mode in emacs, it might be necessary to restart emacs -- a simple exit from the *SQL* buffer session did not fix the issue (probably because the psql client is not restarted when closing an *SQL* session).
-Alexei
Ooops, please ignore my last comment. I am still having the issue after restarting psql client and after restarting Postgre server : an index exists, but cannot be dropped due to the above error. Don't know how to fix it yet. Sorry about that.
So, I ended up deleting the row from pg_class with relname='NAME_OF_CORRUPT_INDEX' and also deleteing the row from pg_index with indrelid=OID_REPORTED_IN_ERROR_MESSAGE.
After that I was able to create a new index with the same name. Hopefully this did not corrupt the state of the DB -- it appears to be working fine.
Post a Comment
<< Home