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>
In-Reply-To: <CAH2-WzkEStBgnk0buJYAKw5jpRYUE+-tuLYn5U+vDyhXNfu+DQ@mail.gmail.com>
From: John Naylor <john.naylor@enterprisedb.com>
Date: Tue, 15 Feb 2022 13:04:42 +0700
Message-ID: <CAFBsxsFiMPxQ-dHZ8tOgktn=+ffeJT3+GinZ4zdOGbmAnCYadA@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: Peter Geoghegan <pg@bowt.ie>
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/CAFBsxsFiMPxQ-dHZ8tOgktn%3D%2BffeJT3%2BGinZ4zdOGbmAnCYadA%40mail.gmail.com>
Precedence: bulk

On Tue, Feb 15, 2022 at 11:22 AM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Feb 14, 2022 at 8:04 PM John Naylor
> <john.naylor@enterprisedb.com> wrote:
> > The failsafe mode does disable truncation as of v14:
> >
> > commit 60f1f09ff44308667ef6c72fbafd68235e55ae27
> > Author: Peter Geoghegan <pg@bowt.ie>
> > Date:   Tue Apr 13 12:58:31 2021 -0700
> >
> > Don't truncate heap when VACUUM's failsafe is in effect.
>
> That's true, but bear in mind that it only does so when the specific
> table being vacuumed actually triggers the failsafe. I believe that
> VACUUM(EMERGENCY) doesn't just limit itself to vacuuming tables where
> this is guaranteed (or even likely). If I'm not mistaken, it's
> possible (even likely) that there will be a table whose
> age(relfrozenxid) is high enough for VACUUM(EMERGENCY) to target the
> table, and yet not so high that the failsafe will kick in at the
> earliest opportunity.

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. 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.

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.
-- 
John Naylor
EDB: http://www.enterprisedb.com