Обсуждение: 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
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