Re: Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid

On Tue, Feb 22, 2011 at 8:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Daniel Farina <daniel(at)heroku(dot)com> writes:
>> From what I can tell, people only see this problem with pg_dump, which
>> is interesting. This symptom has a very long history:
>
> Yeah.  There seems to be some well-hidden bug whereby dropping an object
> sometimes fails to drop (some of?) its dependencies.  I'm still looking
> for a reproducible case, or even a hint as to what the trigger condition
> might be.
>
>> In my case, there are two "missing" pg_namespace entries, and both
>> have the same missing relations.
>
> Uh, what do you mean by "same missing relations"?

There are an identical set of relations (including quasi-relations
like indexes and sequences) with relnames and most other properties
that are identical between the versions that are tied with each of the
two missing namespaces. There's also a superset of those (but that may
be partially or totally explained by the current set being more recent
as the application as grown) that are seen with a normal looking
pg_namespace record. All three copies of these formations seem to have
very sensible pg_class/pg_type/pg_sequence formations in their
respective relnamespaces.

>> * There's also a valid version of all these relations/objects that
>> *are* connected to the schema that's alive and expected.
>
> And this isn't making any sense to this onlooker, either.  Could you
> provide a more detailed explanation of the usage pattern in this
> database?  I speculate that what you mean is the user periodically
> drops and recreates a schema + its contents, but please be explicit.

We run quite a large number of databases, and I unfortunately think
that this particular fault has occurred in what could be called
ancient history, as far as log retention is concerned. Sadly our
investigation will have to be limited to what we can find at this
time, although we can probably slowly work our way to being able to
catch this one in the act. We might also be able to run a catalog
query across other databases to get a sense as to the frequency of the
problem.

It may be worth noting in this case that the user does not own the
schema that is thought to be dropped (or, in fact, any schemas at
all), so DROP SCHEMA as issued by them is not likely a culprit. I will
ask around as to what administrative programs we possess that might
fool with the schema. Still, such a program is probably run many times
across many databases. This is why I'm scratching my head about the
fact that two sets of such bogus relnamespace references were
produced.

Although I have no idea how such a thing could happen, is it possible
that both copies come from one occurrence of the bug?

> Yeah, pg_dump is written to glom onto everything listed in the catalogs
> and sort it out later.  So it tends to notice inconsistencies that you
> might not notice in regular usage of the database.  It's sort of hard to
> avoid, since for example a --schema switch depends on seeing which
> objects belong to which schema ...

I figured as much, although if it were written slightly differently
(starting from oid where nspname = 'public') then perhaps it would not
run into problems. I was meaning to poke at pg_depend to see if
anything interesting can be seen in there.

I'll probably hack up pg_dump to try to step around the yucky
relations so we can ensure that this database gets a clean-looking
restore elsewhere before we put the strange-looking database on ice --
permanently, if you think there is no value in having it around.

--
fdr