To: "John Lister" <john.lister-ps@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Should autovacuum do a database wide vacuum near transaction
	limit?
In-reply-to: <97DC79D65AF14ABCB98B26C2F8BA5755@squarepi.com> 
References: <97DC79D65AF14ABCB98B26C2F8BA5755@squarepi.com>
Comments: In-reply-to "John Lister" <john.lister-ps@kickstone.com>
	message dated "Sat, 22 Jan 2011 12:13:25 +0000"
Date: Sat, 22 Jan 2011 14:34:16 -0500
Message-ID: <28994.1295724856@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"John Lister" <john.lister-ps@kickstone.com> writes:
>>> Was this expected behaviour with temporary tables?

>> It's more expected behavior when you have long running transactions.
>> I haven't seen it caused by temp tables.  Was the parent process in a
>> really long transaction or just open a long time without one?

>  The first thing I checked was for open transactions, but alas there were 
> none. I suspect the process had been open a long time without creating any 
> transactions, but don't know which process it was at this point, the 
> connection was owned by my colleague so need to check with him or look for 
> dead applications...

It's possible for temp tables to remain behind after a backend crash.
Not clear if that's your situation or not, but it does happen.
Such tables will get cleaned out whenever the owning pg_temp_nnn schema
is next used to hold temp tables --- but if it's a high-numbered schema
that might not happen for a long time.  Also, in 8.4 and up, autovacuum
will forcibly drop orphaned temp tables once they get old enough to
start creating xid-wraparound issues.

			regards, tom lane