Postgresql 8.0 and Cancel/Kill backend functions

The cancel function is implemented. See
http://developer.postgresql.org/docs/postgres/functions-admin.html#FUNCT
IONS-ADMIN-SIGNAL-TABLE.

Kill function was considered too dangerous.

//Magnus

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tony Caduto
Sent: Wednesday, January 12, 2005 7:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgresql 8.0 and Cancel/Kill backend functions

Does anyone know if such functions have been added to version 8?
I saw lots of discussion while searching google and saw
references to patches applied and docs.

Google link:

http://groups-beta.google.com/group/comp.databases.postgresql.

patches/browse_thread/thread/8dc627fdbe4b830d/2b4364d42a95b885?q=Cancel%
2FKill+backend+functions+--+docs&_done=%>
2Fgroups%3Fq%3DCancel%2FKill+backend+functions+--+docs%26qt_s%

On Wed, Jan 12, 2005 at 12:00:22PM -0600, Tony Caduto wrote:

Does anyone know if such functions have been added to version 8?

See the 8.0 Release Notes in the development documentation:

* Add function to send cancel request to other backends (Magnus)

See also the "System Administration Functions" section in the
"Functions and Operators" chapter.

Here's an example:

Session 1:
CREATE FUNCTION sleep(integer) RETURNS void AS $$
sleep $_[0];
$$ LANGUAGE plperlu;
SELECT sleep(60);

Session 2:
SELECT procpid, current_query FROM pg_stat_activity;
procpid | current_query
---------+-------------------
...
95609 | SELECT sleep(60);
...
SELECT pg_cancel_backend(95609);

Session 1:
ERROR: canceling query due to user request

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Magnus Hagander wrote:

The cancel function is implemented. See
http://developer.postgresql.org/docs/postgres/functions-admin.html#FUNCT
IONS-ADMIN-SIGNAL-TABLE.

Kill function was considered too dangerous.

Pity - I would have loved this for my test harnesses. I need to drop and
recreate the database between each test and, unless I can kill them, a
test that fails to close a connection victimizes all subsequent tests.

(But not a showstopper in our case - we replace the connect method with
a wrapper and have the harnesses keep track of the connection. This only
leaves connections opened by spawned processes a problem.)

It would be great if this was available as an external method I could
install into a particular database. Hmm... I guess it wouldn't be
difficult to write this - it would simply involve selecting the procpid
from pg_stat_activity and sending a kill signal to it, wouldn't it?

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

The cancel function is implemented. See

http://developer.postgresql.org/docs/postgres/functions-admin.html#FUN

CT
IONS-ADMIN-SIGNAL-TABLE.

Kill function was considered too dangerous.

Pity - I would have loved this for my test harnesses. I need
to drop and recreate the database between each test and,
unless I can kill them, a test that fails to close a
connection victimizes all subsequent tests.

(But not a showstopper in our case - we replace the connect
method with a wrapper and have the harnesses keep track of
the connection. This only leaves connections opened by
spawned processes a problem.)

It would be great if this was available as an external method
I could install into a particular database. Hmm... I guess it
wouldn't be difficult to write this - it would simply involve
selecting the procpid from pg_stat_activity and sending a
kill signal to it, wouldn't it?

Yes, if you can do it from the same machine. If you want a functino that
works across the network, you can look at the code for the cancel
functino and create a copy that just sends a different signal.

//Magnus