Re: Temporary tables prevent autovacuum, leading to XID wraparound

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. One, it saves disk
space. Two, it prevents them from causing wraparound problems. I
believe we've had people complain about both, but definitely the
latter.

> Now, you can argue that autovacuum's check can be fooled by an "owner"
> backend that is connected to the current DB but hasn't actually taken
> possession of its assigned temp schema (and hence the table in question
> really is orphaned after all). This edge case could be taken care of by
> having backends clear their temp schema immediately, as in step 1 of the
> patch. But I still think that that is an expensive way to catch what
> would be a really infrequent case.

I think we should try to do something about this case -- if not now,
then later. I agree that it would be better if we could get
autovacuum to do it instead of doing it in the foreground. I don't
really share your concern about performance; one extra syscache lookup
at backend startup isn't going to break the bank. Rather, I'm
concerned about reliability. As I said upthread:

"So it would be really bad if you had catalog corruption
preventing the removal of pg_temp_2, because then every time you
connect, it will try to remove that schema, fail, and disconnect you."

Now granted your database *shouldn't* have catalog corruption, but a
lot of things that shouldn't happen sometimes do, and it's better when
those problem don't cause cascading failures.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company