From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: pg_cancel_backend and pg_terminate_backend
Date: Wed, 19 Apr 2023 19:53:19 +0000
Message-ID: <d10305f0ea744c7c9d279ca327d8d9fb@express-scripts.com> (raw)
In-Reply-To: <8d16a5ba-a4bc-f139-5f25-daef5ae85340@sqlexec.com>
References: <CAOC+FBVZFVSF1ntpf7UwQ==YA1KaerSrH0_BH+pmczEp88r2Ug@mail.gmail.com>
<8d16a5ba-a4bc-f139-5f25-daef5ae85340@sqlexec.com>
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.
--
Wells Oliver
wells.oliver@gmail.com<mailto:wellsoliver@gmail.com>
Regards,
Michael Vitale
Michaeldba@sqlexec.com<mailto:michaelvitale@sqlexec.com>
703-600-9343
[cid:image001.jpg@01D972CE.AD3DD3C0]
Attachments:
[image/jpeg] image001.jpg (20.6K, ../d10305f0ea744c7c9d279ca327d8d9fb@express-scripts.com/3-image001.jpg)
download | view image
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: Matthew.Wetmore@express-scripts.com, MichaelDBA@sqlexec.com, wells.oliver@gmail.com
Subject: Re: pg_cancel_backend and pg_terminate_backend
In-Reply-To: <d10305f0ea744c7c9d279ca327d8d9fb@express-scripts.com>
* 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