Re: Bug: Unreferenced temp tables disables vacuum to update xid

On Monday 07 January 2008 09:10:24 Joshua D. Drake wrote:
> On Mon, 7 Jan 2008 11:58:29 -0500 (EST)
>
> Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > > Ah -- interesting. This is a known issue, but we haven't found a
> > > > solution yet.
> > >
> > > Is there bug number?
> >
> > I assume it is this TODO item:
> >
> > o 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.
> >
> > http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php
> >
> > but am confused how the fix worked. Have all of these backends been
> > active for 1 billion transactions?
>
> Well it certainly appears that the TODO item is related. However there
> are a couple of differences.
>
> 1. I had to manually vacuum because we had already hid xidStoplimit.
>
> 2. Postgres has been restarted multiple times and it made zero
> difference.
>
> E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent
> when it happens.

After a fresh start of postgres, there should be no temp tables, so would a
work around to this at least be at postmaster start to (for a lack of a
better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in
interactive mode? Doing this would at least have allowedthe manual vacuum to
do what it needed and not have caused confusion on the part of the user?
Also it would have greatly reduced the total time to resolution, and not
requiring hacking the backend to get there.

>
> Sincerely,
>
> Joshua D. Drake

--
Darcy Buskermolen
Command Prompt, Inc.
+1.503.667.4564 X 102
http://www.commandprompt.com/
PostgreSQL solutions since 1997