Re: Bug: Unreferenced temp tables disables vacuum to update xid - Mailing list pgsql-hackers

From Darcy Buskermolen
Subject Re: Bug: Unreferenced temp tables disables vacuum to update xid
Date
Msg-id 200801071037.19483.darcyb@commandprompt.com
Whole thread Raw
In response to Re: Bug: Unreferenced temp tables disables vacuum to update xid  ("Joshua D. Drake" <jd@commandprompt.com>)
Responses Re: Bug: Unreferenced temp tables disables vacuum to update xid  ("Joshua D. Drake" <jd@commandprompt.com>)
Re: Bug: Unreferenced temp tables disables vacuum to update xid  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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@momjian.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


pgsql-hackers by date:

Previous
From: Gregory Stark
Date:
Subject: Re: Bug: Unreferenced temp tables disables vacuum to update xid
Next
From: "Joshua D. Drake"
Date:
Subject: Re: Bug: Unreferenced temp tables disables vacuum to update xid