Re: [PROPOSAL] Drop orphan temp tables in single-mode

On Thu, Mar 7, 2019 at 10:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So if we think we can invent a "MAGICALLY FIX MY DATABASE" command,
> let's do that.  But please let's not turn a well defined command
> like VACUUM into something that you don't quite know what it will do.

I am on the fence about that.  I see your point, but on the other
hand, autovacuum drops temp tables all the time in multi-user mode and
I think it's pretty clear that, with the possible exception of you,
users find that an improvement.  So it could be argued that we're
merely proposing to make the single-user mode behavior of vacuum
consistent with the behavior people are already expecting it to do.

The underlying and slightly more general problem here is that users
find it really hard to know what to do when vacuum fails to advance
relfrozenxid.  Of course, temp tables are only one reason why that can
happen: logical decoding slots and prepared transactions are others,
and I don't think we can automatically drop that stuff because
somebody may still be expecting them to accomplish whatever their
intended purpose is.  The difference with temp tables is that users
imagine -- quite naturally I think -- that they are in fact temporary,
and that they will in fact go away when the session ends.  The user
would tend to view their continued existence as an unwanted
implementation artifact, not something that they should be responsible
for removing.

Really, I'd like to redesign the way this whole system works.  Instead
of forcing a full-system shutdown, we should just refuse to assign any
more XIDs, disable the vacuum cost delay machinery, and let autovacuum
go nuts until the problem is corrected.  Forcing people to run vacuum
to run one vacuum at a time is not nice, and not having background
processes like the bgwriter or checkpointer while you're doing it
isn't good either, and there's no good reason to disallow SELECT
queries while we're recovering the system either. Actually, even
before we get to the point where we currently force a shutdown, we
ought to just give up on vacuum cost delay, either all at once or
perhaps incrementally, when we see that we're getting into trouble.
But all of that is work for another time.

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