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:

dumpRules(): SELECT failed for table webmaster.  Explanation from backend: 'ERROR:  cache lookup of attribute 2 in relation 9892495 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."

Wed, 24 May 2000 02:10:58 -0700
Alfred Perlstein <bright@wintelcom.net>
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.


24 May 2000 18:19:33 +0900
SL Baur <steve@beopen.com>
Re: yowch: dumpRules(): SELECT failed for table website.
* 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."

Wed, 24 May 2000 02:43:07 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: yowch: dumpRules(): SELECT failed for table website.
* 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."

Wed, 24 May 2000 03:00:00 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: yowch: dumpRules(): SELECT failed for table website.
* 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."

Wed, 24 May 2000 03:10:06 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: Re: yowch: dumpRules(): SELECT failed for table website.
* 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."

Wed, 24 May 2000 03:33:39 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: yowch: dumpRules(): SELECT failed for table website.
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

Wed, 24 May 2000 09:59:39 -0500
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Re: yowch: dumpRules(): SELECT failed for table website.