Обсуждение: pg_cancel_backend and pg_terminate_backend
Regards,
Michael Vitale
Wells Oliver wrote on 4/19/2023 3:41 PM:
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 feel like there _used_ to be, maybe in PG9, where I recall maybe seeing a server disconnect or something when using terminate.Just curious about this approach. Thanks.
Regards,
Michael Vitale
703-600-9343
Вложения
pg_cancel_backend()
A function which cancels a backend's current query
pg_terminate_backend()
A function which instructs a backend to terminate
From: Wells Oliver <wells.oliver@gmail.com>
Sent: Wednesday, April 19, 2023 2:42 PM
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: [EXTERNAL] pg_cancel_backend and pg_terminate_backend
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 feel like there _used_ to be, maybe in PG9, where I recall maybe seeing a server disconnect or something when using terminate.
Just curious about this approach. Thanks.
You had to be a SU in 8.0, 9.0 allowed you to cancel your own query.
From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Wednesday, April 19, 2023 2:46 PM
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: [EXTERNAL] Re: pg_cancel_backend and pg_terminate_backend
My experience with the older PG versions is that I couldn't stop a query sometimes with pg_cancel_backend, so I had to resort to pg_terminate_backend.
Regards,
Michael Vitale
Wells Oliver wrote on 4/19/2023 3:41 PM:
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 feel like there _used_ to be, maybe in PG9, where I recall maybe seeing a server disconnect or something when using terminate.
Just curious about this approach. Thanks.
Вложения
pg_cancel_backend()
A function which cancels a backend's current query
pg_terminate_backend()
A function which instructs a backend to terminate
From: Wells Oliver <wells.oliver@gmail.com>
Sent: Wednesday, April 19, 2023 2:42 PM
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: [EXTERNAL] pg_cancel_backend and pg_terminate_backend
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 feel like there _used_ to be, maybe in PG9, where I recall maybe seeing a server disconnect or something when using terminate.
Just curious about this approach. Thanks.
wells.oliver@gmail.com
Wells Oliver schrieb am 19.04.2023 um 21:41: > 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. Why don't you use the idle_in_transaction_session_timeout to do this automatically?
Wells Oliver schrieb am 19.04.2023 um 21:41:
> 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.
Why don't you use the idle_in_transaction_session_timeout to do this automatically?
wells.oliver@gmail.com
Wells Oliver schrieb am 19.04.2023 um 21:41:
> 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.
Why don't you use the idle_in_transaction_session_timeout to do this automatically?
Because longer idle transactions are used by a bunch of processes, but it's a certain group of users I don't want having idle transactions, so here we are...On Wed, Apr 19, 2023 at 1:11 PM Thomas Kellerer <shammat@gmx.net> wrote:Wells Oliver schrieb am 19.04.2023 um 21:41:
> 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.
Why don't you use the idle_in_transaction_session_timeout to do this automatically?
Actually, you can set this for certain users:
ALTER ROLE alice SET idle_in_transaction_session_timeout TO '100 s';
But the fact that this is possible implies that each session can set this parameter to any value, AFAIK. Correct me if I'm wrong.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
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?
On Apr 19, 2023, at 8:37 PM, Rui DeSousa <rui@crazybean.net> wrote: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.