Re: [HACKERS] AutoVacuum Behaviour Question

Tom Lane wrote:
> 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.

TODO updated:

* Prevent long-lived temporary tables from causing frozen-xid advancement
starvation

The problem is that autovacuum cannot vacuum them to set frozen xids;
only the session that created them can do that.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +