On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Dec 21, 2021 at 1:31 PM John Naylor > <john.nay...@enterprisedb.com> wrote: > > On second thought, we don't really need another number here. We could > > simply go by the existing failsafe parameter, and if the admin wants a > > different value, it's already possible to specify > > vacuum_failsafe_age/vacuum_multixact_failsafe_age in a session, > > including in single-user mode. Perhaps a new boolean called > > FAILSAFE_ONLY. If no table is specified, then when generating the list > > of tables, include only those with relfrozenxid/relminmxid greater > > than their failsafe thresholds. > > That's equivalent to the quick and dirty patch I wrote (assuming that > the user actually uses this new FAILSAFE_ONLY thing). > > But if we're going to add a new option to the VACUUM command (or > something of similar scope), then we might as well add a new behavior > that is reasonably exact -- something that (say) only *starts* a > VACUUM for those tables whose relfrozenxid age currently exceeds half > the autovacuum_freeze_max_age for the table (usually taken from the > GUC, sometimes taken from the reloption), which also forces the > failsafe. And with similar handling for > relminmxid/autovacuum_multixact_freeze_max_age. > > In other words, while triggering the failsafe is important, simply *not > starting* VACUUM for relations where there is really no need for it is > at least as important. We shouldn't even think about pruning or > freezing with these tables. (ISTM that the only thing that might be a > bit controversial about any of this is my definition of "safe", which > seems like about the right trade-off to me.)
+1 > > This new command/facility should probably not be a new flag to the > VACUUM command, as such. Rather, I think that it should either be an > SQL-callable function, or a dedicated top-level command (that doesn't > accept any tables). The only reason to have this is for scenarios > where the user is already in a tough spot with wraparound failure, > like that client of yours. Nobody wants to force the failsafe for one > specific table. It's not general purpose, at all, and shouldn't claim > to be. Even not in the situation where the database has to run as the single-user mode to freeze tuples, I think there would be some use cases where users want to run vacuum (in failsafe mode) on tables with relfrozenxid/relminmxid greater than their failsafe thresholds before falling into that situation. I think it’s common that users are somehow monitoring relfrozenxid/relminmxid and want to manually run vacuum on them rather than relying on autovacuums. --min-xid-age option and --min-mxid-age option of vacuumdb command would be good examples. So I think this new command/facility might not necessarily need to be specific to single-user mode. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
