On Tue, Feb 22, 2011 at 8:54 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Daniel Farina <dan...@heroku.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
