Re: Should autovacuum do a database wide vacuum near transaction limit?

"John Lister" <john(dot)lister-ps(at)kickstone(dot)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