Re: Temporary tables prevent autovacuum, leading to XID wraparound

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Mar 6, 2018 at 6:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Now as for the problem originally stated, step 1 alone doesn't fix it,
>> and there's reason not to like that change much. Forcing backends to
>> clear their temp schemas immediately on connection will slow down
>> connection times, and for applications that never make any temp tables,
>> that's just a dead loss (though admittedly it might not be too expensive
>> in that case).

> I think that's a little short-sighted. I think we really want temp
> tables of no-longer-running backends to go away as soon as possible;
> that should be viewed as a gain in and of itself.

We already have autovacuum taking care of that, and as I stated, asking
backends to do it is provably insufficient. The right path is to make
autovacuum cover the cases it's missing today.

> I don't
> really share your concern about performance; one extra syscache lookup
> at backend startup isn't going to break the bank.

If it were just that, I wouldn't be worried either. But it's not.
Once the pg_namespace row exists, which it will in an installation
that's been in use for for any length of time, you're going to have
to actively search for entries in that schema. I'm not sure how
expensive a performDeletion() scan that finds nothing to do really
is, but for sure it's more than the syscache lookup you expended to
find the pg_namespace row.

It's perhaps also worth reminding people that this whole discussion
pertains only to crash recovery; if the previous owner of the temp
schema had exited cleanly, it would've cleaned things up. I'm unexcited
about adding overhead for crash recovery to the normal connection
code path when it's not necessary, and even more so when it's neither
necessary nor sufficient.

regards, tom lane