X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5219DD1C9E1 for ; Mon, 22 Sep 2003 18:30:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 76833-08 for ; Mon, 22 Sep 2003 15:29:37 -0300 (ADT) Received: from localhost.localdomain (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id EFEAAD1C9F2 for ; Mon, 22 Sep 2003 15:29:35 -0300 (ADT) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id h8MHM9qd012924; Mon, 22 Sep 2003 13:22:09 -0400 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h8MITVZ14136; Mon, 22 Sep 2003 14:29:32 -0400 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TD8ATKWL; Mon, 22 Sep 2003 14:29:30 -0400 Subject: Re: Killing the backend to cancel a long waiting query From: Robert Treat To: Paulo Scardine Cc: Dave Page , pgsql-hackers@postgresql.org In-Reply-To: <04f301c38132$7804bc30$6101a8c0@Paulo> References: <20030921054224.GF3845@filer> <20030921142434.S61926-100000@houston.familyhealth.com.au> <50096.80.177.99.193.1064140018.squirrel@ssl.vale-housing.co.uk> <04f301c38132$7804bc30$6101a8c0@Paulo> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 22 Sep 2003 14:29:31 -0400 Message-Id: <1064255371.25694.1903.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/1120 X-Sequence-Number: 44132 On Mon, 2003-09-22 at 13:53, Paulo Scardine wrote: > I can implement it as C functions, I think. > Would be nice to have something like: > > Test=# select pg_list_backends(); > pid | conn_id | user | database | time | host | status > -------+----------+----------+-----------+------+---------------+-------- > 4724 | 35445134 | marcelo | test | 0:34 | 192.168.2.212 | select > 18737 | 15412337 | postgres | template1 | 0:00 | 192.168.1.65 | idle > 18693 | 11448964 | postgres | test | 0:00 | 127.0.0.1 | idle > (3 rows) > > Test=# select pg_stopquery_pid(4724); > pg_stopquery_pid > ------------------ > 0 > > Is it worth? Yes, but you may need a way to ensure that the query canceled is the same one that you really want to cancel. (imagine come connection pooling and bad luck mixed together so that the long running query actually ends but another query fires up between the time you lookup the long running query and the time you issue the kill...). maybe transaction id as well as pid for arguments? Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL