Thread: pg_dump: schema with OID 559701082 does not exist
Hi, I get the following error when I call: # pg_dump db pg_dump: schema with OID 559701082 does not exist this happens with 8.0.1 on RHEL 3.0. I cannot dump the database neither with --schema-only or --data-only. What can I do? Thanks for help, Dirk
Dirk Lutzebäck <lutzeb@aeccom.com> writes:
> I get the following error when I call:
> # pg_dump db
> pg_dump: schema with OID 559701082 does not exist
I suspect a corrupted catalog entry. Look through your system catalogs
for an entry referring to namespace 559701082 --- you want to look in
pg_class.relnamespace, pg_proc.pronamespace, etc.
regards, tom lane
Dirk Lutzebäck <lutzeb@aeccom.com> writes:
> The problem I'm facing is the following:
> cs1=# select relname from pg_class where relname like 'bm%';
> relname
> -----------------------------------
> bm_pagehits_1133271374_047421_941
> bm_pagehits_1133271378_920896_129
> bm_pagehits_1133282272_744243_536
> bm_pagehits_1133284780_743839_884
> (4 rows)
> cs1=# drop table bm_pagehits_1133271374_047421_941;
> ERROR: table "bm_pagehits_1133271374_047421_941" does not exist
Uh, are you sure these are in a schema that's in your search path?
regards, tom lane
Yes, I think so. What search path do you mean? These tables were all temporary tables. Dirk Tom Lane wrote: > Dirk Lutzebäck <lutzeb@aeccom.com> writes: > >>The problem I'm facing is the following: > > >>cs1=# select relname from pg_class where relname like 'bm%'; >> relname >>----------------------------------- >> bm_pagehits_1133271374_047421_941 >> bm_pagehits_1133271378_920896_129 >> bm_pagehits_1133282272_744243_536 >> bm_pagehits_1133284780_743839_884 >>(4 rows) > > >>cs1=# drop table bm_pagehits_1133271374_047421_941; >>ERROR: table "bm_pagehits_1133271374_047421_941" does not exist > > > Uh, are you sure these are in a schema that's in your search path? > > regards, tom lane
Dirk Lutzebäck <lutzeb@aeccom.com> writes:
> Yes, I think so. What search path do you mean? These tables were all
> temporary tables.
In that case they're most likely *not* in your search path, unless your
session happens to have seized on the same pg_temp_nn schema they are
in (in which case it'd have deleted them, so I guess it didn't).
I'd suggest joining to pg_namespace to determine which schema(s) they
are in, and then deleting with the explicit schema, ie, DROP TABLE
pg_temp_nnn.bm_pagehits_... Note you'll probably need to be superuser
to do this.
Or you could just ignore them, because they'll get zapped automatically
next time the containing pg_temp schema gets reused.
regards, tom lane
I have found and deleted an entry with pg_class.relnamespace=559701082 but nowhere else. I still cannot dump the schema. Is there something like a system catalog integrity checker? The problem I'm facing is the following: cs1=# select relname from pg_class where relname like 'bm%'; relname ----------------------------------- bm_pagehits_1133271374_047421_941 bm_pagehits_1133271378_920896_129 bm_pagehits_1133282272_744243_536bm_pagehits_1133284780_743839_884 (4 rows) cs1=# drop table bm_pagehits_1133271374_047421_941; ERROR: table "bm_pagehits_1133271374_047421_941" does not exist cs1=# drop table bm_pagehits_1133271378_920896_129; ERROR: table "bm_pagehits_1133271378_920896_129" does not exist cs1=# drop table bm_pagehits_1133282272_744243_536; ERROR: table "bm_pagehits_1133282272_744243_536" does not exist cs1=# drop table bm_pagehits_1133284780_743839_884; ERROR: table "bm_pagehits_1133284780_743839_884" does not exist Tom Lane wrote: > Dirk Lutzebäck <lutzeb@aeccom.com> writes: > >>I get the following error when I call: >># pg_dump db >>pg_dump: schema with OID 559701082 does not exist > > > I suspect a corrupted catalog entry. Look through your system catalogs > for an entry referring to namespace 559701082 --- you want to look in > pg_class.relnamespace, pg_proc.pronamespace, etc. > > regards, tom lane
Hi Tom,<br /><br /> I have now deleted every temp table I know from pg_temp_nnn using your approach but still can't
dumpthe schema:<br /><br /> pg_dump: schema with OID 559701082 does not exist<br /><br /> I could'nt find any reference
to 559701082 in pg_class, pg_namespace or pg_proc.<br /><br /> Regards,<br /><br /> Dirk<br /><br /> Tom Lane wrote:
<blockquotecite="mid9989.1133298436@sss.pgh.pa.us" type="cite"><pre wrap="">Dirk Lutzebäck <a
class="moz-txt-link-rfc2396E"href="mailto:lutzeb@aeccom.com"><lutzeb@aeccom.com></a> writes: </pre><blockquote
type="cite"><prewrap="">Yes, I think so. What search path do you mean? These tables were all
temporary tables. </pre></blockquote><pre wrap="">
In that case they're most likely *not* in your search path, unless your
session happens to have seized on the same pg_temp_nn schema they are
in (in which case it'd have deleted them, so I guess it didn't).
I'd suggest joining to pg_namespace to determine which schema(s) they
are in, and then deleting with the explicit schema, ie, DROP TABLE
pg_temp_nnn.bm_pagehits_... Note you'll probably need to be superuser
to do this.
Or you could just ignore them, because they'll get zapped automatically
next time the containing pg_temp schema gets reused.
regards, tom lane </pre></blockquote><br /><div class="moz-signature">-- <br /><small><i>This email and any
filestransmitted with it are confidential and intended solely for the use of the individual or entity to whom they are
addressed.If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its
contents,but should return it to the sender immediately and delete your copy from your system. Thank you for your
cooperation.</i></small><p><b>DirkLutzebäck</b> <a class="moz-txt-link-rfc2396E"
href="mailto:lutzeb@aeccom.com"><lutzeb@aeccom.com></a>Tel +49.30.5362.1635 Fax .1638<br /> CTO <a
href="http://www.aeccom.com">AEC/communicationsGmbH</a>, Berlin, Germany </div>
Dirk Lutzebäck <lutzeb@aeccom.com> writes: > I have now deleted every temp table I know from pg_temp_nnn using your > approach but still can't dump the schema: > pg_dump: schema with OID 559701082 does not exist > I could'nt find any reference to 559701082 in pg_class, pg_namespace or > pg_proc. Better keep looking then. See http://www.postgresql.org/docs/8.1/static/catalogs.html (adjust link for your PG version) to find out which catalogs reference pg_namespace. regards, tom lane
Yes, I finally found the reference in pg_type. Thanks for your help! Regards, Dirk Tom Lane wrote: > Dirk Lutzebäck <lutzeb@aeccom.com> writes: > >>I have now deleted every temp table I know from pg_temp_nnn using your >>approach but still can't dump the schema: >>pg_dump: schema with OID 559701082 does not exist >>I could'nt find any reference to 559701082 in pg_class, pg_namespace or >>pg_proc. > > > Better keep looking then. See > http://www.postgresql.org/docs/8.1/static/catalogs.html > (adjust link for your PG version) to find out which catalogs reference > pg_namespace. > > regards, tom lane