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

I originally sent this message from my gmail account yesterday as we
were having issues with our work mail servers yesterday, but seeing
that it hasn't made it to the lists yet, I'm resending from my
registered address. You have my apologies if you receive this twice.

"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) and one that fails
reindexing with "ERROR: xxxxx is an index" where is an index on a
completely different table. 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 get the same errors). 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 and before that we are going to run a dump excluding
those tables, restore that on a separate machine and see if these
errors crop up there anywhere. 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

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com