Re: Large Database \d: ERROR: cache lookup failed for relation ...

"Thomas F. O'Connell" <tf ( at ) o ( dot ) ptimized ( dot ) com> writes:
> I'm dealing with a database where there are ~150,000 rows in
> information_schema.tables. I just tried to do a \d, and it came back
> with this:

> ERROR: cache lookup failed for relation [oid]

> Is this indicative of corruption, or is it possibly a resource issue?

Greetings,

This message is a follow-up to Thomas's message quoted above (we're working
together on the same database). He received one response when he sent the
above message which was from Tom Lane and can be easily summarized as him
having said that that could happen tables were being created or dropped
while running the \d in psql. Unfortunately, that wasn't the case, we have
now determined that there is some corruption in our database and we are
hoping some of you back-end gurus might have some suggestions.

How we verified that there is corruption was simply to reindex all of our
tables in addition to getting the same errors when running a dump this past
weekend. We so far have a list of five tables for which reindex fails with
the error: "ERROR: could not open relation with OID xxxx" (sub xxxx with the
five different #s). After dropping all of the indexes on these tables (a
couple didn't have any to begin with), we still cannot run reindex on them.
In addition, we can't drop the tables either. We can however run alter table
statements on them. So, we have scheduled a downtime for an evening later
this week wherein we plan on bringing the database down for a REINDEX
SYSTEM. Is there anything else anyone can think of that we can do to narrow
down where the actual corruption is or how to fix it?

--
Erik Jones
mage2k(at)gmail(dot)com