[ADMIN] Schema with OID 0 does not exist in pg_dump

[Lukasz Brodziak]

Hi,

When I try to make a dump of a database I get the error pg_dump: schema with
OID 0 does not exist. How can I find the schema with this OID? pg_namespace
has no info on this.

[Tom Lane]

Post by Lukasz Brodziak
When I try to make a dump of a database I get the error pg_dump: schema with
OID 0 does not exist. How can I find the schema with this OID?

Well, you can't, because it doesn't exist ;-). What you need to find
out is what is trying to reference it. I'd start by looking for a zero
in one of the catalog columns that reference pg_namespace.oid, such as
pg_class.relnamespace.

regards, tom lane

[Lukasz Brodziak]

Ok, I've found the rows theyh were placed in pg_operator table. After
deletion I get the following error in pg_dump:
pg_dump: Error message from server: ERROR: operator is not unique: smallint
<> integer
LINE 1: ...d = relowner) AS rolname, relchecks, (reltriggers <> 0) AS r...
^
HINT: Could not choose a best candidate operator. You may need to add
explicit type casts.
pg_dump: The command was: SELECT c.tableoid, c.oid, relname, relacl,
relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
relowner) AS rolname, relchecks, (reltriggers <> 0) AS relhastriggers,
relhasindex, relhasrules, relhasoids, relfrozenxid, NULL AS reloftype,
d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM
pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace,
array_to_string(c.reloptions, ', ') AS reloptions, NULL AS toast_reloptions
FROM pg_class c LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid =
c.tableoid AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid =
c.tableoid AND d.deptype = 'a') WHERE relkind in ('r', 'S', 'v', 'c') ORDER
BY c.oid
pg_dump: *** aborted because of error

Post by Tom Lane

Post by Lukasz Brodziak
When I try to make a dump of a database I get the error pg_dump: schema

with

Post by Lukasz Brodziak
OID 0 does not exist. How can I find the schema with this OID?

Well, you can't, because it doesn't exist ;-). What you need to find
out is what is trying to reference it. I'd start by looking for a zero
in one of the catalog columns that reference pg_namespace.oid, such as
pg_class.relnamespace.
regards, tom lane

[Tom Lane]

Post by Lukasz Brodziak
Ok, I've found the rows theyh were placed in pg_operator table. After
pg_dump: Error message from server: ERROR: operator is not unique: smallint
<> integer
LINE 1: ...d = relowner) AS rolname, relchecks, (reltriggers <> 0) AS r...
^
HINT: Could not choose a best candidate operator. You may need to add
explicit type casts.

Kinda looks like you deleted some rows you shouldn't have ... the above
is about what I'd expect to happen if you'd removed the int2 <> int4
operator. Do you know what you removed, exactly? You might try
comparing the contents of pg_operator with another, non-broken database,
and putting back whatever's missing.

regards, tom lane

[Lukasz Brodziak]

Hi,

Just managed to complete the pg_dump by replacing the broken pg_operator
file with the one from non-broken database then I read what You've written.
Now I can go further with the DB :-) Thanks a lot for the guidelines Tom.

Regards
Luke

Post by Lukasz Brodziak

Post by Lukasz Brodziak
Ok, I've found the rows theyh were placed in pg_operator table. After

smallint

Post by Lukasz Brodziak
<> integer
LINE 1: ...d = relowner) AS rolname, relchecks, (reltriggers <> 0) AS

r...

Post by Lukasz Brodziak
^
HINT: Could not choose a best candidate operator. You may need to add
explicit type casts.

Kinda looks like you deleted some rows you shouldn't have ... the above
is about what I'd expect to happen if you'd removed the int2 <> int4
operator. Do you know what you removed, exactly? You might try
comparing the contents of pg_operator with another, non-broken database,
and putting back whatever's missing.
regards, tom lane