Re: AutoVacuum Behaviour Question

From: Bruce McAlister <bruce(dot)mcalister(at)blueface(dot)ie>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: AutoVacuum Behaviour Question
Date: 2007-06-28 14:41:39
Message-ID: 4683C8A3.2070803@blueface.ie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-06-28 14:45:55 Re: using PREPAREd statements in CURSOR
Previous Message Alvaro Herrera 2007-06-28 14:08:06 Re: Execution variability

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce McAlister 2007-06-28 15:40:13 Re: AutoVacuum Behaviour Question
Previous Message Tom Lane 2007-06-28 14:25:54 Re: Doc update for pg_start_backup