pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedKilling the backend to cancel a long waiting query
11+ messages / 7 participants
[nested] [flat]
* Killing the backend to cancel a long waiting query
@ 2003-09-18 18:04 Paulo Scardine <paulos@cimed.ind.br>
0 siblings, 2 replies; 11+ messages in thread
From: Paulo Scardine @ 2003-09-18 18:04 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; +Cc: pgsql-odbc@postgresql.org; pgsql-interfaces
There are many interfaces that doesn't support canceling a long query other
than killing the application or killing the backend using the server
operational system.
I've searched the docs, faqs and list archives; seen a lot of questions but
no answers.
So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
the backend's PID, start the query in a new thread, and kill the backend
using another connection if I want. Its ugly, I know, but worked wonderfully
with ODBC, the Delphi developers here and the users are very happy.
Questions are:
1) is there a better way to do that?
2) is it safe to kill the backend this way (other than an user killing the
wrong postgres owned proccess)?
3) is this useful for anyone else?
Regards,
--
Paulo Scardine
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [HACKERS] Killing the backend to cancel a long waiting query
@ 2003-09-18 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paulo Scardine <paulos@cimed.ind.br>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-09-18 19:08 UTC (permalink / raw)
To: Paulo Scardine <paulos@cimed.ind.br>; +Cc: pgsql-hackers@postgresql.org; pgsql-odbc@postgresql.org; pgsql-interfaces
"Paulo Scardine" <paulos@cimed.ind.br> writes:
> So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
> the backend's PID, start the query in a new thread, and kill the backend
> using another connection if I want.
I trust when you say "kill", you really mean "send SIGINT" ...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-18 19:19 Paulo Scardine <paulos@cimed.ind.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Paulo Scardine @ 2003-09-18 19:19 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> ...
> I trust when you say "kill", you really mean "send SIGINT" ...
> ...
I'm sending a SIGTERM. Would SIGINT be more appropriate?
Thank you,
--
Paulo Scardine
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-18 19:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paulo Scardine <paulos@cimed.ind.br>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-09-18 19:54 UTC (permalink / raw)
To: Paulo Scardine <paulos@cimed.ind.br>; +Cc: pgsql-hackers@postgresql.org
"Paulo Scardine" <paulos@cimed.ind.br> writes:
>> I trust when you say "kill", you really mean "send SIGINT" ...
> I'm sending a SIGTERM. Would SIGINT be more appropriate?
Yes --- that would actually cancel the query, not cause the backend to
shut down.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [HACKERS] Killing the backend to cancel a long waiting query
@ 2003-09-18 23:46 Hiroshi Inoue <Inoue@tpf.co.jp>
parent: Paulo Scardine <paulos@cimed.ind.br>
1 sibling, 0 replies; 11+ messages in thread
From: Hiroshi Inoue @ 2003-09-18 23:46 UTC (permalink / raw)
To: Paulo Scardine <paulos@cimed.ind.br>; +Cc: pgsql-hackers@postgresql.org; pgsql-odbc@postgresql.org; pgsql-interfaces
Paulo Scardine wrote:
>
> There are many interfaces that doesn't support canceling a long query other
> than killing the application or killing the backend using the server
> operational system.
>
> I've searched the docs, faqs and list archives; seen a lot of questions but
> no answers.
>
> So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
> the backend's PID, start the query in a new thread, and kill the backend
> using another connection if I want. Its ugly, I know, but worked wonderfully
> with ODBC, the Delphi developers here and the users are very happy.
>
> Questions are:
> 1) is there a better way to do that?
Call SQLCancel().
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-21 05:42 Kevin Brown <kevin@sysexperts.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Kevin Brown @ 2003-09-21 05:42 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Tom Lane wrote:
> "Paulo Scardine" <paulos@cimed.ind.br> writes:
> >> I trust when you say "kill", you really mean "send SIGINT" ...
>
> > I'm sending a SIGTERM. Would SIGINT be more appropriate?
>
> Yes --- that would actually cancel the query, not cause the backend to
> shut down.
Ahh...this is very useful information.
Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
command that takes a backend ID as its argument (and, of course, does
the appropriate checks of whether you're a superuser or the owner of
the backend) and sends the appropriate signal to the target backend?
That would make it possible for users to kill their own runaway queries
without having to run as whatever user PG is running as.
--
Kevin Brown kevin@sysexperts.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-21 06:25 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Kevin Brown <kevin@sysexperts.com>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2003-09-21 06:25 UTC (permalink / raw)
To: Kevin Brown <kevin@sysexperts.com>; +Cc: pgsql-hackers@postgresql.org
> Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
> command that takes a backend ID as its argument (and, of course, does
> the appropriate checks of whether you're a superuser or the owner of
> the backend) and sends the appropriate signal to the target backend?
>
> That would make it possible for users to kill their own runaway queries
> without having to run as whatever user PG is running as.
It would be awesome for phpPGAdmin as well. eg. Superusers would be able
to cancel sql queries that gumby users are running, etc.
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-21 10:26 Dave Page <dpage@vale-housing.co.uk>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Dave Page @ 2003-09-21 10:26 UTC (permalink / raw)
To: chriskl@familyhealth.com.au; +Cc: kevin@sysexperts.com; pgsql-hackers@postgresql.org
It's rumoured that Christopher Kings-Lynne once said:
>> Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
>> command that takes a backend ID as its argument (and, of course, does
>> the appropriate checks of whether you're a superuser or the owner of
>> the backend) and sends the appropriate signal to the target backend?
>>
>> That would make it possible for users to kill their own runaway
>> queries without having to run as whatever user PG is running as.
>
>
> It would be awesome for phpPGAdmin as well. eg. Superusers would be
> able to cancel sql queries that gumby users are running, etc.
I'll second that for pgAdmin. I have times in the past where it would have
been useful on my own systems as well.
Regards, Dave.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-22 17:53 Paulo Scardine <paulos@cimed.ind.br>
parent: Dave Page <dpage@vale-housing.co.uk>
0 siblings, 1 reply; 11+ messages in thread
From: Paulo Scardine @ 2003-09-22 17:53 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: pgsql-hackers@postgresql.org
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?
--
Paulo Scardine
----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
> > It would be awesome for phpPGAdmin as well. eg. Superusers would be
> > able to cancel sql queries that gumby users are running, etc.
>
> I'll second that for pgAdmin. I have times in the past where it would have
> been useful on my own systems as well.
> Regards, Dave.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-22 18:29 Robert Treat <xzilla@users.sourceforge.net>
parent: Paulo Scardine <paulos@cimed.ind.br>
0 siblings, 1 reply; 11+ messages in thread
From: Robert Treat @ 2003-09-22 18:29 UTC (permalink / raw)
To: Paulo Scardine <paulos@cimed.ind.br>; +Cc: Dave Page <dpage@vale-housing.co.uk>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Killing the backend to cancel a long waiting query
@ 2003-09-22 19:02 Paulo Scardine <paulos@cimed.ind.br>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 11+ messages in thread
From: Paulo Scardine @ 2003-09-22 19:02 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Dave Page <dpage@vale-housing.co.uk>; pgsql-hackers@postgresql.org
I do not know how to do that. I learned that I can send a SIGINT to a
backend to cancel a query, but I dont know how to kill just one transaction.
I have only "pg_getpid" and "pg_killpid" as interfaces to system functions
"getpid" and "kill".
BTW, what information can I get about the current running backends on the
internal structures? Or do I have to ask the OS?
Thank you,
--
Paulo Scardine
----- Original Message -----
From: "Robert Treat" <xzilla@users.sourceforge.net>
Subject: Re: [HACKERS] Killing the backend to cancel a long waiting query
>
> 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?
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-09-22 19:02 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 18:04 Killing the backend to cancel a long waiting query Paulo Scardine <paulos@cimed.ind.br>
2003-09-18 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-18 19:19 ` Paulo Scardine <paulos@cimed.ind.br>
2003-09-18 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-21 05:42 ` Kevin Brown <kevin@sysexperts.com>
2003-09-21 06:25 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-09-21 10:26 ` Dave Page <dpage@vale-housing.co.uk>
2003-09-22 17:53 ` Paulo Scardine <paulos@cimed.ind.br>
2003-09-22 18:29 ` Robert Treat <xzilla@users.sourceforge.net>
2003-09-22 19:02 ` Paulo Scardine <paulos@cimed.ind.br>
2003-09-18 23:46 ` Hiroshi Inoue <Inoue@tpf.co.jp>
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