Thread: yowch: dumpRules(): SELECT failed for table website.
while doing a pg_dump of a table after postgresql made a mess of itself: dumpRules(): SELECT failed for table website. Explanation from backend: 'ERROR: cache lookup of attribute 1 in relation9892634 failed '. Guys, there has to be a simple command to fix a corrupted database. I'm really killing myself over here trying to mix REINDEX, VACUUM along with creating temp tables and reinserting the data which gives me: dumpRules(): SELECT failed for table webmaster. Explanation from backend: 'ERROR: cache lookup of attribute 2 in relation9892495 failed '. :( Yup, we're still willing to pay for support. The database isn't even active but seems to be corrupting itself just by running these administrative commands. Would anyone like access to the box? I'm currently recompiling a what I hope is 7.0.1 to give it a shot. thanks, -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
* Hiroshi Inoue <Inoue@tpf.co.jp> [000524 02:40] wrote: > > -----Original Message----- > > From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On > > Behalf Of Alfred Perlstein > > > > while doing a pg_dump of a table after postgresql made a mess of itself: > > > > dumpRules(): SELECT failed for table website. Explanation from > > backend: 'ERROR: cache lookup of attribute 1 in relation 9892634 failed > > '. > > > > Guys, there has to be a simple command to fix a corrupted database. > > > > I'm really killing myself over here trying to mix REINDEX, VACUUM > > along with creating temp tables and reinserting the data which gives me: > > > > How did you issue REINDEX command ? postmaster -p 1080 -o "-O -P" was run then: psql -p 1080 webcounter REINDEX DATABASE webcounter force; -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein <bright@wintelcom.net> writes in pgsql-hackers@postgresql.org: > while doing a pg_dump of a table after postgresql made a mess of itself: > dumpRules(): SELECT failed for table website. Explanation from > backend: 'ERROR: cache lookup of attribute 1 in relation 9892634 > failed '. I just got a message like that earlier this afternoon. My problem was that I had created a view and later dropped and recreated one of the tables the view referenced. Dropping and recreating the view fixed things.
* Hiroshi Inoue <Inoue@tpf.co.jp> [000524 02:58] wrote: > > -----Original Message----- > > From: Alfred Perlstein [mailto:bright@wintelcom.net] > > > > * Hiroshi Inoue <Inoue@tpf.co.jp> [000524 02:40] wrote: > > > > -----Original Message----- > > > > From: pgsql-hackers-owner@hub.org > > [mailto:pgsql-hackers-owner@hub.org]On > > > > Behalf Of Alfred Perlstein > > > > > > > > while doing a pg_dump of a table after postgresql made a mess > > of itself: > > > > > > > > dumpRules(): SELECT failed for table website. Explanation from > > > > backend: 'ERROR: cache lookup of attribute 1 in relation > > 9892634 failed > > > > '. > > > > > > > > Guys, there has to be a simple command to fix a corrupted database. > > > > > > > > I'm really killing myself over here trying to mix REINDEX, VACUUM > > > > along with creating temp tables and reinserting the data > > which gives me: > > > > > > > > > > How did you issue REINDEX command ? > > > > postmaster -p 1080 -o "-O -P" > > was run > > then: > > > > psql -p 1080 webcounter > > REINDEX DATABASE webcounter force; > > > > Hmm,shutdown postmaster and invoke standalone postgres. > > postgres -O -P webmaster > REINDEX DATABASE webcounter force; > ^D gah! ~/scripts % postgres -O -P webmaster DEBUG: Data Base System is starting up at Wed May 24 02:24:49 2000 DEBUG: Data Base System was shut down at Wed May 24 02:24:46 2000 DEBUG: Data Base System is in production state at Wed May 24 02:24:49 2000 FATAL 1: Database "webmaster" does not exist in the system catalog. FATAL 1: Database "webmaster" does not exist in the system catalog. not good :( -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
* SL Baur <steve@beopen.com> [000524 02:59] wrote: > Alfred Perlstein <bright@wintelcom.net> writes in pgsql-hackers@postgresql.org: > > > while doing a pg_dump of a table after postgresql made a mess of itself: > > > dumpRules(): SELECT failed for table website. Explanation from > > backend: 'ERROR: cache lookup of attribute 1 in relation 9892634 > > failed '. > > I just got a message like that earlier this afternoon. My problem was > that I had created a view and later dropped and recreated one of the > tables the view referenced. Dropping and recreating the view fixed > things. I'm not using views afaik. There seems to be a serious corruption problem when a transaction is aborted, I'll see if I can have a reproduceable bug report tomorrow. Afaik it has to do with a transaction aborting after inserting or updating into a table. Something seems to go seriously wrong. We're also getting some problems when we don't "SET ENABLE_SEQSCAN=OFF;" for certain queries, Postgresql takes a really unoptimal path and will loop forever. Btw, is there any way to specify an abort timeout for a query if it's taking too long to just fail? -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
* Hiroshi Inoue <Inoue@tpf.co.jp> [000524 03:05] wrote: > > > Hmm,shutdown postmaster and invoke standalone postgres. > > > > > > postgres -O -P webmaster > > > REINDEX DATABASE webcounter force; > > > ^D > > > > gah! > > > > ~/scripts % postgres -O -P webmaster > > Sorry,webcounter instead of webmaster. > > > DEBUG: Data Base System is starting up at Wed May 24 02:24:49 2000 > > DEBUG: Data Base System was shut down at Wed May 24 02:24:46 2000 > > DEBUG: Data Base System is in production state at Wed May 24 > > 02:24:49 2000 > > FATAL 1: Database "webmaster" does not exist in the system catalog. > > FATAL 1: Database "webmaster" does not exist in the system catalog. > > > > not good :( ugh, it's late for me over here, I should have noticed "database" rather than "table" but i've already fixed it via moving the data to another table. I'm wondering if there's a way to get a unique value into a table? this caused some problems: CREATE TABLE "data" ( "d" varchar(256) PRIMARY KEY, "d_id" serial ); because after I reloaded the table from: insert into data select * from data_backup; then tried to insert into 'data' using only values for 'd' then it barfed because it was trying to use values from the serial that were already in the table. is there a way around this? using OID doesn't seem right, but seems to be the only "safe" way to get a truly unique key to use as a forien key that I've seen. any suggestions? -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
On Wed, May 24, 2000 at 03:33:39AM -0700, Alfred Perlstein wrote:
> I'm wondering if there's a way to get a unique value into a table?
>
> this caused some problems:
>
> CREATE TABLE "data" (
> "d" varchar(256) PRIMARY KEY,
> "d_id" serial
> );
>
> because after I reloaded the table from:
>
> insert into data select * from data_backup;
>
> then tried to insert into 'data' using only values for 'd' then it barfed
> because it was trying to use values from the serial that were already
> in the table.
>
> is there a way around this? using OID doesn't seem right, but seems to
> be the only "safe" way to get a truly unique key to use as a forien key
> that I've seen.
>
> any suggestions?
>
Right, I assume this is after you recreated the table? That created a new
sequence behind the serial for d_id, which needs to be updated after you
insert explicit values into the id field. here's my standard fix for that
SELECT setval('data_d_id_seq',max(d_id)) from data;
The name of the sequence is <tablename>_<serial field name>_seq,
trimmed to fit in NAMEDATALEN (default 30). If you created the table
with a different name, that's how the sequence is named (they're not
automatically renamed, or dropped, with their associated table)
I do this whenever I load data into a table manually. Hmm, it might be
possible to setup a trigger (or rule?) to handle the non-default case
(i.e., whenever a serial values is actually provided) and do this
automatically. It'd only need to fire if the inserted/updated value is
greater than currval of the sequence. Hmm...
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005