Re: AutoVacuum Behaviour Question

Jeff Amiel wrote:
>
> Bruce Momjian wrote:
>>>
>>> No, it isn't. Please add a TODO item about it:
>>> * Prevent long-lived temp tables from causing frozen-Xid advancement
>>> starvation
>
> Can somebody explain this one to me? because of our auditing technique, we
> have many LONG lived temp tables.....(one per pooled connection)...so as
> long as the pool isn't disturbed, these temp tables can exist for a long
> time (weeks....months?)

Hmm. The problem is that the system can't advance the frozen Xid for a
database when there are temp tables that live for long periods of time.
Autovacuum can't vacuum those tables; if the app vacuums them itself
then there's no problem, but you can only vacuum them in the same
session that creates it.

The problem with a frozen Xid (datfrozenxid) that doesn't advance is of
Xid-wraparound nature. The system eventually shuts itself down to
prevent data loss, so if those temp tables live a really long life, you
could be subject to that. (The immediate symptom is that pg_clog
segments do not get recycled, which is not serious because it's just
wasted disk space, and it's not a lot).

> (previous thread about our use of temp tables and autovacuum/xid issues)
> http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php
> http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php

Sorry, I'm offline ATM and can't check those.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)