Thread: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid
A medium-length story short, this query returns non-zero: select count(distinct typnamespace) from pg_type where not exists (select 1 from pg_namespace where oid = pg_type.typnamespace); I did a very brief search in all the release notes for 8.3.5 to 8.3.14, but have not found precisely what I was looking for (searches for namespace, schema, type, and corruption). This was discovered when performing a pg_dump of this user's database, whereby pg_dump complained when trying to dump types for lack of a good catalog entry to nab the namespace name from. In our case, two namespaces seem to be affected. The user of this database was never privileged enough to even perform CREATE SCHEMA, to my knowledge, and in fact only have the schema (owned by the postgres superuser) that they began with. Is it safe to perform an UPDATE on pg_type to give entries a valid typnamespace? Is there any forensic evidence I can grab before doing that to assist in figuring out the mechanism for this bug, if applicable? Cheers. -- fdr
Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid
From
Daniel Farina
Date:
On Mon, Feb 21, 2011 at 10:43 PM, Daniel Farina <daniel@heroku.com> wrote: > A medium-length story short, this query returns non-zero: > > select count(distinct typnamespace) from pg_type where =A0not exists > (select 1 from pg_namespace where oid =3D pg_type.typnamespace); > > I did a very brief search in all the release notes for 8.3.5 to > 8.3.14, but have not found precisely what I was looking for (searches > for namespace, schema, type, and corruption). It may also be useful information to know that no recent shenanigans have happened on this server: it's been up continuously for about 500 days. That doesn't mean something interesting did not occur a very long time ago, and I'm currently asking around for any notes about interesting things that have occurred on this machine. --=20 fdr
Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid
From
Daniel Farina
Date:
On Mon, Feb 21, 2011 at 10:46 PM, Daniel Farina <daniel@heroku.com> wrote: > It may also be useful information to know that no recent shenanigans > have happened on this server: it's been up continuously for about 500 > days. That doesn't mean something interesting did not occur a very > long time ago, and I'm currently asking around for any notes about > interesting things that have occurred on this machine. From what I can tell, people only see this problem with pg_dump, which is interesting. This symptom has a very long history: http://archives.postgresql.org/pgsql-general/2004-02/msg00970.php http://archives.postgresql.org/pgsql-admin/2006-10/msg00192.php http://archives.postgresql.org/pgsql-bugs/2005-11/msg00305.php http://archives.postgresql.org/pgsql-bugs/2010-01/msg00087.php http://archives.postgresql.org/pgsql-general/2011-02/msg00334.php Something I'm not sure any of these mention that's very interesting in my case that may be crucial information: In my case, there are two "missing" pg_namespace entries, and both have the same missing relations. Both of them have "credible" looking OIDs (in the hundreds of thousands, and one after the other) as well as "credible" looking ancillary information: * all owners are correct * there are exactly four relfrozenxid values. They look like this: SELECT distinct c.relnamespace, relfrozenxid::text FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE nspname IS NULL; relnamespace | relfrozenxid --------------+-------------- 320204 | 0 320204 | 6573962 320527 | 0 320527 | 6574527 Note that relfrozenxic increases along with the oid, which is generally what you'd expect. Some relations have no frozen xid. * This is affecting the following features the user has used: sequences, relations, indexes (in this case, they are all _pkey indexes) * There's also a valid version of all these relations/objects that *are* connected to the schema that's alive and expected. As such, \dt, \dn seem to work as one would expect. The modern namespace OID is 378382, which is in line with a smooth monotonic increase over time. * Each relkind has its own relfilenode, and they all do appear to exist in the cluster directory. I didn't spot any big ones from a random sampling (I can write a comprehensive one on request), but some were 8KB and some were 16KB, which might suggest that some data is in some of them. More forensics tomorrow. Sadly, for whatever reason, pg_dump --schema=public didn't seem to help me out. We do need a workaround if we wish to keep doing forensics. -- fdr
On Tue, Feb 22, 2011 at 8:48 AM, Daniel Farina <daniel@heroku.com> wrote: > =A0The modern namespace OID is > 378382, which is in line with a smooth monotonic increase over time. Wait, what? namespace OID is the OID of the schema. The OID of an object doesn't change over the lifetime of the object, it's a unique identifier. So the only reason this would be increasing like this would be if you're creating schemas continually over time. What actually is going on in this database? --=20 greg
Re: Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid
From
Tom Lane
Date:
Daniel Farina <daniel@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'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. > Sadly, for whatever reason, pg_dump --schema=public didn't seem to > help me out. We do need a workaround if we wish to keep doing > forensics. 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 ... regards, tom lane
Re: Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid
From
Daniel Farina
Date:
On Tue, Feb 22, 2011 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Daniel Farina <daniel@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. =A0There seems to be some well-hidden bug whereby dropping an object > sometimes fails to drop (some of?) its dependencies. =A0I'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. =A0Could you > provide a more detailed explanation of the usage pattern in this > database? =A0I 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. =A0So it tends to notice inconsistencies that you > might not notice in regular usage of the database. =A0It'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 =3D '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. --=20 fdr