On Thu, Mar 7, 2019 at 10:24 AM Tom Lane <t...@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
