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(at)austehc(dot)unimelb(dot)edu(dot)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(at)austehc(dot)unimelb(dot)edu(dot)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(at)austehc(dot)unimelb(dot)edu(dot)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(at)austehc(dot)unimelb(dot)edu(dot)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(at)austehc(dot)unimelb(dot)edu(dot)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