Thread: help with corrupted database

Hello.
A memory fault in a trigger left my database
in a corrupted state:
- A temporary table listed in pg_class  was not accessible with a select
- I could not DROP it
- I deleted the record from pg_class
- I can't create temporary tables anymore  (restart/vacuum full don't help)  ERROR:  cache lookup failed for relation
1250714

Any hint?

--strk;

strk wrote:
> Hello.
> A memory fault in a trigger left my database
> in a corrupted state:
> 

>     - I can't create temporary tables anymore
>       (restart/vacuum full don't help)
>       ERROR:  cache lookup failed for relation 1250714

*What* is giving this error? Something seems to be holding onto a 
reference to (at a guess) your temporary table. Can you identify what?

--  Richard Huxton  Archonet Ltd

On Wed, Mar 23, 2005 at 01:48:11PM +0000, Richard Huxton wrote:
> strk wrote:
> >Hello.
> >A memory fault in a trigger left my database
> >in a corrupted state:
> >
> 
> >    - I can't create temporary tables anymore
> >      (restart/vacuum full don't help)
> >      ERROR:  cache lookup failed for relation 1250714
> 
> *What* is giving this error? Something seems to be holding onto a 
> reference to (at a guess) your temporary table. Can you identify what?

Whatever is called from create temp table ..

strk=# create temp table test1(a int);
ERROR:  cache lookup failed for relation 1250714
strk=# create temp table test2(a int);
ERROR:  cache lookup failed for relation 1250714

PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian)

--strk;

strk wrote:
> On Wed, Mar 23, 2005 at 01:48:11PM +0000, Richard Huxton wrote:
> 
>>strk wrote:
>>
>>>Hello.
>>>A memory fault in a trigger left my database
>>>in a corrupted state:
>>>
>>
>>>    - I can't create temporary tables anymore
>>>      (restart/vacuum full don't help)
>>>      ERROR:  cache lookup failed for relation 1250714
>>
>>*What* is giving this error? Something seems to be holding onto a 
>>reference to (at a guess) your temporary table. Can you identify what?
> 
> 
> Whatever is called from create temp table ..
> 
> strk=# create temp table test1(a int);
> ERROR:  cache lookup failed for relation 1250714
> strk=# create temp table test2(a int);
> ERROR:  cache lookup failed for relation 1250714

Have you disconnected/reconnected since deleting the temp table?

--  Richard Huxton  Archonet Ltd

On Wed, Mar 23, 2005 at 02:49:53PM +0000, Richard Huxton wrote:
> strk wrote:
> >On Wed, Mar 23, 2005 at 01:48:11PM +0000, Richard Huxton wrote:
> >
> >>strk wrote:
> >>
> >>>Hello.
> >>>A memory fault in a trigger left my database
> >>>in a corrupted state:
> >>>
> >>
> >>>    - I can't create temporary tables anymore
> >>>      (restart/vacuum full don't help)
> >>>      ERROR:  cache lookup failed for relation 1250714
> >>
> >>*What* is giving this error? Something seems to be holding onto a 
> >>reference to (at a guess) your temporary table. Can you identify what?
> >
> >
> >Whatever is called from create temp table ..
> >
> >strk=# create temp table test1(a int);
> >ERROR:  cache lookup failed for relation 1250714
> >strk=# create temp table test2(a int);
> >ERROR:  cache lookup failed for relation 1250714
> 
> Have you disconnected/reconnected since deleting the temp table?

Yes. And vacuum full'd. And pg_ctl stop/start.
--strk;

strk <strk@keybit.net> writes:
> On Wed, Mar 23, 2005 at 01:48:11PM +0000, Richard Huxton wrote:
>> *What* is giving this error? Something seems to be holding onto a 
>> reference to (at a guess) your temporary table. Can you identify what?

> Whatever is called from create temp table ..

"\set VERBOSITY verbose" might help.
        regards, tom lane