MIME-Version: 1.0
References: <CAFBsxsFL5LoWanSLDjt_SjS9=FU_9XWi22iHMexRS2SubkX-Vw@mail.gmail.com>
 <CAH2-Wz=Z5Jjd1y8kCfXd6e-GSvKiL8XEmxoJJFNHcekM4aw-Vg@mail.gmail.com>
 <CAH2-Wzn3pE8rNzBxh-Qpmq=zxFWFpoC7LWLbyDg_1XZ3wJOtUA@mail.gmail.com>
 <CAFBsxsG-VDhXYa25uERfDGD4dO_AZ3d+Dv2NF+DJBADZiXejpw@mail.gmail.com>
 <CAH2-Wznop1jM3j6E1xTcG--kFQ1hW5cMmM8B1965Mz+uUg6HSg@mail.gmail.com>
 <20211210015616.o242b4xchhpglfcy@alap3.anarazel.de> <CA+TgmoYPfofQmRtUan=A3aWE9wFsJaOFr+W_ys2pPkNPr-2FZw@mail.gmail.com>
 <CAFBsxsGaZZTfLgd+H+VG6mxv5KfDxYf43tqOr_JqdwstCy=pLA@mail.gmail.com>
 <CA+TgmoY3m3RimCHifQnxU3YDVV7oYRbRUX4GZ4FuYkhnhe0giA@mail.gmail.com>
 <20220203215048.rmwjzixdzaetedxq@alap3.anarazel.de> <CAFBsxsHp3brGqWu5vU_6d5wfOU5i40c8u3Hg=3TtN4qZwmAecw@mail.gmail.com>
 <CAH2-WzkEStBgnk0buJYAKw5jpRYUE+-tuLYn5U+vDyhXNfu+DQ@mail.gmail.com> <CAFBsxsFiMPxQ-dHZ8tOgktn=+ffeJT3+GinZ4zdOGbmAnCYadA@mail.gmail.com>
In-Reply-To: <CAFBsxsFiMPxQ-dHZ8tOgktn=+ffeJT3+GinZ4zdOGbmAnCYadA@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 15 Feb 2022 09:28:47 -0800
Message-ID: <CAH2-WzkpZcat1YmEVAKpcMBaWRXUd2vGVKHtrBjzBGDL=j6mbA@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: John Naylor <john.naylor@enterprisedb.com>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAH2-WzkpZcat1YmEVAKpcMBaWRXUd2vGVKHtrBjzBGDL%3Dj6mbA%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 14, 2022 at 10:04 PM John Naylor
<john.naylor@enterprisedb.com> wrote:
> Well, the point of inventing this new vacuum mode was because I
> thought that upon reaching xidStopLimit, we couldn't issue commands,
> period, under the postmaster. If it was easier to get a test instance
> to xidStopLimit, I certainly would have discovered this sooner.

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.

> When
> Andres wondered about getting away from single user mode, I assumed
> that would involve getting into areas too deep to tackle for v15. As
> Robert pointed out, lazy_truncate_heap is the only thing that can't
> happen for vacuum at this point, and fully explains why in versions <
> 14 our client's attempts to vacuum resulted in error. Since the
> failsafe mode turns off truncation, vacuum should now *just work* near
> wraparound. If there is any doubt, we can tighten the check for
> entering failsafe.

Obviously having to enter single user mode is horrid. If we can
reasonably update the advice to something more reasonable now, then
that would help users that find themselves in this situation a great
deal.

> Now, it's certainly possible that autovacuum is either not working at
> all because of something broken, or is not working on the oldest
> tables at the moment, so one thing we could do is to make VACUUM [with
> no tables listed] get the tables from pg_class in reverse order of
> max(xid age, mxid age). That way, the horizon will eventually pull
> back over time and the admin can optionally cancel the vacuum at some
> point. Since the order is harmless when it's not needed, we can do
> that unconditionally.

My ongoing work on freezing/relfrozenxid tends to make the age of
relfrozenxid much more indicative of the amount of work that VACUUM
would have to do when run -- not limited to freezing. You could
probably do this anyway, but it's nice that that'll be true.

-- 
Peter Geoghegan