Thread: Schema with OID 0 does not exist in pg_dump
Hi,
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
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.
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
Lukasz Brodziak <lukasz.brodziak@gmail.com> writes:
> 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
Ok, I've found the rows theyh were placed in pg_operator table. After deletion I get the following error in pg_dump:
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
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
2011/9/1 Tom Lane <tgl@sss.pgh.pa.us>
Lukasz Brodziak <lukasz.brodziak@gmail.com> writes:Well, you can't, because it doesn't exist ;-). What you need to find
> 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?
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
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
Lukasz Brodziak <lukasz.brodziak@gmail.com> writes:
> 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.
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
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
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
2011/9/5 Tom Lane <tgl@sss.pgh.pa.us>
Lukasz Brodziak <lukasz.brodziak@gmail.com> writes:> Ok, I've found the rows theyh were placed in pg_operator table. AfterKinda looks like you deleted some rows you shouldn't have ... the above
> 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.
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
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"