On Tue, Mar 6, 2018 at 6:39 PM, Tom Lane <t...@sss.pgh.pa.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
