Tom Lane wrote:
> 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.
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@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +