Thread: autovacuum_freeze_max_age reloption seems broken

The autovacuum_freeze_max_age reloption exists so that the DBA can
optionally have antiwraparound autovacuums run against a table that
requires more frequent antiwraparound autovacuums. This has problems
because there are actually two types of VACUUM right now (aggressive
and non-aggressive), which, strictly speaking, is an independent
condition of antiwraparound-ness. There is a tacit assumption within
autovacuum.c that all antiwraparound autovacuums are also aggressive,
I think. But that just isn't true, which leads to clearly broken
behavior when the autovacuum_freeze_max_age reloption is in use.

Note that the VacuumParams state that gets passed down to
vacuum_set_xid_limits() does not include anything about the "reloption
version" of autovacuum_freeze_max_age. So quite naturally
vacuum_set_xid_limits() can only work off of the
autovacuum_freeze_max_age GUC, even when the reloption happens to have
been used over in autovacuum.c. In practice this means that we can
easily see autovacuum spin uselessly when the reloption is in use --
it'll launch antiwraparound autovacuums that never advance
relfrozenxid and so never address the relfrozenxid age issue from the
point of view of autovacuum.c.

There is no reason to think that the user will also (say) set the
autovacuum_freeze_table_age reloption separately (not to be confused
with the vacuum_freeze_table_age GUC!). We'll usually just work off
the GUC (I mean why wouldn't we?). I don't see why vacuumlazy.c
doesn't just force aggressive mode whenever it sees an antiwraparound
autovacuum, no matter what. Recall the problem scenario that led to
bugfix commit dd9ac7d5 -- that also could have been avoided by making
sure that every antiwraparound autovacuum was aggressive (actually the
original problem was that we'd suppress non-aggressive antiwraparound
autovacuums as redundant).

I only noticed this problem because I am in the process of writing a
patch series that demotes vacuum_freeze_table_age to a mere
compatibility option (and even gets rid of the whole concept of
aggressive VACUUM). The whole way that vacuum_freeze_table_age and
autovacuum_freeze_max_age are supposed to work together seems very
confusing to me. I'm not surprised that this was overlooked for so long.

--
Peter Geoghegan

On Mon, Oct 10, 2022 at 4:46 PM Peter Geoghegan <pg@bowt.ie> wrote:
> There is no reason to think that the user will also (say) set the
> autovacuum_freeze_table_age reloption separately (not to be confused
> with the vacuum_freeze_table_age GUC!). We'll usually just work off
> the GUC (I mean why wouldn't we?). I don't see why vacuumlazy.c
> doesn't just force aggressive mode whenever it sees an antiwraparound
> autovacuum, no matter what.

Actually, even forcing every antiwraparound autovacuum to use
aggressive mode isn't enough to stop autovacuum.c from spinning. It
might be a good start, but it still leaves the freeze_min_age issue.

The only way that autovacuum.c is going to be satisfied and back off
with launching antiwraparound autovacuums is if relfrozenxid is
advanced, and advanced by a significant amount. But what if the
autovacuum_freeze_max_age reloption happens to have been set to
something that's significantly less than the value of the
vacuum_freeze_min_age GUC (or the autovacuum_freeze_min_age reloption,
even)? Most of the time we can rely on vacuum_set_xid_limits() making
sure that the FreezeLimit cutoff (cutoff that determines which XID
we'll freeze) isn't unreasonably old relative to other cutoffs. But
that won't work if we're forcing an aggressive VACUUM in vacuumlazy.c.

I suppose that this separate freeze_min_age issue could be fixed by
teaching autovacuum.c's table_recheck_autovac() function to set
freeze_min_age to something less than the current value of reloptions
like autovacuum_freeze_min_age and autovacuum_freeze_table_age for the
same table (when either of the table-level reloptions happened to be
set). In other words, autovacuum.c could be taught to make sure that
these reloption-based cutoffs have sane values relative to each other
by applying roughly the same approach taken in vacuum_set_xid_limits()
for the GUCs.

-- 
Peter Geoghegan