Обсуждение: pg_cancel_backend and pg_terminate_backend

Поиск
Список
Период
Сортировка

pg_cancel_backend and pg_terminate_backend

От
Wells Oliver
Дата:
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.

--

Re: pg_cancel_backend and pg_terminate_backend

От
MichaelDBA
Дата:
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.

--


Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 


Вложения

pg_cancel_backend and pg_terminate_backend

От
"Wetmore, Matthew (CTR)"
Дата:

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.

 

--

pg_cancel_backend and pg_terminate_backend

От
"Wetmore, Matthew (CTR)"
Дата:

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.

 

--

 



Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

 

Вложения

Re: pg_cancel_backend and pg_terminate_backend

От
Wells Oliver
Дата:
Correct. Is there broader risk to the running server and other connections if a given user's backend is terminated?

On Wed, Apr 19, 2023 at 12:51 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

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.

 

--



--

Re: pg_cancel_backend and pg_terminate_backend

От
Thomas Kellerer
Дата:
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?




Re: pg_cancel_backend and pg_terminate_backend

От
Wells Oliver
Дата:
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?





--

Re: pg_cancel_backend and pg_terminate_backend

От
Benjamin Leis
Дата:
We use pg_terminate within some monitoring code for all the long running things in our system with no issues. 

On Wed, Apr 19, 2023, 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?



Re: pg_cancel_backend and pg_terminate_backend

От
Holger Jakobs
Дата:
Am 19.04.23 um 22:13 schrieb Wells Oliver:
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
Вложения

Re: pg_cancel_backend and pg_terminate_backend

От
Rui DeSousa
Дата:


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.

Re: pg_cancel_backend and pg_terminate_backend

От
Rui DeSousa
Дата:


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.

One more thought if it wasn’t apparent already.  Use TCP keep alive -- to identify truly abandoned idle sessions and have those connection torn down.