Re: effective_multixact_freeze_max_age issue

On Sun, Aug 28, 2022 at 11:38:09AM -0700, Peter Geoghegan wrote:
> On Tue, Aug 2, 2022 at 4:12 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> That is, we only need to make sure that the "multiXactCutoff must
>> always be <= oldestMxact" invariant holds once, by checking for it
>> directly. The same thing happens with OldestXmin/FreezeLimit. That
>> seems like a simpler foundation. It's also a lot more logical. Why
>> should the cutoff for freezing be held back by a long running
>> transaction, except to the extent that it is strictly necessary to do
>> so to avoid wrong answers (wrong answers seen by the long running
>> transaction)?
>
> Anybody have any input on this? I'm hoping that this can be committed soon.
>
> ISTM that the way that we currently derive FreezeLimit (by starting
> with OldestXmin rather than starting with the same
> ReadNextTransactionId() value that gets used for the aggressiveness
> cutoffs) is just an accident of history. The "Routine vacuuming" docs
> already describe this behavior in terms that sound closer to the
> behavior with the patch than the actual current behavior:
>
> "When VACUUM scans every page in the table that is not already
> all-frozen, it should set age(relfrozenxid) to a value just a little
> more than the vacuum_freeze_min_age setting that was used (more by the
> number of transactions started since the VACUUM started)"

The idea seems sound to me, and IMO your patch simplifies things nicely,
which might be reason enough to proceed with it. However, I'm struggling
to understand when this change would help much in practice. IIUC it will
cause vacuums to freeze a bit more, but outside of extreme cases (maybe
when vacuum_freeze_min_age is set very high and there are long-running
transactions), ISTM it might not have tremendously much impact. Is the
intent to create some sort of long-term behavior change for autovacuum, or
is this mostly aimed towards consistency among the cutoff calculations?

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com