Re: ERROR: cache lookup failed for relation

From: joe speigle <joeofclew(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: ERROR: cache lookup failed for relation
Date: 2008-09-09 01:25:24
Message-ID: 816820.25870.qm@web65605.mail.ac4.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

List and (most importantly) somebody with the same problem,

I finally got access to my table by performing these random steps:

1) /usr/local/pg8.1/bin/pg_resetxlog -x 0x5000000 -l 0x1,0x0,0xC9 mod
[ read the manpage and figure out the args ]

2) following the steps here:
http://people.planetpostgresql.org/greg/index.php?/archives/88-Performing-a-reindex-of-the-system-tables.html

which was reindexing the system tables.

happily, there is no visible bad news even on running "\set verbosity verbose"

----- Original Message ----
From: joe speigle <joeofclew(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Sent: Monday, September 8, 2008 9:54:36 PM
Subject: [GENERAL] ERROR: cache lookup failed for relation

Hello list,

I have contributed the mysql2pgsql script to the community at http://pgfoundry.org/projects/mysql2pgsql/

I am wondering why I pg_class can see it, but " AND pg_catalog.pg_table_is_visible(c.oid)" (as in \d korean_english)
queries are returning false. My database has been acting strangely since copying files from backup, including having to use pg_resetxlog for transactions). What went wrong is ??? but most importantly, I want to access that table as that holds lots of work.

I also ran

[postgres(at)localhost ~]$ pg_filedump mod/base/16388/4273964 > 4273964.out
[postgres(at)localhost ~]$ cat 4273964.out | grep ERROR
[postgres(at)localhost ~]$ ls -lah *.out
-rw-r--r-- 1 postgres postgres 13M 2008-09-08 20:58 4273964.out

on the table but can't spot any irregularities.

where should I look? how can I get to the data ??? apparently, it's not corrupt?

(NOTE: also has happened similar messages from some other tables which I dropped)

================details ==========================

I am getting

mod=# \d korean_english ;
ERROR: cache lookup failed for relation 4273964

and cannot run \dt anymore on this database, nor dump it (dumping gives ..... another error,
(((((((((((((((((((((((
ERROR: relation "modpgwebuser.korean_english" does not exist
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: relation "modpgwebuser.korean_english" does not exist
pg_dump: The command was: LOCK TABLE modpgwebuser.korean_english IN ACCESS SHARE MODE
))))))))))))))))))

I assume I look like this for 4273964 ....

mod=# select relname,relnamespace,relfilenode,relpages,reltuples from pg_class where relfilenode= 4273964 ;

relname | relnamespace | relfilenode | relpages | reltuples
----------------+--------------+-------------+----------+-----------
korean_english | 16394 | 4273964 | 2689 | 188763
(1 row)

That is the correct # of tuples for the (unaccessable) table.

I searched through the main list of pg_catalog tables on www.postgresql.org but I am not using oid's and it's the relfilenode which matches, is that correct?

This error came after I restored from backup copy. I "pg_ctl -D mod stop" my database and then copy the files using the commandline to a backup directory, and when I restored a backup from last time, now I get these error messages.... I am not using slony, not restoring a backup from a different version, perhaps (I amonly human, but this is unlikely, but needs to be pondered ) perhaps made a backup without shutting down the backend (???? could i be that stupid??). Also, prior to this I was deleting rows using a php script which was acting strangely by not outputting results to the browser, but after the statement ran, was prompting me to download the file 'index.php' which upon opening was empty (? can that be related ?).

I also have ran
[postgres(at)localhost ~]$ /usr/local/pg8.1/bin/pg_resetxlog -x 0x5000000 -l 0x1,0x0,0xC9 mod

on the database because of the following error, which was taken care of by the above.
ERROR: cache lookup failed for relation 1690989
ERROR: cache lookup failed for relation 1690989
mod=# ERROR: xlog flush request 0/B9F9919C is not satisfied --- flushed only to 0/9E8BCFA4
CONTEXT: writing block 45 of relation 1663/16388/1249

I tried to find dependencies and what-not by the following queries, but came out empty-handed.

mod=# select * from pg_depend where refobjid=4273964 ;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
(0 rows)

mod=# select * from pg_depend where objid=4273964 ;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
(0 rows)

mod=# select * from pg_depend where refobjid=4273964 ;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
(0 rows)

mod=# select * from pg_trigger where tgconstrrelid=4273964 ;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs
---------+--------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+--------
(0 rows)

mod=# select * from pg_index where indexrelid=4273964 ;
indexrelid | indrelid | indnatts | indisunique | indisprimary | indisclustered | indkey | indclass | indexprs | indpred
------------+----------+----------+-------------+--------------+----------------+--------+----------+----------+---------
(0 rows)

Thank you,

Joseph.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-09-09 01:30:29 Re: Postgres 8.3.1 RowExclusiveLock With JDBC XA
Previous Message Peter Cai 2008-09-09 01:04:02 Re: Very weird problem of "order by" in postgresql