Re: Tables dissapearing

Kamil Srot <kamil(dot)srot(at)nlogy(dot)com> writes:
> # select xmin, age(xmin) from pg_class;
> xmin | age
> -----------+------------
> 2 | 2147483647
> 2 | 2147483647
> 2 | 2147483647
> 2 | 2147483647
> 2 | 2147483647
> 2 | 2147483647
> 236838019 | 539453
> 2 | 2147483647
> 2 | 2147483647
> 231899309 | 5478163
> etc.

I see no indication here that there's anything wrong at all.

The rows with xmin = 2 (ie, FrozenTransactionID) are evidently pg_class
rows that have never been modified since the database was created.
There isn't anything wrong with them. age() is returning INT_MAX for
them to denote that they are "infinitely far in the past", but they are
in no danger of being invalidated by wraparound.

The newer rows indicate that your current transaction counter is around
237 million, which is barely a tenth of the way to wraparound. While
it's conceivable that it already wrapped around (ie, you've really
executed 237 million plus some multiple of 2^32 transactions), I think
it's more probable that no wraparound has happened and your problem is
entirely unrelated to that. I believe this in part because having just
one table "disappear" is not the likely result of an XID wraparound
problem, and in part because 8.2 has got very strong defenses against
allowing wraparound failure to occur: it will shut down first, and it
will do a whole lot of bleating and autovacuuming before that.

You haven't yet showed us any details of what happens. What exactly do
you mean by the table "disappearing"? Can you select from it? Do you
see a row for it in pg_class?

regards, tom lane