From: Tom Lane <tgl@sss.pgh.pa.us>
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?
Date: Sat, 22 Jan 2011 14:34:16 -0500
Message-ID: <28994.1295724856@sss.pgh.pa.us> (raw)
In-Reply-To: <97DC79D65AF14ABCB98B26C2F8BA5755@squarepi.com>
References: <97DC79D65AF14ABCB98B26C2F8BA5755@squarepi.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, john.lister-ps@kickstone.com
Subject: Re: Should autovacuum do a database wide vacuum near transaction limit?
In-Reply-To: <28994.1295724856@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox