From: Rui DeSousa <rui@crazybean.net>
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_cancel_backend and pg_terminate_backend
Date: Wed, 19 Apr 2023 20:37:08 -0400
Message-ID: <D01D3F50-9172-43FD-B2F2-1FF1BB1FA0CF@crazybean.net> (raw)
In-Reply-To: <83b2ee4970954aebb6dbe7d8f5f6e87b@express-scripts.com>
References: <CAOC+FBVZFVSF1ntpf7UwQ==YA1KaerSrH0_BH+pmczEp88r2Ug@mail.gmail.com>
<83b2ee4970954aebb6dbe7d8f5f6e87b@express-scripts.com>
> On Apr 19, 2023, at 3:51 PM, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:
>
> 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.
>
> Question: is there any server impact beyond that user's transaction when calling pg_terminate_backend?
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.
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.
I would continue to terminate non application connections without concern. If it’s an issue, fix the application/pooling config; terminating backend sessions is a bandaid with other issues.=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, Matthew.Wetmore@express-scripts.com, wells.oliver@gmail.com
Subject: Re: pg_cancel_backend and pg_terminate_backend
In-Reply-To: <D01D3F50-9172-43FD-B2F2-1FF1BB1FA0CF@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox