Subject: Re: do only critical work during single-user vacuum?

From: John Naylor <[email-masked]>
Date: Wed, 16 Feb 2022 15:43:12 +0700

On Wed, Feb 16, 2022 at 6:17 AM Peter Geoghegan <[email-masked]> wrote:
>
> On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan <[email-masked]> wrote:

> > I did notice from my own testing of the failsafe (by artificially
> > inducing wraparound failure using an XID burning C function) that
> > autovacuum seemed to totally correct the problem, even when the system
> > had already crossed xidStopLimit - it came back on its own. I wasn't
> > completely sure of how robust this effect was, though.

I'll put some effort in finding any way that it might not be robust.
After that, changing the message and docs is trivial.

> It seemed worth noting this in comments above
> should_attempt_truncation(). Pushed a commit to do that just now.

Thanks for that.

-- 
John Naylor
EDB: http://www.enterprisedb.com

---

From: Noah Misch <[email-masked]>
Date: Sat, 19 Feb 2022 20:57:57 -0800

> I'll put some effort in finding any way that it might not be robust.

A VACUUM may create a not-trivially-bounded number of multixacts via
FreezeMultiXactId().  In a cluster at multiStopLimit, completing VACUUM
without error needs preparation something like:

1. Kill each XID that might appear in a multixact.
2. Resolve each prepared transaction that might appear in a multixact.
3. Run VACUUM.  At this point, multiStopLimit is blocking new multixacts from
   other commands, and the lack of running multixact members removes the need
   for FreezeMultiXactId() to create multixacts.

Adding to the badness of single-user mode so well described upthread, one can
enter it without doing (2) and then wrap the nextMXact counter.

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 15 Mar 2022 14:48:24 -0700

> After that, changing the message and docs is trivial.

It would be great to be able to totally drop the idea of using
single-user mode before Postgres 15 feature freeze. How's that going?

I suggest that we apply the following patch as part of that work. It
adds one last final failsafe check at the point that VACUUM makes a
final decision on rel truncation.

It seems unlikely that the patch will ever make the crucial difference
in a wraparound scenario -- in practice it's very likely that we'd
have triggered the wraparound at that point if we run into trouble
with the target rel's relfrozenxid age. And even if it does get to
that point, it would still be possible for the autovacuum launcher to
launch another autovacuum -- this time around we will avoid rel
truncation, restoring the system to normal operation (i.e. no more
xidStopLimit state).

On the other hand it's possible that lazy_cleanup_all_indexes() will
take a very long time to run, and it runs after the current final
failsafe check. An index AM's amvacuumcleanup() routine can take a
long time to run sometimes, especially with GIN indexes. And so it's
just about possible that we won't have triggered the failsafe by the
time lazy_cleanup_all_indexes() is called, which then spends a long
time doing index cleanup -- long enough for the system to reach
xidStopLimit due to the target rel's relfrozenxid age crossing the
crucial xidStopLimit crossover point.

This patch makes this problem scenario virtually impossible. Right now
I'm only prepared to say it's very unlikely. I don't see a reason to
take any chances, though.

-- 
Peter Geoghegan

---

From: John Naylor <[email-masked]>
Date: Thu, 31 Mar 2022 16:51:05 +0700

> single-user mode before Postgres 15 feature freeze. How's that going?

Unfortunately, I was distracted from this work for a time, and just as
I had intended to focus on it during March, I was out sick for 2-3
weeks. I gather from subsequent discussion that a full solution goes
beyond just a new warning message and documentation. Either way I'm
not quite prepared to address this in time for v15.

> final decision on rel truncation.

That is one thing that was in the back of my mind, and it seems
reasonable to me.