Skip site navigation (1) Skip section navigation (2)

Re: pg_class.relnamespace NOT IN pg_namespace.oid

From: Ireneusz Pluta <ipluta(at)wp(dot)pl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pg_class.relnamespace NOT IN pg_namespace.oid
Date: 2012-02-27 21:57:04
Message-ID: 4F4BFC30.8010209@wp.pl (view raw or flat)
Thread:
Lists: pgsql-general
W dniu 2012-02-27 21:59, Tom Lane pisze:
> Ireneusz Pluta<i(dot)(dot)(at)wp(dot)pl>  writes:
>> It apperas that I have some entries in pg_class that have relnamespace not appearing in
>> pg_namespace. So pg_dump fails with "pg_dump: schema with OID 52072764 does not exist", I guess.
> That's a bit disturbing --- do you have any idea what triggered that?

Not exactly. This is an environment where a user running his periodic reports, per logic of his 
script, for each run creates a new schema which then serves as a separated runtime sandbox. The 
schema after such a run is left alone uselessly unless one eventualy wants to look into for 
comparing with other runs or debugging, I guess. Just a subject of further garbage collecting. It 
may happen that not each run finishes succesfully, particularly in cases of manual runs on a 
development/debugging course and Ctrl-C hits. The orphaned tables seem to be the same as appearing 
in another schemas I see here. They are not complete set of what can be seen in another schemas, 
just two out of much more.
>> How to get rid of or fix these damaged entries? Just try to delete from pg_class manually (and then
>> delete files pointed to by their relfilenode)? Or create a new schema and update
>> pg_class.relnamespace to the oid of the new schema and then inspect and drop?
> I'd do the latter I think.  Keep in mind that there are probably also
> entries in pg_depend linking the tables to the schemas.  If your goal is
> only to get to a clean dumpable state and then dump and reload the
> database, you probably don't need to worry about fixing pg_depend.
> However, if you intend to keep on using the database without a reload,
> it'd be prudent to make sure pg_depend is straightened out as well.

I found rows of missing namespace oid in pg_depend.refobjid. I understand that I update them to oid 
of a newly created empty schema?

But, as in the menatime I played with the case separately on a test database, I found also pg_type 
entries need fixing. I was not aware of pg_depend, but found that after only tweaking both pg_class, 
and pg_type the database got dumpable.

In response to

Responses

pgsql-general by date

Next:From: Josh HemannDate: 2012-02-27 22:07:35
Subject: PL/Python on Postgres 9.1
Previous:From: James B. ByrneDate: 2012-02-27 21:45:13
Subject: Re: Having a problem with RoR-3.1.1 and Pg-9.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group