Thread: pg_dump: could not find namespace with OID

In fact I have fixed my database (source code rocks!) but I thought I would 
share my experience with the list in case anyone else sees this.  I am 
running PostgreSQL 7.3.2 on i386--netbsdelf.  I created two user defined 
types, glaccount and section.  See attached SQL file that creates the type.  
Somehow I created two rows for each type in the pg_opclass table, one correct 
and one with NULL entries in opcnamespace and opcowner.  That's what was 
causing the problem I was seeing.  I simply removed the bogus rows and 
pg_dump worked again.

As far as I can tell there was nothing else affected but if anyone wants me to 
test something I still have a copy of the broken database around to try 
things on.

Should those fields be NOT NULL to protect against this?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Somehow I created two rows for each type in the pg_opclass table, one
> correct and one with NULL entries in opcnamespace and opcowner.
> Should those fields be NOT NULL to protect against this?

They are.  But 7.3 has a bug that keeps it from enforcing NOT NULL on
certain system catalogs.  Did you make the bogus row by means of a
manual INSERT into pg_opclass?  If so, you're just seeing that bug.

If you were able to cause CREATE OPERATOR CLASS to make such a broken
entry, I'd be real interested to see how.
        regards, tom lane

PS: you'd best take another look at the RESTRICT and JOIN entries for
your operators...