Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sID0a-00DuAH-Vl for pgsql-admin@arkaria.postgresql.org; Fri, 14 Jun 2024 19:54:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sID0Y-005HuS-PS for pgsql-admin@arkaria.postgresql.org; Fri, 14 Jun 2024 19:54:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sID0Y-005HuK-BU for pgsql-admin@lists.postgresql.org; Fri, 14 Jun 2024 19:54:47 +0000 Received: from qs51p00im-qukt01080302.me.com ([17.57.155.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sID0W-001gDB-Uq for pgsql-admin@postgresql.org; Fri, 14 Jun 2024 19:54:46 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1718394883; bh=Xp5sSr/Bnbgo1Ckt0zvH1WklsaLW+tRumsvZiVG5MEY=; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; b=TxZz2ckmp5f7ZQaSAgqzgIXbOWH24BV1h6jRq4QZJxu6defkwzVlsLit/3BrfLiFb 7OOKVqdVe1W0HHnOnWUto8ttpuwI7v2VDf0XsgceIaG7CVpmOLZLCS+q2DyHt4waIj s8/OLmRlsppCQ79ymX/Pfyj10yKMkRr3ggsXC8vc+Tiw0zNVWwEtLmg1so4vH6fh0H gDp94VhZ/ldX8vWeYbI/7/9TFnEPi8tiVTBVFyxVjx/FSVk90rWAj0Sf4aUVZgr7rY v7vXHwWnphlhepmM5Ql/vZKLSFVMxkvqi0MCybt6Enbxn5UG+otZc3YDXopiD0aZNl ryB+iBNjlyfnQ== Received: from smtpclient.apple (qs51p00im-dlb-asmtp-mailmevip.me.com [17.57.155.28]) by qs51p00im-qukt01080302.me.com (Postfix) with ESMTPSA id 692371140382; Fri, 14 Jun 2024 19:54:42 +0000 (UTC) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_9A3BB820-66C6-4324-AD16-E6D543C61F7C" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.8\)) Subject: Re: Keepalive Date: Fri, 14 Jun 2024 15:54:38 -0400 In-Reply-To: <2300378.1718378913@sss.pgh.pa.us> Cc: pgsql-admin To: Tom Lane References: <161042FE-FC86-456B-A8E1-AED38AE01820@icloud.com> <2300378.1718378913@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3696.120.41.1.8) X-Proofpoint-GUID: 0Qw4CSFlcDmLkRgEgZAGzpxmNm-JZY8k X-Proofpoint-ORIG-GUID: 0Qw4CSFlcDmLkRgEgZAGzpxmNm-JZY8k X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16 definitions=2024-06-14_17,2024-06-14_03,2024-05-17_01 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 mlxlogscore=999 suspectscore=0 clxscore=1015 adultscore=0 malwarescore=0 mlxscore=0 bulkscore=0 phishscore=0 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2406140138 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_9A3BB820-66C6-4324-AD16-E6D543C61F7C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 14, 2024, at 11:28 AM, Tom Lane wrote: >=20 > Rui DeSousa writes: >> I have a very long running query that is not being terminated after a = keep alive timeout event. The situation is that the client drops from = the network, the servers=E2=80=99 tcp/ip stack drops the connection, and = the Postgres query continues to run without a network connection. =20 >=20 >> The given system is running on Linux and I=E2=80=99m being told this = is expected behavior; however, that is not has not been my experience. = My preferred platform to run Postgres on is FreeBSD and in cases like = this the Postgres session is also terminated once the tcp/ip connection = is dropped by the kernel. >=20 > Really? >=20 > I would expect the query to keep running until the backend tries to > perform some I/O to the client. How quickly that happens would depend > a great deal on the details of the query, but not on which OS you're > running on. >=20 > regards, tom lane I just tried the following spinner() function on FreeBSD and the keep = alive timeout event cause both the network connection to be torn down = along with the Postgres session -- as I would expect it to do. I will = try this exact function on the Linux system and see if I get different = results and report back; however, I might not be able to test it out = until next week. create or replace function spinner() returns void as $$ declare _x bigint :=3D 0; begin loop _x :=3D _x + 1; end loop; end; $$ language plpgsql ; --Apple-Mail=_9A3BB820-66C6-4324-AD16-E6D543C61F7C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Jun 14, 2024, at 11:28 AM, Tom Lane <tgl@sss.pgh.pa.us> = wrote:

Rui DeSousa <rui.desousa@icloud.com> writes:
I have a very long = running query that is not being terminated after a keep alive timeout = event.  The situation is that the client drops from the network, = the servers=E2=80=99 tcp/ip stack drops the connection, and the Postgres = query continues to run without a network connection.  

The given system is running on Linux and I=E2=80=99m being = told this is expected behavior; however, that is not has not been my = experience.  My preferred platform to run Postgres on is FreeBSD = and in cases like this the Postgres session is also terminated once the = tcp/ip connection is dropped by the kernel.
Really?

I would expect the = query to keep running until the backend tries to
perform = some I/O to the client.  How quickly that happens would depend
a great deal on the details of the query, but not on which OS = you're
running on.

regards, = tom lane


I just tried the = following spinner() function on FreeBSD and the keep alive timeout event = cause both the network connection to be torn down along with the = Postgres session -- as I would expect it to do.  I will try this = exact function on the Linux system and see if I get different results = and report back; however, I might not be able to test it out until next = week.


create or = replace function spinner()
returns void
as $$
declare
 _x bigint :=3D 0;
begin
  =  loop
    =  _x :=3D _x + 1;
   end loop;
end;
$$ = language plpgsql
;



= --Apple-Mail=_9A3BB820-66C6-4324-AD16-E6D543C61F7C--