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>
 <3F7FD826-36EF-4991-AB37-81675AFEBF54@amazon.com>
 <0544901A-0FC5-46B5-801B-F30AC0B81A19@amazon.com>
 <CAH2-Wz=9mfmSL5QDG9GmXt2Av8NBvNSo+CF7FPW3g80awHab7Q@mail.gmail.com>
 <DB4D9904-146A-4B21-8362-99F885C489E1@amazon.com>
In-Reply-To: <DB4D9904-146A-4B21-8362-99F885C489E1@amazon.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Mon, 20 Dec 2021 17:17:26 -0800
Message-ID: 
 <CAH2-Wz=Eb-jrhFK6=_WNT6V1JAK-qFOTu_4-yOpAhkEES4VeNA@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: "Bossart, Nathan" <bossartn@amazon.com>
Cc: John Naylor <john.naylor@enterprisedb.com>,
 Andres Freund <andres@anarazel.de>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000003decd905d39dc610"
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-Wz%3DEb-jrhFK6%3D_WNT6V1JAK-qFOTu_4-yOpAhkEES4VeNA%40mail.gmail.com>
Precedence: bulk

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

On Thu, Dec 9, 2021 at 8:41 PM Bossart, Nathan <bossartn@amazon.com> wrote:
> I like the idea of having a built-in function that does the bare
> minimum to resolve wraparound emergencies, and I think providing some
> sort of simple progress indicator (even if rudimentary) would be very
> useful.

If John doesn't have time to work on this during the Postgres 15
cycle, and if nobody else picks it up, then we should at least do the
bare minimum here: force the use of the failsafe in single user mode
(regardless of the age of relfrozenxid/relminmxid, which in general
might not be that old in tables where VACUUM might need to do a lot of
work). Attached quick and dirty patch shows what this would take. If
nothing else, it seems natural to define running any VACUUM in single
user mode as an emergency.

This is really the least we could do -- it's much better than nothing,
but still really lazy^M^M^M^M conservative. I haven't revised the
assumption that the user should do a top-level "VACUUM" in databases
that can no longer allocate XIDs due to wraparound, despite the fact
that we could do far better with moderate effort. Although it might
make sense to commit something like the attached as part of a more
worked out solution (assuming it didn't fully remove single user mode
from the equation, which would be better still).

-- 
Peter Geoghegan

--0000000000003decd905d39dc610
Content-Type: application/octet-stream;
	name="v1-0001-Always-trigger-failsafe-in-single-user-mode.patch"
Content-Disposition: attachment;
	filename="v1-0001-Always-trigger-failsafe-in-single-user-mode.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_kxfelm2f0>
X-Attachment-Id: f_kxfelm2f0

RnJvbSBiN2EzZGVkMThkMTMwM2U2NDk1MTQzOTQzN2JjZDU5MjVkNWM2ZjQ0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBHZW9naGVnYW4gPHBnQGJvd3QuaWU+CkRhdGU6IE1v
biwgMjAgRGVjIDIwMjEgMTY6NTg6NDAgLTA4MDAKU3ViamVjdDogW1BBVENIIHYxXSBBbHdheXMg
dHJpZ2dlciBmYWlsc2FmZSBpbiBzaW5nbGUgdXNlciBtb2RlLgoKLS0tCiBzcmMvYmFja2VuZC9h
Y2Nlc3MvaGVhcC92YWN1dW1sYXp5LmMgfCAxMiArKysrKysrKy0tLS0KIHNyYy9iYWNrZW5kL2Nv
bW1hbmRzL3ZhY3V1bS5jICAgICAgICB8ICA2ICsrKysrLQogMiBmaWxlcyBjaGFuZ2VkLCAxMyBp
bnNlcnRpb25zKCspLCA1IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2Fj
Y2Vzcy9oZWFwL3ZhY3V1bWxhenkuYyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxh
enkuYwppbmRleCBkYjZiZWNmZWQuLjJiYTg4Mjk1MSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
YWNjZXNzL2hlYXAvdmFjdXVtbGF6eS5jCisrKyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3Zh
Y3V1bWxhenkuYwpAQCAtMjYwNSw2ICsyNjA1LDE0IEBAIGxhenlfY2hlY2tfd3JhcGFyb3VuZF9m
YWlsc2FmZShMVlJlbFN0YXRlICp2YWNyZWwpCiAJCXZhY3JlbC0+ZG9faW5kZXhfY2xlYW51cCA9
IGZhbHNlOwogCQl2YWNyZWwtPmRvX3JlbF90cnVuY2F0ZSA9IGZhbHNlOwogCisJCS8qIFN0b3Ag
YXBwbHlpbmcgY29zdCBsaW1pdHMgZnJvbSB0aGlzIHBvaW50IG9uICovCisJCVZhY3V1bUNvc3RB
Y3RpdmUgPSBmYWxzZTsKKwkJVmFjdXVtQ29zdEJhbGFuY2UgPSAwOworCisJCS8qIFNpbmdsZSB1
c2VyIG1vZGUgdXNlcyBmYWlsc2FmZSBhcyBzdGFuZGFyZCwgc28gbm8gV0FSTklORyBuZWVkZWQg
Ki8KKwkJaWYgKCFJc1VuZGVyUG9zdG1hc3RlcikKKwkJCXJldHVybiB0cnVlOworCiAJCWVyZXBv
cnQoV0FSTklORywKIAkJCQkoZXJybXNnKCJieXBhc3Npbmcgbm9uZXNzZW50aWFsIG1haW50ZW5h
bmNlIG9mIHRhYmxlIFwiJXMuJXMuJXNcIiBhcyBhIGZhaWxzYWZlIGFmdGVyICVkIGluZGV4IHNj
YW5zIiwKIAkJCQkJCWdldF9kYXRhYmFzZV9uYW1lKE15RGF0YWJhc2VJZCksCkBAIC0yNjE1LDEw
ICsyNjIzLDYgQEAgbGF6eV9jaGVja193cmFwYXJvdW5kX2ZhaWxzYWZlKExWUmVsU3RhdGUgKnZh
Y3JlbCkKIAkJCQkgZXJyaGludCgiQ29uc2lkZXIgaW5jcmVhc2luZyBjb25maWd1cmF0aW9uIHBh
cmFtZXRlciBcIm1haW50ZW5hbmNlX3dvcmtfbWVtXCIgb3IgXCJhdXRvdmFjdXVtX3dvcmtfbWVt
XCIuXG4iCiAJCQkJCQkgIllvdSBtaWdodCBhbHNvIG5lZWQgdG8gY29uc2lkZXIgb3RoZXIgd2F5
cyBmb3IgVkFDVVVNIHRvIGtlZXAgdXAgd2l0aCB0aGUgYWxsb2NhdGlvbiBvZiB0cmFuc2FjdGlv
biBJRHMuIikpKTsKIAotCQkvKiBTdG9wIGFwcGx5aW5nIGNvc3QgbGltaXRzIGZyb20gdGhpcyBw
b2ludCBvbiAqLwotCQlWYWN1dW1Db3N0QWN0aXZlID0gZmFsc2U7Ci0JCVZhY3V1bUNvc3RCYWxh
bmNlID0gMDsKLQogCQlyZXR1cm4gdHJ1ZTsKIAl9CiAKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5k
L2NvbW1hbmRzL3ZhY3V1bS5jIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvdmFjdXVtLmMKaW5kZXgg
NWM0YmMxNWI0Li4yMTdiYjY5NjUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3Zh
Y3V1bS5jCisrKyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3ZhY3V1bS5jCkBAIC0xMTUzLDcgKzEx
NTMsOCBAQCB2YWN1dW1fc2V0X3hpZF9saW1pdHMoUmVsYXRpb24gcmVsLAogLyoKICAqIHZhY3V1
bV94aWRfZmFpbHNhZmVfY2hlY2soKSAtLSBVc2VkIGJ5IFZBQ1VVTSdzIHdyYXBhcm91bmQgZmFp
bHNhZmUKICAqIG1lY2hhbmlzbSB0byBkZXRlcm1pbmUgaWYgaXRzIHRhYmxlJ3MgcmVsZnJvemVu
eGlkIGFuZCByZWxtaW5teGlkIGFyZSBub3cKLSAqIGRhbmdlcm91c2x5IGZhciBpbiB0aGUgcGFz
dC4KKyAqIGRhbmdlcm91c2x5IGZhciBpbiB0aGUgcGFzdC4gIFRoaXMgaXMgYXNzdW1lZCB0byBh
bHdheXMgYmUgdGhlIGNhc2Ugd2hlbgorICogdGhlIGJhY2tlbmQgaXMgcnVuIGluIHNpbmdsZSB1
c2VyIG1vZGUuCiAgKgogICogSW5wdXQgcGFyYW1ldGVycyBhcmUgdGhlIHRhcmdldCByZWxhdGlv
bidzIHJlbGZyb3plbnhpZCBhbmQgcmVsbWlubXhpZC4KICAqCkBAIC0xMjAzLDYgKzEyMDQsOSBA
QCB2YWN1dW1feGlkX2ZhaWxzYWZlX2NoZWNrKFRyYW5zYWN0aW9uSWQgcmVsZnJvemVueGlkLCBN
dWx0aVhhY3RJZCByZWxtaW5teGlkKQogCQlyZXR1cm4gdHJ1ZTsKIAl9CiAKKwlpZiAoIUlzVW5k
ZXJQb3N0bWFzdGVyKQorCQlyZXR1cm4gdHJ1ZTsKKwogCXJldHVybiBmYWxzZTsKIH0KIAotLSAK
Mi4zMC4yCgo=
--0000000000003decd905d39dc610--