Re: FATAL: could not open relation xxx: No such file or directory

On Tue, Apr 15, 2008 at 9:36 AM, Mikko Partio <mpartio(at)gmail(dot)com> wrote:

>
>
> On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio <mpartio(at)gmail(dot)com> wrote:
>
> > Hello all
> >
> > my struggle with the database continues (see earlier thread titled "too
> > many trigger records found for relation xyz").
> >
> > Today, I created yet another to table to the same database. Everything
> > went ok, no errors or anything, but when I checked pg_tables -view I saw two
> > tables with the same name. Instantly I queried pg_class and yes there was
> > again two tables with same oid. I dropped the table before anything more
> > serious could happen, but then postgres started to complain of "cache lookup
> > failed for relation ...". I disconnected my psql session and tried to
> > reconnect but failed to do so:
> >
> > 2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL: could not open relation
> > 1663/16386/544592: No such file or directory
> >
> > Indeed, there is no such file in that directory. I'm guessing that file
> > is connected to the table I just dropped. Now, is there anything to do to
> > get the database back online? I can still connect to other databases in the
> > same instance
>
>
> The cure was to create file 1663/16386/54459 8K in size with dd. The file
> in question was in fact the oid index on pg_class -- I had issued a REINDEX
> on pg_class just a moment before and apparantly something went wrong and the
> system lost track of the index. There was also two entries in pg_index for
> index pg_class_oid_index. After I removed the other entry and reindexed
> pg_class and pg_index, everything seems to be working ok. All the symptoms
> indicate that perhaps a xid wraparound had happened, but there is no such
> warning in logs and age(datfrozenxid) went never higher than say
> 250,000,000. Does anybody have a clue what might have happene
>

And now it has happened again. A CLUSTER operation was done on a table
succesfully, afterwards when trying to access the table I get the error

2008-04-17 13:05:30 EEST [8435]: [32-1] ERROR: could not open relation
1663/16386/359232: No such file or directory

Seems to me like VACUUM FULL, REINDEX and CLUSTER change the filename of a
table and/or index and then fail to record the new name to system
catalogues. Is this a known deficiency what can I do to stop this behaviour?

Regards

Mikko