MIME-Version: 1.0
References: <LO2P123MB17113181847230A9D67C8602CD03A@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
 <CALL-XeMUhHnqZx083rWx14xX2XjqBeq7h94TOfXBtoaSGzhxWg@mail.gmail.com>
 <LO2P123MB1711F8A621404614ED676F7DCD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
 <LO2P123MB1711F0E3DF3BD9E9C092ACD4CD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
In-Reply-To: <LO2P123MB1711F0E3DF3BD9E9C092ACD4CD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
From: Justin <zzzzz.graf@gmail.com>
Date: Tue, 9 Sep 2025 15:31:51 -0400
Message-ID: <CALL-XeO5e92GHJN6_krXLYLGAzWDv9qkgNp0uoio7Uqv6G0qdw@mail.gmail.com>
Subject: Re: LWLock SerializableFinishedList
To: Alec Cozens <acozens@pixelpower.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cea489063e635bb4"
Archived-At: <https://www.postgresql.org/message-id/CALL-XeO5e92GHJN6_krXLYLGAzWDv9qkgNp0uoio7Uqv6G0qdw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Sep 8, 2025 at 12:04=E2=80=AFPM Alec Cozens <acozens@pixelpower.com=
> wrote:

> Looking at the postgresql code, the LW lock SerializableFinishedList
> appears to be acquired and then released, usually in the duration of a ca=
ll
> to a procedure. Base on that (admittedly maybe faulty) view of the code, =
I
> am surprised to see the lock held open for over and hour and a half.
>
>
>
>
>
Hi Alec,

Clarification  Lock_timeout and Statement_timeout work the same way, they
just differ in what triggers them.  Lock timeout is triggered when a lock
can not be acquired in the specified time throwing an error.
Statement_Timeout is triggered when any specific command/query can not be
executed in the specific time.

the error message

could not truncate directory "pg_serial": apparent wraparound

This is concerning:  do you have any replicas??  There  is a known issue
when hot_standby_feedback =3D on and replica has long running transaction
running, SLRU will overflow causing PostgreSQL to go to a crawl. Only after
the replica releases whatever locks it has does performance return to
normal.  This has been mitigated in PostgreSQL 17 with these configurable
settings
https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-MULT=
IXACT-MEMBER-BUFFERS


I have no idea if SLRU directly affects serialized transactions, I would
suspect that it does as SLRU affects all transactions and if it overflows
bad things start happening.  The next time this happen look at the replica
to see if there are any long running transactions

All the conditions I can think of that can cause this is a  transaction
sitting out there holding onto an XID causing the serialized transactions
to wait

Thank you
Justin

--000000000000cea489063e635bb4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 8, 2025=
 at 12:04=E2=80=AFPM Alec Cozens &lt;<a href=3D"mailto:acozens@pixelpower.c=
om">acozens@pixelpower.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div class=3D"msg-4896264870640200118">


<div lang=3D"EN-GB" style=3D"overflow-wrap: break-word;">
<div class=3D"m_-4896264870640200118WordSection1">
<p class=3D"MsoNormal"><span>Looking at the postgresql code, the LW lock
</span>SerializableFinishedList appears to be acquired and then released, u=
sually in the duration of a call to a procedure. Base on that (admittedly m=
aybe faulty) view of the code, I am surprised to see the lock held open for=
 over and hour and a half.<span><u></u><u></u></span></p>
<p class=3D"MsoNormal"><span><u></u>=C2=A0<u></u></span></p>
<div>
<div style=3D"border-right:none;border-bottom:none;border-left:none;border-=
top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class=3D"MsoNormal"><br></p></div></div></div></div></div></blockquote><=
div><br></div><div>Hi Alec,<br><br>Clarification=C2=A0 Lock_timeout and Sta=
tement_timeout work the same way, they just differ in=C2=A0what triggers th=
em.=C2=A0 Lock timeout is triggered when a lock can not be acquired in the =
specified=C2=A0time throwing an error. Statement_Timeout is triggered when =
any specific command/query can not be executed in the specific time.=C2=A0=
=C2=A0<br><br>the error message=C2=A0</div><div><br><span style=3D"text-ind=
ent:48px">could not truncate directory &quot;pg_serial&quot;: apparent wrap=
around</span><br><br>This is concerning:=C2=A0 do you have any replicas??=
=C2=A0 There=C2=A0 is a known issue=C2=A0 when hot_standby_feedback =3D on =
and replica has long running transaction running, SLRU will overflow causin=
g PostgreSQL to go to a crawl. Only after the replica releases whatever=C2=
=A0locks it has does performance return to normal.=C2=A0 This has been miti=
gated in PostgreSQL 17 with these configurable settings=C2=A0<br><a href=3D=
"https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-MUL=
TIXACT-MEMBER-BUFFERS">https://www.postgresql.org/docs/devel/runtime-config=
-resource.html#GUC-MULTIXACT-MEMBER-BUFFERS</a>=C2=A0 =C2=A0<br><br>I have =
no idea if SLRU directly affects serialized transactions, I would suspect t=
hat it does as SLRU affects all transactions and if it overflows bad things=
 start happening.=C2=A0 The next time this happen look at the replica to se=
e if there are any long running transactions</div><div><br></div><div>All t=
he conditions I can think of that can cause this is a=C2=A0 transaction sit=
ting out there holding onto an XID causing the serialized=C2=A0transactions=
 to wait=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0<br><br>Thank you<br>Justin<br>=C2=
=A0 =C2=A0</div></div></div>

--000000000000cea489063e635bb4--