MIME-Version: 1.0
References: <20230329.132155.629765142788133576.horikyota.ntt@gmail.com>
 <20230329.173456.1185961934810139447.horikyota.ntt@gmail.com>
 <CAAKRu_Yg4-kWH_EN7wtpm3z9B+H27UTmOxzzYv1Vg=YdZ6v_rw@mail.gmail.com>
 <CAD21AoCGdxYAJHmE_nXw_MFw1+Qtn3vcThRu5FiadukgQnpWeQ@mail.gmail.com>
 <935191FB-083B-4060-89FC-466F61FAA391@yesql.se> <CAAKRu_aoa0YT36q2n3xa9MZYdepeH4Jyf2EVJj=7YdC_1c974Q@mail.gmail.com>
 <CAAKRu_bSsChKsyEy1ZV2XJQ22FoDdA56efjNr9==m2TO94557w@mail.gmail.com>
 <CAD21AoDi4i4tj1Cf9BG0q2iyVhHisQ2xxVBZajEqExX6t6_b3g@mail.gmail.com>
 <CAAKRu_bkg+0WAGTxsy0HP75RzFbPwqz3ctYq1kThjFhSL69upw@mail.gmail.com>
 <1252075.1680547394@sss.pgh.pa.us> <20230403190837.qubpnwugfe2k2g46@awork3.anarazel.de>
 <CAAKRu_ap7nhmqtKQ1TRo3LzHG3FK=YpGO=O0019M3BbN7ysm3A@mail.gmail.com>
 <7E06F879-7E20-4A6A-862F-CA72CDC9A323@yesql.se> <CAAKRu_b1HjGCTsFpUnmwLNS8NeXJ+JnrDLhT1osP+Gq9HCU+Rw@mail.gmail.com>
 <CBF0083E-5893-4B35-9498-142A835D03DC@yesql.se> <CAAKRu_bJ2DJxYoMkyMo9UpoX74LY5d2Jir86QWFUftrGKWP5Bw@mail.gmail.com>
 <CA+TgmoYDYHht9zsHye3tywx7oWGMP4s_6AGefHkDsMuGHLzT7w@mail.gmail.com>
 <CAAKRu_auVL_uE794zwiX6t18LJvKtJ5mrmV9tkbpWzS8AYeChQ@mail.gmail.com>
 <CAAKRu_aX+MLn32kPnAQ30WChbiLTkKKmY9e8+B1ZY1BokpQZYA@mail.gmail.com>
 <CAAKRu_aH-UbvNCQq-EouNUTjtGexQTRVo7ik_vYR4WUocVny7w@mail.gmail.com>
 <CA+TgmoY3EaCz3_7F3+Sb3FUHonf4nxdC-_UWnn6fWEPtXU7t5g@mail.gmail.com>
 <D0A88FB1-56A6-4CD5-B886-8539B7BA7F58@yesql.se> <CAAKRu_YmQg_i=0DBfPzy1MMc2GjfuJFo=0rFv4fCzRQStLhjvw@mail.gmail.com>
 <CAAKRu_beQECOV4WfU6nG0hhEVobCnH88Y8MQi1LrTwMfRyxuCw@mail.gmail.com>
 <5028847C-937A-4CC2-B127-AFF5F9FB79C5@yesql.se> <CAAKRu_Znk6WCCQqEw8hv3+X1Fm8gG0x4KKRAkKmO2JOcA-77-g@mail.gmail.com>
 <2B703533-102A-47E5-A4F7-C6A68C09BB27@yesql.se> <CAFBsxsFz=GqaG5Ens5aNgVYoV2Y+pfMUijX0ku+CCkWfALwiqg@mail.gmail.com>
In-Reply-To: <CAFBsxsFz=GqaG5Ens5aNgVYoV2Y+pfMUijX0ku+CCkWfALwiqg@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Thu, 27 Apr 2023 21:10:00 +0900
Message-ID: <CAD21AoB_NQwdznBqcTMPv=vx71feDvBTNEMAvaUMAGqNcruk9Q@mail.gmail.com>
Subject: Re: Should vacuum process config file reload more often
To: John Naylor <john.naylor@enterprisedb.com>
Cc: Daniel Gustafsson <daniel@yesql.se>, Melanie Plageman <melanieplageman@gmail.com>, 
	Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 
	Amit Kapila <amit.kapila16@gmail.com>
Content-Type: multipart/mixed; boundary="00000000000097d1de05fa503ef6"
Archived-At: <https://www.postgresql.org/message-id/CAD21AoB_NQwdznBqcTMPv%3Dvx71feDvBTNEMAvaUMAGqNcruk9Q%40mail.gmail.com>
Precedence: bulk

--00000000000097d1de05fa503ef6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Apr 27, 2023 at 6:30=E2=80=AFPM John Naylor
<john.naylor@enterprisedb.com> wrote:
>
>
> On Fri, Apr 7, 2023 at 6:08=E2=80=AFAM Daniel Gustafsson <daniel@yesql.se=
> wrote:
> >
> > I had another read-through and test-through of this version, and have a=
pplied
> > it with some minor changes to comments and whitespace.  Thanks for the =
quick
> > turnaround times on reviews in this thread!
>
> -       VacuumFailsafeActive =3D false;
> +       Assert(!VacuumFailsafeActive);
>
> I can trigger this assert added in commit 7d71d3dd08.
>
> First build with the patch in [1], then:
>
> session 1:
>
> CREATE EXTENSION xid_wraparound ;
>
> CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (=
autovacuum_enabled=3Dfalse);
> INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
>
> -- I can trigger without this, but just make sure it doesn't get vacuumed
> BEGIN;
> DELETE FROM autovacuum_disabled WHERE id % 2 =3D 0;
>
> session 2:
>
> -- get to failsafe limit
> SELECT consume_xids(1*1000*1000*1000);
> INSERT INTO autovacuum_disabled(data) SELECT 1;
> SELECT consume_xids(1*1000*1000*1000);
> INSERT INTO autovacuum_disabled(data) SELECT 1;
>
> VACUUM autovacuum_disabled;
>
> WARNING:  cutoff for removing and freezing tuples is far in the past
> HINT:  Close open transactions soon to avoid wraparound problems.
> You might also need to commit or roll back old prepared transactions, or =
drop stale replication slots.
> WARNING:  bypassing nonessential maintenance of table "john.public.autova=
cuum_disabled" as a failsafe after 0 index scans
> DETAIL:  The table's relfrozenxid or relminmxid is too far in the past.
> HINT:  Consider increasing configuration parameter "maintenance_work_mem"=
 or "autovacuum_work_mem".
> You might also need to consider other ways for VACUUM to keep up with the=
 allocation of transaction IDs.
> server closed the connection unexpectedly
>
> #0  0x00007ff31f68ebec in __pthread_kill_implementation ()
>    from /lib64/libc.so.6
> #1  0x00007ff31f63e956 in raise () from /lib64/libc.so.6
> #2  0x00007ff31f6287f4 in abort () from /lib64/libc.so.6
> #3  0x0000000000978032 in ExceptionalCondition (
>     conditionName=3DconditionName@entry=3D0xa4e970 "!VacuumFailsafeActive=
",
>     fileName=3DfileName@entry=3D0xa4da38 "../src/backend/access/heap/vacu=
umlazy.c", lineNumber=3DlineNumber@entry=3D392) at ../src/backend/utils/err=
or/assert.c:66
> #4  0x000000000058c598 in heap_vacuum_rel (rel=3D0x7ff31d8a97d0,
>     params=3D<optimized out>, bstrategy=3D<optimized out>)
>     at ../src/backend/access/heap/vacuumlazy.c:392
> #5  0x000000000069af1f in table_relation_vacuum (bstrategy=3D0x14ddca8,
>     params=3D0x7ffec28585f0, rel=3D0x7ff31d8a97d0)
>     at ../src/include/access/tableam.h:1705
> #6  vacuum_rel (relid=3Drelid@entry=3D16402, relation=3Drelation@entry=3D=
0x0,
>     params=3Dparams@entry=3D0x7ffec28585f0, skip_privs=3Dskip_privs@entry=
=3Dtrue,
>     bstrategy=3Dbstrategy@entry=3D0x14ddca8)
>     at ../src/backend/commands/vacuum.c:2202
> #7  0x000000000069b0e4 in vacuum_rel (relid=3D16398, relation=3D<optimize=
d out>,
>     params=3Dparams@entry=3D0x7ffec2858850, skip_privs=3Dskip_privs@entry=
=3Dfalse,
>     bstrategy=3Dbstrategy@entry=3D0x14ddca8)
>     at ../src/backend/commands/vacuum.c:2236

Good catch. I think the problem is that vacuum_rel() is called
recursively and we don't reset VacuumFailsafeActive before vacuuming
the toast table. I think we should reset it in heap_vacuum_rel()
instead of Assert(). It's possible that we trigger the failsafe mode
only for either one.Please find the attached patch.

Regards,

--=20
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

--00000000000097d1de05fa503ef6
Content-Type: application/octet-stream; 
	name="reset_VacuumFailsafeActive.patch"
Content-Disposition: attachment; filename="reset_VacuumFailsafeActive.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lgz2xq5k0>
X-Attachment-Id: f_lgz2xq5k0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYyBiL3NyYy9i
YWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYwppbmRleCAwYTllYmQyMmJkLi4yYmE4NWJk
M2Q2IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC92YWN1dW1sYXp5LmMKKysr
IGIvc3JjL2JhY2tlbmQvYWNjZXNzL2hlYXAvdmFjdXVtbGF6eS5jCkBAIC0zODksNyArMzg5LDcg
QEAgaGVhcF92YWN1dW1fcmVsKFJlbGF0aW9uIHJlbCwgVmFjdXVtUGFyYW1zICpwYXJhbXMsCiAJ
QXNzZXJ0KHBhcmFtcy0+aW5kZXhfY2xlYW51cCAhPSBWQUNPUFRWQUxVRV9VTlNQRUNJRklFRCk7
CiAJQXNzZXJ0KHBhcmFtcy0+dHJ1bmNhdGUgIT0gVkFDT1BUVkFMVUVfVU5TUEVDSUZJRUQgJiYK
IAkJICAgcGFyYW1zLT50cnVuY2F0ZSAhPSBWQUNPUFRWQUxVRV9BVVRPKTsKLQlBc3NlcnQoIVZh
Y3V1bUZhaWxzYWZlQWN0aXZlKTsKKwlWYWN1dW1GYWlsc2FmZUFjdGl2ZSA9IGZhbHNlOwogCXZh
Y3JlbC0+Y29uc2lkZXJfYnlwYXNzX29wdGltaXphdGlvbiA9IHRydWU7CiAJdmFjcmVsLT5kb19p
bmRleF92YWN1dW1pbmcgPSB0cnVlOwogCXZhY3JlbC0+ZG9faW5kZXhfY2xlYW51cCA9IHRydWU7
Cg==
--00000000000097d1de05fa503ef6--