Skip site navigation (1) Skip section navigation (2)

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

From: Darcy Buskermolen <darcyb(at)commandprompt(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Bug: Unreferenced temp tables disables vacuum to update xid
Date: 2008-01-07 18:37:18
Message-ID: 200801071037.19483.darcyb@commandprompt.com (view raw or flat)
Thread:
Lists: 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(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

In response to

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2008-01-07 18:37:38
Subject: Re: Bug: Unreferenced temp tables disables vacuum to update xid
Previous:From: Gregory StarkDate: 2008-01-07 18:28:49
Subject: Re: Bug: Unreferenced temp tables disables vacuum to update xid

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group