Thread: pg_inherits: not found, but visible
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
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
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
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
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
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
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
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
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
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
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