Thread: Cancelling long running query?
How can I cancel a long running query besides quitting the app? TIA Patrick Hatcher Macys.Com
Patrick, > How can I cancel a long running query besides quitting the app? > TIA Methods: 1) Shutdown and restart the database server with pg_ctl -m fast restart. 2) Kill the client connection proccess on the server side. This is dangerous if the long-running query is a data-modification query. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> How can I cancel a long running query besides quitting the app?
> Methods:
> 1) Shutdown and restart the database server with pg_ctl -m fast restart.
Better is just to send SIGINT to the individual backend running the
query. This is equivalent to the client having made a QueryCancel
request.
> 2) Kill the client connection proccess on the server side. This is dangerous
> if the long-running query is a data-modification query.
No more dangerous than any other forced database crash ... which is what
you will get if you kill a backend process ...
regards, tom lane
I have a table a:
create tabel a( marca int4,
mo varchar(1)
);
This is the contents of the table:
marca mo
1 C
2 C
4 B
5 O
1 C
1 B
I need a SQL interogation with following results:
marca concedii boala obligatii
1 2 1 0
2 1 0 0
4 0 1 0
5 0 0 1
How can I do that?
Thanks!
This is actually pretty hard. The best I could come up with is
select id, A = case name when 'A' then count(*) else 0 end,
B = case name when 'B' then count(*) else 0 end,
C = case name when 'C' then count(*) else 0 end
from test
group by id, name
which doesn't really do what you want. I'm sure you could work out
something eventually, but that would probably take a long time. My
suggestion would be to actually create a temp table and use that instead.
Something like
Create table temp_a(marca int4,
condceddi int4,
boala int4,
obligatii int4)
insert into temp_a (marca)
select distinct marca
from a
update temp_a set condceddi = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'A')
update temp_a set boaloa = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'B')
update temp_a set obligatti = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'C')
select * from temp_a
and then drop the table (or, better yet, create a temp table - but I'm not
sure how to do this in postgres).
If anyone knows a better way, I would be most interested to know.
---
Rob
**************************
Rob Cherry
mailto:rob@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of cristi
> Sent: 12 November 2002 08:46
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] SQL
>
>
> I have a table a:
> create tabel a( marca int4,
> mo varchar(1)
> );
>
> This is the contents of the table:
> marca mo
> 1 C
> 2 C
> 4 B
> 5 O
> 1 C
> 1 B
>
> I need a SQL interogation with following results:
>
> marca concedii boala obligatii
> 1 2 1 0
> 2 1 0 0
> 4 0 1 0
> 5 0 0 1
>
> How can I do that?
>
>
> Thanks!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Yes! Excellent! Thank you very very much! > This is actually pretty hard. The best I could come up with is > > select id, A = case name when 'A' then count(*) else 0 end, > B = case name when 'B' then count(*) else 0 end, > C = case name when 'C' then count(*) else 0 end > from test > group by id, name > > > > which doesn't really do what you want. I'm sure you could work out > something eventually, but that would probably take a long time. My > suggestion would be to actually create a temp table and use that instead. > Something like > > Create table temp_a(marca int4, > condceddi int4, > boala int4, > obligatii int4) > > insert into temp_a (marca) > select distinct marca > from a > > update temp_a set condceddi = (select count(ma) from a as a > where a.id = temp_a.id > and name like 'A') > > update temp_a set boaloa = (select count(ma) from a as a > where a.id = temp_a.id > and name like 'B') > > update temp_a set obligatti = (select count(ma) from a as a > where a.id = temp_a.id > and name like 'C') > > select * from temp_a > > and then drop the table (or, better yet, create a temp table - but I'm not > sure how to do this in postgres). > > If anyone knows a better way, I would be most interested to know. > > > --- > Rob > > ************************** > Rob Cherry > mailto:rob@jamwarehouse.com > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ************************** > > > > -----Original Message----- > > From: pgsql-novice-owner@postgresql.org > > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of cristi > > Sent: 12 November 2002 08:46 > > To: pgsql-novice@postgresql.org > > Subject: [NOVICE] SQL > > > > > > I have a table a: > > create tabel a( marca int4, > > mo varchar(1) > > ); > > > > This is the contents of the table: > > marca mo > > 1 C > > 2 C > > 4 B > > 5 O > > 1 C > > 1 B > > > > I need a SQL interogation with following results: > > > > marca concedii boala obligatii > > 1 2 1 0 > > 2 1 0 0 > > 4 0 1 0 > > 5 0 0 1 > > > > How can I do that? > > > > > > Thanks! > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Tue, 12 Nov 2002 08:45:37 +0200, "cristi" <cristi@dmhi.ct.ro>
wrote:
>This is the contents of the table:
>marca mo
>1 C
>2 C
>[...]
>
>I need a SQL interogation with following results:
>marca concedii boala obligatii
> 1 2 1 0
> 2 1 0 0
> 4 0 1 0
> 5 0 0 1
SELECT marca,
SUM(CASE mo WHEN 'C' THEN 1 ELSE 0 END) AS concedii,
SUM(CASE mo WHEN 'B' THEN 1 ELSE 0 END) AS boala,
SUM(CASE mo WHEN 'O' THEN 1 ELSE 0 END) AS obligatii
FROM yourtable
GROUP BY marca
ORDER BY marca;
HTH.
Servus
Manfred
Amazing! Brilliant solution! You make me very happy! Thanks! > wrote: > >This is the contents of the table: > >marca mo > >1 C > >2 C > >[...] > > > >I need a SQL interogation with following results: > >marca concedii boala obligatii > > 1 2 1 0 > > 2 1 0 0 > > 4 0 1 0 > > 5 0 0 1 > > SELECT marca, > SUM(CASE mo WHEN 'C' THEN 1 ELSE 0 END) AS concedii, > SUM(CASE mo WHEN 'B' THEN 1 ELSE 0 END) AS boala, > SUM(CASE mo WHEN 'O' THEN 1 ELSE 0 END) AS obligatii > FROM yourtable > GROUP BY marca > ORDER BY marca; > > HTH. > Servus > Manfred >
Thanks all. I should add a few things to this question:
The queries would always be SELECT queries. Users would be doing the query
from apps like NCR QueryMan or Crystal Reports. While I could connect to
my box via the command line and kill the PID, I hoped there was a way to
utilize the quit query capabilities both of these apps provide.
Patrick Hatcher
Macys.Com
Legacy Integration Developer
|--------+--------------------------------->
| | Tom Lane |
| | <tgl@sss.pgh.pa.us> |
| | Sent by: |
| | pgsql-novice-owner@post|
| | gresql.org |
| | |
| | |
| | 11/11/2002 04:58 PM |
|--------+--------------------------------->
>------------------------------------------------------------------------------------------------------------|
| |
| To: josh@agliodbs.com |
| cc: "Patrick Hatcher" <PHatcher@macys.com>, pgsql-novice@postgresql.org |
| Subject: Re: [NOVICE] Cancelling long running query? |
>------------------------------------------------------------------------------------------------------------|
Josh Berkus <josh@agliodbs.com> writes:
>> How can I cancel a long running query besides quitting the app?
> Methods:
> 1) Shutdown and restart the database server with pg_ctl -m fast restart.
Better is just to send SIGINT to the individual backend running the
query. This is equivalent to the client having made a QueryCancel
request.
> 2) Kill the client connection proccess on the server side. This is
dangerous
> if the long-running query is a data-modification query.
No more dangerous than any other forced database crash ... which is what
you will get if you kill a backend process ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org