Re: [HACKERS] AutoVacuum Behaviour Question

Alvaro Herrera <alvherre(at)commandprompt(dot)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