Wednesday, June 01, 2005

"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.

3 Comments:

Anonymous Alexei Colin said...

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

3:42 PM  
Anonymous Alexei Colin said...

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.

3:50 PM  
Anonymous Alexei Colin said...

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.

5:50 PM  

Post a Comment

<< Home