On Sun, Feb 4, 2018 at 10:10 PM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
> The attached patch does the former. The small change in autovacuum.c is mainly for autovac launcher and background
workerswhich don't connect to a database. I'll add this to the next CF. I'd like this to be back-patched.
This is really two separate changes:
1. Teach backends to remove any leftover pg_temp_%d schema on startup.
2. Teach autovacuum to remove any leftover tables in a pg_temp_%d
schema if the backend is active but in some other database (rather
than only when the backend is not active at all).
Both of those changes seem broadly reasonable to me, but what do other
people think?
#1 is bound to have some small impact on backend startup time, but
it's probably negligible (one extra syscache lookup, in the common
case). I guess one problem is that if the cleanout of the old
pg_temp_%d schema should fail, then the backend will probably end up
exiting. 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.
On the other hand, there's plenty of kinds of catalog corruption that
can prevent a database connection already. It would be nice if we
could think of a way to skip this cleanup if the previous backend
failed to complete it, but I don't see a way to do that off-hand. I
think we should at least skip it if we're in single-user mode, though.
I don't see any problem with #2, although that part of the patch could
use a little cosmetic work.
I would not be inclined to back-patch these changes, or at least not
farther than v10. Before v10, we only tried to drop temporary tables
if they were old enough to threaten wraparound, so we've already made
this much more aggressive than it used to be. I think making it more
aggressive in the ways mentioned above is a good idea, but I'm not
sure that the failure to do so constitutes a bug.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company