Re: 8.3.5: Types with typnamespace pointing at non-existent pg_namespace oid - Mailing list pgsql-bugs

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