Re: pg_class.relnamespace NOT IN pg_namespace.oid

W dniu 2012-02-27 21:59, Tom Lane pisze:
> Ireneusz Pluta<i..@wp.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.