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 <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1ppIIa-0003N0-6g
	for pgsql-admin@arkaria.postgresql.org; Thu, 20 Apr 2023 00:37:20 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1ppIIX-0001Fh-UQ
	for pgsql-admin@arkaria.postgresql.org; Thu, 20 Apr 2023 00:37:17 +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 <rui@crazybean.net>)
	id 1ppIIX-0001FX-J2
	for pgsql-admin@lists.postgresql.org; Thu, 20 Apr 2023 00:37:17 +0000
Received: from impout008aa.msg.chrl.nc.charter.net ([47.43.20.32]
 helo=impout008.msg.chrl.nc.charter.net)
	by magus.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <rui@crazybean.net>)
	id 1ppIIV-003wAw-50
	for pgsql-admin@postgresql.org; Thu, 20 Apr 2023 00:37:16 +0000
Received: from cerberus.crazybean.net ([71.11.29.254])
	by cmsmtp with ESMTP
	id pIIRp5scMTuGEpIISpjWqu; Thu, 20 Apr 2023 00:37:12 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.4 cv=aruhtAVV c=1 sm=1 tr=0 ts=64408938
 a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17
 a=xqWC_Br6kY4A:10 a=dKHAf1wccvYA:10 a=AI1xt-L-AAAA:8 a=CgdO5-1YSAuYC-iaevMA:9
 a=QEXdDO2ut3YA:10 a=lUHsgcU8LzB8X1uzxfwA:9 a=tQyeUNjZ0PKp-qqT:21
 a=_W_S_7VecoQA:10 a=lqcHg5cX4UMA:10 a=SvLiCHWmo-lsxKAhau-Q:22
Received: from smtpclient.apple (<unknown>
 [2001:470:e117:4:351b:d6b3:19cf:a53])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 7391e9e8
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Wed, 19 Apr 2023 20:37:09 -0400 (EDT)
From: Rui DeSousa <rui@crazybean.net>
Message-Id: <D01D3F50-9172-43FD-B2F2-1FF1BB1FA0CF@crazybean.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_3B976FBC-3BD2-474D-8AFF-AAD09D204960"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.3\))
Subject: Re: pg_cancel_backend and pg_terminate_backend
Date: Wed, 19 Apr 2023 20:37:08 -0400
In-Reply-To: <83b2ee4970954aebb6dbe7d8f5f6e87b@express-scripts.com>
Cc: Wells Oliver <wells.oliver@gmail.com>,
 pgsql-admin <pgsql-admin@postgresql.org>
To: "Wetmore, Matthew (CTR)" <Matthew.Wetmore@express-scripts.com>
References: 
 <CAOC+FBVZFVSF1ntpf7UwQ==YA1KaerSrH0_BH+pmczEp88r2Ug@mail.gmail.com>
 <83b2ee4970954aebb6dbe7d8f5f6e87b@express-scripts.com>
X-Mailer: Apple Mail (2.3696.120.41.1.3)
X-CMAE-Envelope: 
 MS4xfBDhi6TFNEr0f1Vw9H3FEGtGHzUTRrNA829t4s42P+7ebxW5uWnkbZC7cGP4b/vFBMqaoTL2IA6N8ZG/+UGKFwwlLsAv010Ae2eJ3bmalmQmpra0O6Ra
 wGmk1LKqQCq72PNo43C8/Ar5DeYSZXuRdba/kskKICUxYD9effk/TtXFujczf0tsc+Bp/O+eVVqFVYHTncMRiECT7g8WykibaJE1fcbyrTRsYXwHCw0xnmgA
 IjVR3c8c2R2IHfLNzw1/Z/O1TdZdHCSQGDZDogO2sE7lZSaSsVMTspoLQEyh0WDu
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/D01D3F50-9172-43FD-B2F2-1FF1BB1FA0CF%40crazybean.net>
Precedence: bulk


--Apple-Mail=_3B976FBC-3BD2-474D-8AFF-AAD09D204960
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8



> On Apr 19, 2023, at 3:51 PM, Wetmore, Matthew (CTR) =
<Matthew.Wetmore@express-scripts.com> wrote:
>=20
> I usually prefer pg_cancel_backend because it seems.. nicer, but =
lately I've had a troublesome user who leaves transactions open and I've =
scripted up a call to pg_terminate_backend after 60 minutes in an idle =
transaction. It works well.
> =20
> Question: is there any server impact beyond that user's transaction =
when calling pg_terminate_backend?=20

I would advise against doing that for application connections.  What =
happens depends on the application or pooling tech used.  It could =
generate errors in the application as the connection is no longer valid =
yet the application might still being maintaining data structures for =
the given connection. =20

I.e.. It also introduces a race condition; the application picks up the =
connection from the connection pool at the same time the connection is =
terminated on the backend; the end result is an error propagating up to =
end user or the application being require to catch the error and retry, =
etc.=20

I would continue to terminate non application connections without =
concern.  If it=E2=80=99s an issue, fix the application/pooling config; =
terminating backend sessions is a bandaid with other issues.=

--Apple-Mail=_3B976FBC-3BD2-474D-8AFF-AAD09D204960
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div =
class=3D"">On Apr 19, 2023, at 3:51 PM, Wetmore, Matthew (CTR) &lt;<a =
href=3D"mailto:Matthew.Wetmore@express-scripts.com" =
class=3D"">Matthew.Wetmore@express-scripts.com</a>&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div class=3D""><meta =
charset=3D"UTF-8" class=3D""><div style=3D"margin: 0in 0in 0.0001pt; =
font-size: 12pt; font-family: &quot;Times New Roman&quot;, serif; =
caret-color: rgb(0, 0, 0); font-style: normal; font-variant-caps: =
normal; font-weight: 400; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none;" class=3D"">I usually prefer&nbsp;pg_cancel_backend because it =
seems.. nicer, but lately I've had a troublesome user who leaves =
transactions open and I've scripted up a call =
to&nbsp;pg_terminate_backend after 60 minutes in an idle transaction. It =
works well.<o:p class=3D""></o:p></div><div style=3D"caret-color: rgb(0, =
0, 0); font-family: Helvetica; font-size: 14px; font-style: normal; =
font-variant-caps: normal; font-weight: 400; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;" class=3D""><div style=3D"margin: 0in 0in =
0.0001pt; font-size: 12pt; font-family: &quot;Times New Roman&quot;, =
serif;" class=3D""><o:p class=3D"">&nbsp;</o:p></div></div><div =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: =
14px; font-style: normal; font-variant-caps: normal; font-weight: 400; =
letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><div =
style=3D"margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: =
&quot;Times New Roman&quot;, serif;" class=3D"">Question: is there any =
server impact beyond that user's transaction when calling =
pg_terminate_backend?<span =
class=3D"Apple-converted-space">&nbsp;</span></div></div></div></blockquot=
e></div><br class=3D""><div class=3D"">I would advise against doing that =
for application connections. &nbsp;What happens depends on the =
application or pooling tech used. &nbsp;It could generate errors in the =
application as the connection is no longer valid yet the application =
might still being maintaining data structures for the given connection. =
&nbsp;</div><div class=3D""><br class=3D""></div><div class=3D"">I.e.. =
It also introduces a race condition; the application picks up the =
connection from the connection pool at the same time the connection is =
terminated on the backend; the end result is an error propagating up to =
end user or the application being require to catch the error and retry, =
etc.&nbsp;</div><div class=3D""><br class=3D""></div><div class=3D"">I =
would continue to terminate non application connections without concern. =
&nbsp;If it=E2=80=99s an issue, fix the application/pooling config; =
terminating backend sessions is a bandaid with other =
issues.</div></body></html>=

--Apple-Mail=_3B976FBC-3BD2-474D-8AFF-AAD09D204960--