Re: ERROR: cache lookup failed for relation - Mailing list 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@yahoo.com>
To: pgsql-general@postgresql.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
touse pg_resetxlog for transactions).  What went wrong is ??? but most importantly, I want to access that table as that
holdslots of work.   

I also ran

[postgres@localhost ~]$ pg_filedump mod/base/16388/4273964 >  4273964.out
[postgres@localhost ~]$ cat 4273964.out | grep ERROR
[postgres@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
relfilenodewhich 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
thecommandline to a backup directory, and when I restored a backup from last time, now I get these error messages.... I
amnot using slony, not restoring a backup from a different version, perhaps (I amonly human, but this is unlikely, but
needsto be pondered ) perhaps made a backup without shutting down the backend (???? could i be that stupid??).  Also,
priorto 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 (?
canthat be related ?).   

I also have ran
[postgres@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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general