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>
 <CAH2-WzkpZcat1YmEVAKpcMBaWRXUd2vGVKHtrBjzBGDL=j6mbA@mail.gmail.com>
 <CAH2-WznMo_aEQJJrD1G70RbbWRB9C3gFf3Am6emYrWSTh6wu=w@mail.gmail.com> <CAFBsxsHMOe=eNG-9nWyGiSXA7QH6ZS3cXCjRqxyasQM6BO3==g@mail.gmail.com>
In-Reply-To: <CAFBsxsHMOe=eNG-9nWyGiSXA7QH6ZS3cXCjRqxyasQM6BO3==g@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 15 Mar 2022 14:48:24 -0700
Message-ID: <CAH2-Wzk0ZrsYx7FxQZ2dGm4XLDNwp2jpNDXwjpASiJNs4HBhDQ@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: multipart/mixed; boundary="0000000000003e96cd05da48c313"
Archived-At: <https://www.postgresql.org/message-id/CAH2-Wzk0ZrsYx7FxQZ2dGm4XLDNwp2jpNDXwjpASiJNs4HBhDQ%40mail.gmail.com>
Precedence: bulk

--0000000000003e96cd05da48c313
Content-Type: text/plain; charset="UTF-8"

On Wed, Feb 16, 2022 at 12:43 AM John Naylor
<john.naylor@enterprisedb.com> wrote:
> 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 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

--0000000000003e96cd05da48c313
Content-Type: application/octet-stream; 
	name="v1-0001-Perform-final-failsafe-check-before-truncation.patch"
Content-Disposition: attachment; 
	filename="v1-0001-Perform-final-failsafe-check-before-truncation.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_l0sn9lgl0>
X-Attachment-Id: f_l0sn9lgl0

RnJvbSA3MmFlYmM3ODU0ZDI0ZmI4NDY2YjQ1NTM2ZTQxY2MzZDc2OGNkNDQ1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBHZW9naGVnYW4gPHBnQGJvd3QuaWU+CkRhdGU6IFR1
ZSwgMTUgTWFyIDIwMjIgMTQ6MjQ6MTIgLTA3MDAKU3ViamVjdDogW1BBVENIIHYxXSBQZXJmb3Jt
IGZpbmFsIGZhaWxzYWZlIGNoZWNrIGJlZm9yZSB0cnVuY2F0aW9uLgoKLS0tCiBzcmMvYmFja2Vu
ZC9hY2Nlc3MvaGVhcC92YWN1dW1sYXp5LmMgfCA2ICsrKysrKwogMSBmaWxlIGNoYW5nZWQsIDYg
aW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1
bWxhenkuYyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYwppbmRleCA4N2Fi
Nzc3NWEuLjczMzM2NTYwYiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvYWNjZXNzL2hlYXAvdmFj
dXVtbGF6eS5jCisrKyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYwpAQCAt
MjgyMiw3ICsyODIyLDEzIEBAIHNob3VsZF9hdHRlbXB0X3RydW5jYXRpb24oTFZSZWxTdGF0ZSAq
dmFjcmVsKQogCWlmIChwb3NzaWJseV9mcmVlYWJsZSA+IDAgJiYKIAkJKHBvc3NpYmx5X2ZyZWVh
YmxlID49IFJFTF9UUlVOQ0FURV9NSU5JTVVNIHx8CiAJCSBwb3NzaWJseV9mcmVlYWJsZSA+PSB2
YWNyZWwtPnJlbF9wYWdlcyAvIFJFTF9UUlVOQ0FURV9GUkFDVElPTikpCisJeworCQkvKiBQZXJm
b3JtIGEgZmluYWwgZmFpbHNhZmUgY2hlY2sgb3V0IG9mIGFuIGFidW5kYW5jZSBvZiBjYXV0aW9u
ICovCisJCWlmIChsYXp5X2NoZWNrX3dyYXBhcm91bmRfZmFpbHNhZmUodmFjcmVsKSkKKwkJCXJl
dHVybiBmYWxzZTsKKwogCQlyZXR1cm4gdHJ1ZTsKKwl9CiAKIAlyZXR1cm4gZmFsc2U7CiB9Ci0t
IAoyLjMwLjIKCg==
--0000000000003e96cd05da48c313--