Re: [GENERAL] AutoVacuum Behaviour Question

Alvaro Herrera <alvherre@commandprompt.com> writes:
>>>> No, it isn't.  Please add a TODO item about it:
>>>> * Prevent long-lived temp tables from causing frozen-Xid advancement
>>>> starvation
>>
> Jeff Amiel wrote:
>> 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.

I'm not convinced there's a huge problem here.  Surely Jeff's app is
going to either vacuum or truncate those temp tables occasionally;
otherwise they'll bloat to the point of uselessness.  Either action
will fix the problem.

The real issue is that the app has to remember to do that.  Perhaps
a better TODO item would be
    * Find a way to autovacuum temp tables
though I admit I have no clue how to do that without giving up most
of the performance advantages of temp tables.

            regards, tom lane