Re: AutoVacuum Behaviour Question

Alvaro Herrera wrote:
> Bruce McAlister wrote:
>> Martijn van Oosterhout wrote:
>>> All the values here look OK, except one:
>>>
>>> On Thu, Jun 28, 2007 at 07:50:36AM +0100, Bruce McAlister wrote:
>>>> blueface-crm=# select oid, relfrozenxid from pg_class where relkind in
>>>> ('r', 't');
>>>> oid | relfrozenxid
>>>> ---------+--------------
>>>> 2570051 | 2947120794
>>> Whatever this table is, the freeze XID isn't getting updated for some
>>> reason...
>
> Doh.
>
>> This looks like a temporary relation,
>>
>> temp4295 | 2947120794
>>
>> Is there a way we can manually force these to update?
>
> No. Only the session that created the temp table can vacuum it.
> Autovacuum skips temp tables. I guess the only thing you can do here is
> close that session.
>

How could I go about finding out which session created the temporary table?

So this could be a potential issue for autovacuum then. If, for example,
our environment uses connection pooling. Then these connections are
persistent to the database? From Martjin's comments, I would assume then
that the connection pooling driver (ODBC/NPGSQL etc etc) should
implement the "RESET SESSION" DDL after each transaction/query so that
we don't have these temporary tables lying about indefinately?

> I'm thinking that maybe should make vac_update_datfrozenxid ignore temp
> tables. But this doesn't really work, because if we were to truncate
> pg_clog there would be tuples on the temp table marked with XIDs that
> are nowhere to be found. Maybe we could make some noise about it
> though.
>
> This is a problem only in recent releases (8.2) because we started
> allowing the max freeze age be configurable.
>

I think the max/min freeze age parameters we are using here are the
default ones, I think I just uncommented them.