Re: AutoVacuum Behaviour Question - Mailing list pgsql-general

Alvaro Herrera wrote:
> > Bruce McAlister wrote:
> >
>> >> 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?
>> >>
> >
> > Do this:
> >
> > select relname, nspname
> > from pg_class join pg_namespace on (relnamespace = pg_namespace.oid)
> > where pg_is_other_temp_schema(relnamespace);
> >
> > It returns something like
> >
> >  relname |  nspname
> > ---------+-----------
> >  foo     | pg_temp_1
> > (1 fila)
> >
> > So it is session with ID 1.  You can then find out the PID with
> >
> > alvherre=# select pg_stat_get_backend_pid(1);
> >  pg_stat_get_backend_pid
> > -------------------------
> >                    13416
> > (1 fila)
> >
> >
> >
okidoki, I tried this:

blueface-crm=# select relname, nspname from pg_class join pg_namespace
on (relnamespace = pg_namespace.oid) where
pg_is_other_temp_schema(relnamespace);
 relname  |  nspname
----------+------------
 temp4295 | pg_temp_63
(1 row)

blueface-crm=# select pg_stat_get_backend_pid(63);
 pg_stat_get_backend_pid
-------------------------
                    6661
(1 row)

blueface-crm=# select datname, client_addr, client_port from
pg_stat_activity where procpid = '6661';
    datname     | client_addr | client_port
----------------+-------------+-------------
 whitelabel-ibb | 10.6.0.181  |        1587
(1 row)

Is that correct? If it is then I'm really confused, how can a connection
to the whitelabel-ibb database create temporary tables in the
blueface-crm database?


>> >> 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?
>> >>
> >
> > Right -- but sadly RESET SESSION (actually called DISCARD because RESET
> > was getting too overcrowded) is only available in 8.3.
> >
> > But you are right, temp tables and connection pools are a rather serious
> > issue, it seems.
> >
> >
Is there a way we can actually work around this in the 8.2 release? Is
there a way or a command that we could run manually over the connection
that would cleanup the session environment? Is it possible to do it
programatically?
>>> >>> 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.
>> >>
> >
> > Humm, I would like to think that the default freeze age is 2 billion ...
> > [checks the code]  but no, you are right, the default is 200 million.
> >
> >
Is 2 billion a better value to set it to?