Date: Thu, 3 Feb 2022 13:50:48 -0800
From: Andres Freund <andres@anarazel.de>
To: Robert Haas <robertmhaas@gmail.com>
Cc: John Naylor <john.naylor@enterprisedb.com>,
	Peter Geoghegan <pg@bowt.ie>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: do only critical work during single-user vacuum?
Message-ID: <20220203215048.rmwjzixdzaetedxq@alap3.anarazel.de>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CA+TgmoY3m3RimCHifQnxU3YDVV7oYRbRUX4GZ4FuYkhnhe0giA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20220203215048.rmwjzixdzaetedxq%40alap3.anarazel.de>
Precedence: bulk

Hi,

On 2022-02-03 13:42:20 -0500, Robert Haas wrote:
> They *do* have a choice. They can continue to operate the system in
> multi-user mode, they can have read access to their data, and they can
> run VACUUM and other non-XID-allocating commands to fix the issue.
> Sure, their application can't run commands that allocate XIDs, but
> it's not going to be able to do that if they go to single-user mode
> either.

I wonder if we shouldn't add some exceptions to the xid allocation
prevention. It makes sense that we don't allow random DML. But it's e.g. often
more realistic to drop / truncate a few tables with unimportant content,
rather than spend the time vacuuming those.  We could e.g. allow xid
consumption within VACUUM, TRUNCATE, DROP TABLE / INDEX when run at the top
level for longer than we allow it for anything else.


> But it would still be better than going into single-user mode, which
> provides even less functionality and has basically no advantages of
> any kind.

Indeed. Single user is the worst response to this (and just about anything
else, really). Even just getting into the single user mode takes a while
(shutdown checkpoint). The user interface is completely different (and
awful). The buffer cache is completely cold. The system is slower because
there's no wal writer / checkpointer running.  Which basically is a list of
things one absolutely do not wants when confronted with a wraparound
situation.

Greetings,

Andres Freund