Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1prxx2-0004pW-QB for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Apr 2023 09:30:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1prxx1-0000iP-HC for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Apr 2023 09:30:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1prxx1-0000iE-5r for pgsql-hackers@lists.postgresql.org; Thu, 27 Apr 2023 09:30:07 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1prxwx-005Ljw-JV for pgsql-hackers@postgresql.org; Thu, 27 Apr 2023 09:30:06 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-b9a6f17f2b6so2300161276.1 for ; Thu, 27 Apr 2023 02:30:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1682587801; x=1685179801; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=iFgI/LNcajBjPuEN7RHZoafhefi7WG4kFzczQEjpvbg=; b=gXjNHWGt644YAoMkp8IvsvhX0zgQvnGHgOsxu9s0hV5gObKKAzI6bAzIc1Xe1JSxY6 ADCmOuTATesZVNYDwt5zqitNrSasffqAml0RkNDXBLVJmyzf2aCNrPbNngpFUndRCJwZ QZjbqzsNlINxM9bfIlNR+3p/OEsD2LaFNWMeDU2lnXGhF4l2bNUpFQpolUs2/zU7d4sb Hca7frRDQ5q0oqRSc1/QosYCJJEt71qD3DipLrxlfleEw9LCGoV7r31bfO5LMRCs4Q0M 6vzGVtDs1WJMCFuBAZXXbuGWz+sQiD61UlRC6yFVPhSu6lbaS3/fuV93NGcsLeA2WAYD 4UrQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1682587801; x=1685179801; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=iFgI/LNcajBjPuEN7RHZoafhefi7WG4kFzczQEjpvbg=; b=T3XDGkWHjsekTxsnXDuzx7dfpevE+sfunuaBIbKZLmDYjp4ovS8qcSZEWlyp1hNf/I JUP0+goUvzBDGe7Nln3OQqvpSe4HeO+bEk1yvOgsGkYRfGiX9m+/vGd0Mxw0lbqGULep iBOjJiDFqmw4lqaactYknSl7kBNuAADYLw8r+1ht8h+9pCsA5kvVv+NIISYbvXLrbbm5 6+uUS8VvWmcr9GsfBz5Lxv5N8hdVw0GD6BBrhSvL0R2pQ3sdeEdrJMHx5S149LlerZI+ +5bzt5NPqhYnG+YOZoD/yMV6Cm2Ly8lh7Z6vGncFNxSsfd4eiRdTEImeml+TDsJeK93r 64yA== X-Gm-Message-State: AC+VfDy/IY0VtWeGVVQkMnGqjWq30HjQTNqElMmdluOzqEMQ0b8GFkOQ Y6+9nkAg4rHqfWo8gx8924u6Q4hefXc/JJpRfHPSRXGOhNww2JjfAYMKGGW4FS52DGSIuWmJjEy eaUPtrQVB1mprbIj4CefEcjZo89bld6aFXKLDI/C8rtMKwz/zRotDRNhKPJUZdzLMJgCTu5AOcP 3/CmB7XIECyIj4H0OwDsIE1jhAu+ypGlJJ1eFvLDPIG2YEkdbUN0BT X-Google-Smtp-Source: ACHHUZ5EPlFVeRCdV8GXd2c8hJRHgAE17vx2RvRtjBoWiCeR9gA6Tuo90BRx9aJoyb5xrLwrAkap4Pd+fmGQ5PfNZgI= X-Received: by 2002:a81:189:0:b0:54f:6f2e:b3f5 with SMTP id 131-20020a810189000000b0054f6f2eb3f5mr3728066ywb.13.1682587801061; Thu, 27 Apr 2023 02:30:01 -0700 (PDT) MIME-Version: 1.0 References: <20230329.132155.629765142788133576.horikyota.ntt@gmail.com> <20230329.173456.1185961934810139447.horikyota.ntt@gmail.com> <935191FB-083B-4060-89FC-466F61FAA391@yesql.se> <1252075.1680547394@sss.pgh.pa.us> <20230403190837.qubpnwugfe2k2g46@awork3.anarazel.de> <7E06F879-7E20-4A6A-862F-CA72CDC9A323@yesql.se> <5028847C-937A-4CC2-B127-AFF5F9FB79C5@yesql.se> <2B703533-102A-47E5-A4F7-C6A68C09BB27@yesql.se> In-Reply-To: <2B703533-102A-47E5-A4F7-C6A68C09BB27@yesql.se> From: John Naylor Date: Thu, 27 Apr 2023 16:29:49 +0700 Message-ID: Subject: Re: Should vacuum process config file reload more often To: Daniel Gustafsson Cc: Melanie Plageman , Robert Haas , Andres Freund , Tom Lane , Masahiko Sawada , Kyotaro Horiguchi , PostgreSQL Hackers , Amit Kapila Content-Type: multipart/alternative; boundary="0000000000002fa92505fa4e00f2" X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002fa92505fa4e00f2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Apr 7, 2023 at 6:08=E2=80=AFAM Daniel Gustafsson = wrote: > > I had another read-through and test-through of this version, and have applied > 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.autovacuum_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/vacuumlazy.c", lineNumber=3DlineNumber@entry=3D= 392) at ../src/backend/utils/error/assert.c:66 #4 0x000000000058c598 in heap_vacuum_rel (rel=3D0x7ff31d8a97d0, params=3D, bstrategy=3D) 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=3D0x= 0, 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, params=3Dparams@entry=3D0x7ffec2858850, skip_privs=3Dskip_privs@entry= =3Dfalse, bstrategy=3Dbstrategy@entry=3D0x14ddca8) at ../src/backend/commands/vacuum.c:2236 #8 0x000000000069c594 in vacuum (relations=3D0x14dde38, params=3D0x7ffec2858850, bstrategy=3D0x14ddca8, vac_context=3D0x14ddb90= , isTopLevel=3D) at ../src/backend/commands/vacuum.c:623 [1] https://www.postgresql.org/message-id/CAD21AoAyYBZOiB1UPCPZJHTLk0-arrq5zqNG= j%2BPrsbpdUy%3Dg-g%40mail.gmail.com -- John Naylor EDB: http://www.enterprisedb.com --0000000000002fa92505fa4e00f2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, Apr 7, 2023 at 6:08=E2=80=AFAM Daniel Gustafss= on <daniel@yesql.se> wrote:>
> I had another read-through and test-through of this version, = and have applied
> it with some minor changes to comments and whitesp= ace.=C2=A0 Thanks for the quick
> turnaround times on reviews in this= thread!

- =C2=A0 =C2=A0 =C2=A0 VacuumFailsafeActive =3D false;
+= =C2=A0 =C2=A0 =C2=A0 Assert(!VacuumFailsafeActive);

I can trigger t= his assert added in commit=C2=A07d71d3dd08.

First build with the pat= ch 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 autovac= uum_disabled WHERE id % 2 =3D 0;

session 2:

-- get to failsaf= e limit
SELECT consume_xids(1*1000*1000*1000);
INSERT INTO autovacuum= _disabled(data) SELECT 1;
SELECT consume_xids(1*1000*1000*1000);
INSE= RT INTO autovacuum_disabled(data) SELECT 1;

VACUUM autovacuum_disabl= ed;

WARNING: =C2=A0cutoff for removing and freezing tuples is far in= the past
HINT: =C2=A0Close open transactions soon to avoid wraparound p= roblems.
You might also need to commit or roll back old prepared transac= tions, or drop stale replication slots.
WARNING: =C2=A0bypassing nonesse= ntial maintenance of table "john.public.autovacuum_disabled" as a= failsafe after 0 index scans
DETAIL: =C2=A0The table's relfrozenxid= or relminmxid is too far in the past.
HINT: =C2=A0Consider increasing c= onfiguration parameter "maintenance_work_mem" or "autovacuum= _work_mem".
You might also need to consider other ways for VACUUM t= o keep up with the allocation of transaction IDs.
server closed the conn= ection unexpectedly

#0 =C2=A00x00007ff31f68ebec in __pthread_kill_im= plementation ()
=C2=A0 =C2=A0from /lib64/libc.so.6
#1 =C2=A00x00007ff= 31f63e956 in raise () from /lib64/libc.so.6
#2 =C2=A00x00007ff31f6287f4 = in abort () from /lib64/libc.so.6
#3 =C2=A00x0000000000978032 in Excepti= onalCondition (
=C2=A0 =C2=A0 conditionName=3DconditionName@entry=3D0xa4= e970 "!VacuumFailsafeActive",
=C2=A0 =C2=A0 fileName=3DfileNa= me@entry=3D0xa4da38 "../src/backend/access/heap/vacuumlazy.c", li= neNumber=3DlineNumber@entry=3D392) at ../src/backend/utils/error/assert.c:6= 6
#4 =C2=A00x000000000058c598 in heap_vacuum_rel (rel=3D0x7ff31d8a97d0, =
=C2=A0 =C2=A0 params=3D<optimized out>, bstrategy=3D<optimized= out>)
=C2=A0 =C2=A0 at ../src/backend/access/heap/vacuumlazy.c:392#5 =C2=A00x000000000069af1f in table_relation_vacuum (bstrategy=3D0x14ddc= a8,
=C2=A0 =C2=A0 params=3D0x7ffec28585f0, rel=3D0x7ff31d8a97d0)
=C2= =A0 =C2=A0 at ../src/include/access/tableam.h:1705
#6 =C2=A0vacuum_rel (= relid=3Drelid@entry=3D16402, relation=3Drelation@entry=3D0x0,
=C2=A0 = =C2=A0 params=3Dparams@entry=3D0x7ffec28585f0, skip_privs=3Dskip_privs@entr= y=3Dtrue,
=C2=A0 =C2=A0 bstrategy=3Dbstrategy@entry=3D0x14ddca8)
=C2= =A0 =C2=A0 at ../src/backend/commands/vacuum.c:2202
#7 =C2=A00x000000000= 069b0e4 in vacuum_rel (relid=3D16398, relation=3D<optimized out>, =C2=A0 =C2=A0 params=3Dparams@entry=3D0x7ffec2858850, skip_privs=3Dskip_pr= ivs@entry=3Dfalse,
=C2=A0 =C2=A0 bstrategy=3Dbstrategy@entry=3D0x14ddca= 8)
=C2=A0 =C2=A0 at ../src/backend/commands/vacuum.c:2236
#8 =C2=A00x= 000000000069c594 in vacuum (relations=3D0x14dde38,
=C2=A0 =C2=A0 params= =3D0x7ffec2858850, bstrategy=3D0x14ddca8, vac_context=3D0x14ddb90,
=C2= =A0 =C2=A0 isTopLevel=3D<optimized out>) at ../src/backend/commands/v= acuum.c:623

[1] http= s://www.postgresql.org/message-id/CAD21AoAyYBZOiB1UPCPZJHTLk0-arrq5zqNGj%2B= PrsbpdUy%3Dg-g%40mail.gmail.com

--
John Naylor
EDB: http://www.enterprisedb.com
--0000000000002fa92505fa4e00f2--