From: Alec Cozens <acozens@pixelpower.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: LWLock SerializableFinishedList
Thread-Topic: LWLock SerializableFinishedList
Thread-Index: AdwehPU7dO/joFdST/qND9jVOOC4NA==
Date: Fri, 5 Sep 2025 17:02:27 +0000
Message-ID:
 <LO2P123MB17113181847230A9D67C8602CD03A@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
Accept-Language: en-GB, en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?wYOvEXSUQMcxx3StycnFbauN7dxdiwDI7ABdVmOp9yFKM0lg0IQdazAyV9Nl?=
 =?us-ascii?Q?9BKMwYrJUlYXLcCJV5yFc3Sz9FmgoJPXdl/45ZnGDpyfmuwUofaLPZxl7794?=
 =?us-ascii?Q?AWA3OZ2xYqsFtdFtM0QF3fP79/cNeA2e3X5lpWOoWqK5scOMrC46AQO9i4PA?=
 =?us-ascii?Q?zlfjb4ONqf0aAEIDwRzJ57aTyG6fR2VJhnu4mJQ31Kup7X79w8TQQnU4L/Vx?=
 =?us-ascii?Q?tafpWdkxLrAdWKWKJfIsPYTvmcyQ5enHPvZd/9iRjbg9pyOYxyfY2qG+Bjvs?=
 =?us-ascii?Q?q+5UsICmbDSY21W0BWHyE/gT3KVDt9bhVLrlGU02eu/Cuox2JwsKnlbSd+P6?=
 =?us-ascii?Q?5jl0JzxbnkbRhle+t87gFIfguLpxYBQkhvh7LEgZzi8zWTjuthZU8DQN9sxl?=
 =?us-ascii?Q?8d5ZFeWTW/qwjei3jB78ZgdPsKof3xshiepDpdCU9CCPZ0htnOdrK6AHBFba?=
 =?us-ascii?Q?EI0HFkJlzgN6p6zObDlHxmNWd74lnj8l+YlIpgLFBweytjgk03qadFyGPoyX?=
 =?us-ascii?Q?2gjrpQjmuK51ZI5fhLYOKZV5hoczZV9rL127Byq2fINPGBn4t4rX5mt/nyBi?=
 =?us-ascii?Q?PkO2frXY4PAKMSuHcIUVJ7TxJm835SiN7kSI88kdpG48kpGoQfl+kolhzq5i?=
 =?us-ascii?Q?UiyJ3tLfJr4N8ZHwyT7iVvVOINKyHdgYL5D42Md6U9wFMkZVUaGnuelGyixD?=
 =?us-ascii?Q?QMVLIMSa+JCwyW+DovMx+qoWqO5CzjE3pLd7w9NX/MId5ah5kO1KzPjPolZI?=
 =?us-ascii?Q?IFsSHFS8rJ4SZ8Ni14UF7MoqbYO0zjOSi+4FpR3UXUYDh0QMfshXBxKDc3Zb?=
 =?us-ascii?Q?3xdWm306sYyU07k7bVIsWpGDCVmP0myjRmb/5BJPG4sE9BAN6O78yiEzeEnJ?=
 =?us-ascii?Q?EPF7+o/TnqSk5lBeNLh/nNBmXgXLE/LCDXoptxHjbnTI9MWujIKMns2OLIja?=
 =?us-ascii?Q?AqnKMuaQlB2lJEFSm2ii/WM6s5z85nKzuMr4ajXNRsfYdPzVCKx+5Mn4XeN0?=
 =?us-ascii?Q?7JFsFmEQqjoL1YUQ4mMB46VP63UijKfXPx9qkqRChd/8J02iAYTZsG7y6SJY?=
 =?us-ascii?Q?YJ+oWfAbO+n5QroEFatgrBKU82tEwCZyrq7+SaP68kwB12ILeaWdqaqd9Deg?=
 =?us-ascii?Q?Ek6wV+Hi0qogEq5FHrbWuqAG6X1r0wJ85er7IkwCz3fa+KsojEWYB5mdr6GW?=
 =?us-ascii?Q?H3dCMJ7yZxSazIIeQJGn8ZJi0sE6K89yYJk1cLcOFT/+VcWtmcnM2H+renR3?=
 =?us-ascii?Q?EXCfMsK2zag5HfEYKXHFfjtbJGuKI+tfYgwMTbK5/qOoG6Rk52Ql7igcaogi?=
 =?us-ascii?Q?XM2wMVdy+CB8NkBEZ3WZVRSnNaP48yHG2G81t9vZBjOu3bSZaP5tJiX07LI+?=
 =?us-ascii?Q?muGk4iI8T/vOQe0LumG6uHqOhJJbGzgqZJP4PpYYJzqKFREtRJ3e7GVQl0Av?=
 =?us-ascii?Q?sd55IKN7gCPApjvA6J+QL0H4Obt1wUYPT8hAZyYLtqxg8o6CFt2JpRwZDYf+?=
 =?us-ascii?Q?m8kt5F4bUPYzTMOOzXKStBUFhxgpzgyGs9AbOZtwqYTWMQGGvGlkUzgtPlb5?=
 =?us-ascii?Q?LqymMzRCQguTuUQK4jS3EmqsKyp5gflb2ixquTg3?=
Content-Type: multipart/alternative;
	boundary="_000_LO2P123MB17113181847230A9D67C8602CD03ALO2P123MB1711GBRP_"
MIME-Version: 1.0
X-OriginatorOrg: pixelpower.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 5bd3a6d7-927b-43a4-e9ac-08ddec9dfebb
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Sep 2025 17:02:28.3753
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 003c13c0-fabe-446e-ae96-c2ca744f4da0
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: DnMPipLsaJtIyuCx5WfUromyavdLpQS2knzCPDuocY17OELcovfLH5fyQx8IFSrYL8PdvmN28q5acNeDAGzD8g==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CWXP123MB5738
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/LO2P123MB17113181847230A9D67C8602CD03A%40LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_LO2P123MB17113181847230A9D67C8602CD03ALO2P123MB1711GBRP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi

I'm having trouble with PostgreSQL 16.8 on Windows where for maybe days it=
 all works perfectly until the number of active connections start increasi=
ng, until over say 10 minutes all 97 connections are active but seemingly =
waiting on LWLock on SerializableFinishedList. They will remain in this lo=
cked state for some arbitrary period, up to 1 hour 40 minutes, after which=
 the connections will all clear apparently simultaneously and the applicat=
ion continues.

The connections are opened, a few command executed and then closed and ret=
urned to the connection pool.

The application runs on the same server as the postgresql service.

Changing statement_timeout to 1 minute doesn't seem to cancel these "activ=
e" connections.

Most of the application runs in READ COMMITTED isolation level, but the pa=
rticular stored procedure that seems to cause the issue runs in SERIALIZED=
. We end up with "active" but hanging connections running this stored proc=
edure or the associated commands generated by npgqql associated with openi=
ng, closing and returning connections to the pool.

I can't find any information about SerializeableFinishedList or why all th=
e standard timeout parameters seem to have no effect on the cancellation o=
f these hung connections. The npgsql client configuration talks about clea=
ring idle connections, but these are not idle, they are active but hung.

Any thoughts on the matter, or what might cause  a lock on SerializableFin=
ishedList would be much appreciated.

Regards,
Alec

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--_000_LO2P123MB17113181847230A9D67C8602CD03ALO2P123MB1711GBRP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-mic=
rosoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"ht=
tp://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii=
">
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
=09{font-family:"Cambria Math";
=09panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
=09{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
=09{margin:0cm;
=09font-size:12.0pt;
=09font-family:"Aptos",sans-serif;
=09mso-ligatures:standardcontextual;
=09mso-fareast-language:EN-US;}
span.EmailStyle17
=09{mso-style-type:personal-compose;
=09font-family:"Aptos",sans-serif;
=09color:windowtext;}
.MsoChpDefault
=09{mso-style-type:export-only;
=09mso-fareast-language:EN-US;}
@page WordSection1
=09{size:612.0pt 792.0pt;
=09margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
=09{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-GB" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap=
:break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hi<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I&#8217;m having trouble with PostgreSQL 16.8 on Wi=
ndows where for maybe days it all works perfectly until the number of acti=
ve connections start increasing, until over say 10 minutes all 97 connecti=
ons are active but seemingly waiting on LWLock
 on SerializableFinishedList. They will remain in this locked state for so=
me arbitrary period, up to 1 hour 40 minutes, after which the connections =
will all clear apparently simultaneously and the application continues.<o:=
p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">The connections are opened,=20a few command execute=
d and then closed and returned to the connection pool.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">The application runs on the same server as the post=
gresql service.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Changing statement_timeout to 1 minute doesn&#8217;=
t seem to cancel these &#8220;active&#8221; connections.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Most of the application runs in READ COMMITTED isol=
ation level, but the particular stored procedure that seems to cause the i=
ssue runs in SERIALIZED. We end up with &#8220;active&#8221; but hanging c=
onnections running this stored procedure or the associated
 commands generated by npgqql associated with opening, closing and returni=
ng connections to the pool.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I can&#8217;t find any information about Serializea=
bleFinishedList or why all the standard timeout parameters seem to have no=
 effect on the cancellation of these hung connections. The npgsql client c=
onfiguration talks about clearing idle connections,
 but these are not idle, they are active but hung.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Any thoughts on the matter, or what might cause&nbs=
p; a lock on SerializableFinishedList would be much appreciated.<o:p></o:p=
></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Regards,<o:p></o:p></p>
<p class=3D"MsoNormal">Alec<o:p></o:p></p>
</div>
<br clear=3D"both">
______________________________________________________________________<BR>=

This email has been scanned by the Symantec Email Security.cloud service.<=
BR>
For more information please visit http://www.symanteccloud.com<BR>
______________________________________________________________________<BR>=

</body>
</html>

--_000_LO2P123MB17113181847230A9D67C8602CD03ALO2P123MB1711GBRP_--