Thread: pg_dump: schema with OID xxxxxxxx does not exist
Running pg_dump gives this error: pg_dump: schema with OID 37647910 does not exist I've found a variation of this on the mailing list, but I'm not entirely sure what to do about it. I found this OID by running: select * from pg_type where typnamespace = 37647910; and two entries: typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault -----------------------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+-----------+------------+-------------+-------------+----------+------------+------------+-------------+-----------+----------+---------------+------------ entity_browse_bid_seq | 37647910 | 1005 | 4 | t | c | t | , | 37894096 | 0 | record_in | record_out | record_recv | record_send | i | p | f | 0 | -1 | 0 | | entity_browse | 37647910 | 1005 | 4 | t | c | t | , | 37894098 | 0 | record_in | record_out | record_recv | record_send | i | p | f | 0 | -1 | 0 | | (2 rows) Does this mean that I can just delete these rows from this table for the dump to work? Will deleting these rows affect anything else? Can you tell that I'm not really sure what I am doing - I just need to get a database from one server to another. Thanks for any help you could give me, Peter
Peter Neish <peter@austehc.unimelb.edu.au> writes: > entity_browse_bid_seq | 37647910 | 1005 | 4 | t | > c | t | , | 37894096 | 0 | record_in | > record_out | record_recv | record_send | i | p | > f | 0 | -1 | 0 | | > entity_browse | 37647910 | 1005 | 4 | t | > c | t | , | 37894098 | 0 | record_in | > record_out | record_recv | record_send | i | p | > f | 0 | -1 | 0 | | > (2 rows) > Does this mean that I can just delete these rows from this table for the > dump to work? Will deleting these rows affect anything else? These are composite-type entries, which means there should be associated entries in pg_class --- with OIDs 37894096 and 37894098 respectively. Did you check to see if those are still there? We've seen scattered reports of table rowtype entries not being removed when the owning table is dropped, but no one's ever been able to make a reproducible case :-(. Do you know anything about the history or usage of these tables? regards, tom lane
Tom Lane wrote: > Peter Neish <peter@austehc.unimelb.edu.au> writes: > >> entity_browse_bid_seq | 37647910 | 1005 | 4 | t | >> c | t | , | 37894096 | 0 | record_in | >> record_out | record_recv | record_send | i | p | >> f | 0 | -1 | 0 | | >> entity_browse | 37647910 | 1005 | 4 | t | >> c | t | , | 37894098 | 0 | record_in | >> record_out | record_recv | record_send | i | p | >> f | 0 | -1 | 0 | | >> (2 rows) >> > > >> Does this mean that I can just delete these rows from this table for the >> dump to work? Will deleting these rows affect anything else? >> > > These are composite-type entries, which means there should be > associated entries in pg_class --- with OIDs 37894096 and 37894098 > respectively. Did you check to see if those are still there? > Yes, they are there. Can I safely delete the rows from both the pg_type and the pg_class tables? > We've seen scattered reports of table rowtype entries not being removed > when the owning table is dropped, but no one's ever been able to make a > reproducible case :-(. Do you know anything about the history or usage > of these tables? > I am afraid I inherited this database from a previous admin, so I know nothing of its history - sorry... Peter.
Peter Neish <peter@austehc.unimelb.edu.au> writes: > Tom Lane wrote: >> These are composite-type entries, which means there should be >> associated entries in pg_class --- with OIDs 37894096 and 37894098 >> respectively. Did you check to see if those are still there? > Yes, they are there. Can I safely delete the rows from both the pg_type > and the pg_class tables? Oh, that's interesting. Are there entries in pg_attribute for these tables? Look for attrelid = 37894096 or 37894098. What PG version is this exactly? regards, tom lane
Tom Lane wrote: > Peter Neish <peter@austehc.unimelb.edu.au> writes: > >> Tom Lane wrote: >> >>> These are composite-type entries, which means there should be >>> associated entries in pg_class --- with OIDs 37894096 and 37894098 >>> respectively. Did you check to see if those are still there? >>> > > >> Yes, they are there. Can I safely delete the rows from both the pg_type >> and the pg_class tables? >> > > Oh, that's interesting. Are there entries in pg_attribute for these > tables? Look for attrelid = 37894096 or 37894098. What PG version > is this exactly? > > regards, tom lane > > Yes, 32 entries. # select * from pg_attribute where attrelid = 37894096 or attrelid = 37894098; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount ----------+---------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------+--------------+------------+------------- 37894096 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0 37894096 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0 ... 37894098 | bid | 23 | -1 | 4 | 10 | 0 | -1 | -1 | t | p | f | i | t | t | f | t | 0 (32 rows) Its runnign PG Version 7.4.7 Cheers, Peter
Peter Neish <peter@austehc.unimelb.edu.au> writes: > Tom Lane wrote: >> Oh, that's interesting. Are there entries in pg_attribute for these >> tables? Look for attrelid = 37894096 or 37894098. What PG version >> is this exactly? > Yes, 32 entries. Wow, that's the first case I've heard of where whole tables were left around with no matching schema. You'll probably find you have to drop both the pg_class and pg_type rows before pg_dump will work --- AFAIR you shouldn't have to clean out pg_attribute. Since it's hard to tell exactly what's going on here, I'd suggest a pg_dumpall/initdb/reload sequence to be sure there isn't any other undetected corruption. Be sure to check that the dump contains everything you expect though. > Its runnign PG Version 7.4.7 An update would be a good idea --- the 7.4 branch is up to 7.4.14, and we didn't make those patch releases just because we had nothing else to do. It seems at least possible that this problem might be explained by this 7.4.8 bug fix: 2005-05-07 17:33 tgl * src/backend/: access/heap/hio.c, access/nbtree/nbtpage.c, access/nbtree/nbtree.c, commands/vacuumlazy.c (REL7_4_STABLE): Repair very-low-probability race condition between relation extension and VACUUM: in the interval between adding a new page to the relation and formatting it, it was possible for VACUUM to come along and decide it should format the page too. Though not harmful in itself, this would cause data loss if a third transaction were able to insert tuples into the vacuumed page before the original extender got control back. The connection is that if this bug had resulted in loss of pg_depend rows relating these two tables to their schema, then it would have been possible to drop the schema without the tables being removed. regards, tom lane
> >> Yes, 32 entries. >> > > Wow, that's the first case I've heard of where whole tables were left > around with no matching schema. You'll probably find you have to drop > both the pg_class and pg_type rows before pg_dump will work --- AFAIR > you shouldn't have to clean out pg_attribute. > > Thanks Tom. I was able to run pg_dump successfully after clearing those rows from the pg_class and pg_type tables. > Since it's hard to tell exactly what's going on here, I'd suggest a > pg_dumpall/initdb/reload sequence to be sure there isn't any other > undetected corruption. Be sure to check that the dump contains > everything you expect though. > Will do this. Sounds like there's some hairy stuff going on and a good cleanout would be a good idea (as well as a PG upgrade). Thanks again for your help. Peter