Re: [HACKERS] AutoVacuum Behaviour Question

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. +