Re: Tables dissapearing

On Aug 27, 2007, at 4:08 PM, Tom Lane wrote:

> 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

Also, in your original post you mentioned a "proprietal CMS system".
Is this proprietary to your company or one that you've purchased?
The fact that the same table going on multiple dbs all being run by
that CMS system certainly makes it worthy of suspicion.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com