Horton
Menu

Thread

  1. pg_inherits: not found, but visible

    Joel Burton <jburton@scw.org> — 2001-03-20T23:38:35Z

    Postmaster crashed on me, and on restart, pg_inherits cannot be found.
I can see it in pg_class (and it shows up w/ \dS), but any attempt to
modify anything fails with "pg_inherits: No such file or directory".

I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error
above).

What could this be? Is there any hope?

Thanks!

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  2. Re: pg_inherits: not found, but visible

    Hiroshi Inoue <inoue@tpf.co.jp> — 2001-03-21T00:17:34Z

    Joel Burton wrote:
> 
> Postmaster crashed on me, and on restart, pg_inherits cannot be found.
> I can see it in pg_class (and it shows up w/ \dS), but any attempt to
> modify anything fails with "pg_inherits: No such file or directory".
> 
> I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error
> above).
> 
> What could this be? Is there any hope?
> 

Try the following queries.
1) select oid from pg_database where datname = your_db_name;
2) select oid, relfilenode from pg_class where relname = 'pg_inherits';

For example I get the followings in my environment.
1) oid = 18720
2) relfilenode(==oid) = 16567;

and I could find a $PGDATA/base/18720/16567 file.
Could you find such a file ?

regards,
Hiroshi Inoue

  3. Re: pg_inherits: not found, but visible

    Joel Burton <jburton@scw.org> — 2001-03-21T00:20:56Z

    On Wed, 21 Mar 2001, Hiroshi Inoue wrote:

> Joel Burton wrote:
> > 
> > Postmaster crashed on me, and on restart, pg_inherits cannot be found.
> > I can see it in pg_class (and it shows up w/ \dS), but any attempt to
> > modify anything fails with "pg_inherits: No such file or directory".
> > 
> > I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error
> > above).
> > 
> > What could this be? Is there any hope?
> > 
> 
> Try the following queries.
> 1) select oid from pg_database where datname = your_db_name;
> 2) select oid, relfilenode from pg_class where relname = 'pg_inherits';
> 
> For example I get the followings in my environment.
> 1) oid = 18720
> 2) relfilenode(==oid) = 16567;
> 
> and I could find a $PGDATA/base/18720/16567 file.
> Could you find such a file ?

No. I do have the db directory, and all of the other file for the existing
classes, but not this.

Any ideas why this would disappear? Or any ideas about how to get my
existing data out? (I have a dump from about 36 hours ago; it would be
nice to extract some more recent data!)

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  4. Re: pg_inherits: not found, but visible

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-21T00:32:56Z

    Joel Burton <jburton@scw.org> writes:
>> and I could find a $PGDATA/base/18720/16567 file.
>> Could you find such a file ?

> No. I do have the db directory, and all of the other file for the existing
> classes, but not this.

Hm.  You could make an empty file by that name (just 'touch' it) and
then you'd probably be able to dump (possibly after reindexing
pg_inherit's indexes again).  pg_inherits isn't a real critical table,
fortunately.

> Any ideas why this would disappear?

Interesting question, all right.  Did you have a system crash?

			regards, tom lane

  5. Re: pg_inherits: not found, but visible

    Hiroshi Inoue <inoue@tpf.co.jp> — 2001-03-21T00:38:22Z

    Joel Burton wrote:
> 
> On Wed, 21 Mar 2001, Hiroshi Inoue wrote:
> 
> > Joel Burton wrote:
> > >
> > > Postmaster crashed on me, and on restart, pg_inherits cannot be found.
> > > I can see it in pg_class (and it shows up w/ \dS), but any attempt to
> > > modify anything fails with "pg_inherits: No such file or directory".
> > >
> > > I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error
> > > above).
> > >
> > > What could this be? Is there any hope?
> > >
> >
> > Try the following queries.
> > 1) select oid from pg_database where datname = your_db_name;
> > 2) select oid, relfilenode from pg_class where relname = 'pg_inherits';
> >
> > For example I get the followings in my environment.
> > 1) oid = 18720
> > 2) relfilenode(==oid) = 16567;
> >
> > and I could find a $PGDATA/base/18720/16567 file.
> > Could you find such a file ?
> 
> No. I do have the db directory, and all of the other file for the existing
> classes, but not this.
> 

Just a confirmation. What is a result of the second query
in your current environment ? 

> Any ideas why this would disappear?

I have no idea but this is really a disastrous phenomenon.

> Or any ideas about how to get my
> existing data out? (I have a dump from about 36 hours ago; it would be
> nice to extract some more recent data!)
> 

Are you using inheritance ?

regards,
Hiroshi Inoue

  6. Re: pg_inherits: not found, but visible

    Joel Burton <jburton@scw.org> — 2001-03-21T00:42:54Z

    On Tue, 20 Mar 2001, Tom Lane wrote:

> Joel Burton <jburton@scw.org> writes:
> >> and I could find a $PGDATA/base/18720/16567 file.
> >> Could you find such a file ?
> 
> > No. I do have the db directory, and all of the other file for the existing
> > classes, but not this.
> 
> Hm.  You could make an empty file by that name (just 'touch' it) and
> then you'd probably be able to dump (possibly after reindexing
> pg_inherit's indexes again).  pg_inherits isn't a real critical table,
> fortunately.
> 
> > Any ideas why this would disappear?
>
> Interesting question, all right.  Did you have a system crash?

Ok, so I touched the file, and did a postgres -P -O reindex of the table
with force.

Going into psql then, I could select * from the table, and, not
surprisingly, nothing was in it, but I can (& did) dump my data.

For those watching, that's about 15 minutes from the sinking feeling of
'I just lost two days of work' to 'resolution and data restored'. Our
community has *damn fine* technical support! :-) Thanks, Tom, and Hiroshi,
for being so helpful so quickly.


As for your questions, no, I didn't have a system crash. I was running a
Zope page that queries several tables (show all classes, for each class,
show all instances, for each instance, show all dates, etc.); the page
normally takes about 2 minutes to pull everything together (I think that's
Zope's speed issue, not PG!) Anyway, while that was chugging away, I tried
to drop a view and recreate it, and that request just hung there for a
few minutes. The Zope page never came up, and psql notified me that I lost
my connection.

I wasn't, and haven't ever, used inheritance in this database.

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  7. Re: pg_inherits: not found, but visible

    Joel Burton <jburton@scw.org> — 2001-03-21T00:43:59Z

    On Wed, 21 Mar 2001, Hiroshi Inoue wrote:

> Joel Burton wrote:
> > 
> > On Wed, 21 Mar 2001, Hiroshi Inoue wrote:
> > 
> > > Joel Burton wrote:
> > > >
> > > > Postmaster crashed on me, and on restart, pg_inherits cannot be found.
> > > > I can see it in pg_class (and it shows up w/ \dS), but any attempt to
> > > > modify anything fails with "pg_inherits: No such file or directory".
> > > >
> > > > I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error
> > > > above).
> > > >
> > > > What could this be? Is there any hope?
> > > >
> > >
> > > Try the following queries.
> > > 1) select oid from pg_database where datname = your_db_name;
> > > 2) select oid, relfilenode from pg_class where relname = 'pg_inherits';
> > >
> > > For example I get the followings in my environment.
> > > 1) oid = 18720
> > > 2) relfilenode(==oid) = 16567;
> > >
> > > and I could find a $PGDATA/base/18720/16567 file.
> > > Could you find such a file ?
> > 
> > No. I do have the db directory, and all of the other file for the existing
> > classes, but not this.
> > 
> 
> Just a confirmation. What is a result of the second query
> in your current environment ? 

I got exactly what I would expect in a working PG db: the oid and
relfilenode matched, and were OIDs in the range of the other system tables
in the directory.

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  8. Re: pg_inherits: not found, but visible [IT GETS WORSE]

    Joel Burton <jburton@scw.org> — 2001-03-21T01:03:16Z

    Yikes. It gets weirder.

Fixed the pg_inherits problem, went back to my Zoping, trying to optimize
some views, and during another run, get an error that trelclasspq, one of
my tables, couldn't open.

Trying this out in psql, I get the same error message--the file doesn't
exist. And, getting the oid for the file, looked in the directory--and
this file is gone too!

Now, I just made a good dump of the database, so I can always go  back to
that. But this seems to be a *serious* problem in the system.

I have

Zope 2.3.1b2 (most recent version of Zope)
running on a Linux-Mandrake 7.2 box (server #1)

It has a database adapter called ZPoPy, which is the Zope version of PoPy,
a Python database adapter for PostgreSQL.

PoPy is getting data from my PostgreSQL database, which is 7.1beta4, and
served on a different Mandrake 7.2 box.

Has anyone seen anything like this?

I doubt the error is Zope *per se*, since Zope can only talk to the
database adapter, and I doubt the database adapter has the intentional
feature of delete-the-file-for-this-table in its protocol. It *could* be a
problem w/ZPoPy or PoPy; I'll send a message to their list as well.

Thanks!

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  9. Re: pg_inherits: not found, but visible [IT GETS WORSE]

    Ross Reedstrom <reedstrm@rice.edu> — 2001-03-21T01:12:56Z

    On Tue, Mar 20, 2001 at 08:03:16PM -0500, Joel Burton wrote:
> 
> Yikes. It gets weirder.
> 
> 
> I have
> 
> Zope 2.3.1b2 (most recent version of Zope)
> running on a Linux-Mandrake 7.2 box (server #1)
> 

What kind of filesystem is the pgsql data tree living on? If you do a fsck,
does anything turn up in lost+found?

Ross

  10. Re: pg_inherits: not found, but visible [IT GETS WORSE]

    Joel Burton <jburton@scw.org> — 2001-03-21T01:28:26Z

    On Tue, 20 Mar 2001, Ross J. Reedstrom wrote:

> What kind of filesystem is the pgsql data tree living on? If you do a fsck,
> does anything turn up in lost+found?
> 
> Ross

ext2, straight out of the box. It's in /var, which is a separate
partition.

fscking shows no errors, tells no lies, and nothing appears in lost+found.

Thanks,
-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

  11. Re: pg_inherits: not found, but visible [IT GETS WORSE]

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-21T05:20:45Z

    Joel Burton <jburton@scw.org> writes:
> Yikes. It gets weirder.
> Fixed the pg_inherits problem, went back to my Zoping, trying to optimize
> some views, and during another run, get an error that trelclasspq, one of
> my tables, couldn't open.
> Trying this out in psql, I get the same error message--the file doesn't
> exist. And, getting the oid for the file, looked in the directory--and
> this file is gone too!

This does not seem good.  Just to clarify: in both cases, the pg_class
row for the table is still there, but the underlying Unix file is gone?

Barring major malfeasance from your kernel, it seems like Postgres must
be issuing a delete on the wrong file when you are doing something else.
This is particularly bizarre if you are just doing create/delete view,
because in 7.1 a view hasn't got any associated file, and so no unlink()
kernel call should be issued at all.

I would recommend that you try to narrow down the events leading up to
this --- in particular, keeping a postmaster log of queries issued (-d2)
seems like a good idea.

> I doubt the error is Zope *per se*,

Zope cannot be the culprit --- there is no API for deleting a table file
without deleting its pg_class entry ;-).  But it seems possible that
some peculiar pattern of queries that they issue could be triggering a
previously-unknown Postgres bug.

I will be out of town all day tomorrow, but please see what data you can
gather.  If you can create a reproducible failure case it'd be great...

			regards, tom lane