yowch: dumpRules(): SELECT failed for table website.

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: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."

* 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