On Thu, Dec 9, 2021 at 3:53 PM John Naylor <john.nay...@enterprisedb.com> wrote: > > single-user mode should prompt the user about > > what exact VACUUM command they ought to run to get things going. > > The current message is particularly bad in its vagueness because some > users immediately reach for VACUUM FULL, which quite logically seems > like the most complete thing to do.
You mean the GetNewTransactionId() error, about single-user mode? Why do we need to use single-user mode at all? I'm pretty sure that the reason is "as an escape hatch", but I wonder what that really means. ***Thinks*** I suppose that it might be a good idea to make sure that autovacuum cannot run, because in general autovacuum might need to allocate an XID (for autoanalyze), and locking all that down in exactly the right way might not be a very good use of our time. But even still, why not have some variant of single-user mode just for this task? Something that's easy to use when the DBA is rudely awakened at 4am -- something a little bit like a big red button that fixes the exact problem of XID exhaustion, in a reasonably targeted way? I don't think that this needs to involve the VACUUM command itself. The current recommendation to do a whole-database VACUUM doesn't take a position on how old the oldest datfrozenxid has to be in order to become safe again, preferring to "make a conservative recommendation" -- which is what a database-level VACUUM really is. But that doesn't seem helpful at all. In fact, it's not even conservative. We could easily come up with a reasonable definition of "datfrozenxid that's sufficiently new to make it safe to come back online and allocate XIDs again". Perhaps something based on the current autovacuum_freeze_max_age (and autovacuum_multixact_freeze_max_age) settings, with sanity checks. We could then apply this criteria in new code that implements this "big red button" (maybe this is a new option for the postgres executable, a little like --single?). Something that's reasonably targeted, and dead simple to use. -- Peter Geoghegan
