My database is used primarily in an OLAP-type environment. Sometimes my
users get a little carried away and find some way to slip past the
sanity filters in the applications and end up bogging down the server
with queries that run for hours and hours. And, of course, what users
tend to do is to keep queuing up more queries when they don't see the
first one return instantly

So, I have been searching for a way to kill an individual query. I read
in the mailing list archives that you could 'kill' the pid. I've tried
this a few times and more than once, it has caused the postmaster to
die(!), terminating every query that was in process, even unrelated to
that query.

Is there some way I can just kill a query and not risk breaking
everything else when I do it?

Thanks


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Dan Harris <fbsd@drivefaster.net> writes:
> So, I have been searching for a way to kill an individual query. I read
> in the mailing list archives that you could 'kill' the pid. I've tried
> this a few times and more than once, it has caused the postmaster to
> die(!), terminating every query that was in process, even unrelated to
> that query.

You should be using SIGINT, not SIGTERM.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 5/2/06, Dan Harris <fbsd@drivefaster.net> wrote:
> My database is used primarily in an OLAP-type environment. Sometimes my
> users get a little carried away and find some way to slip past the
> sanity filters in the applications and end up bogging down the server
> with queries that run for hours and hours. And, of course, what users
> tend to do is to keep queuing up more queries when they don't see the
> first one return instantly
>
> So, I have been searching for a way to kill an individual query. I read
> in the mailing list archives that you could 'kill' the pid. I've tried
> this a few times and more than once, it has caused the postmaster to
> die(!), terminating every query that was in process, even unrelated to
> that query.
>
> Is there some way I can just kill a query and not risk breaking
> everything else when I do it?
>
> Thanks
>

Hi Dan,

You can kill a specific pid under 8.1 using SELECT
pg_cancel_backend(pid). You can kill a query from the command line by
doing $ kill -TERM pid or $kill -SIGINT pid.

There are several tips from this thread that may be useful about
killing long running SQL:
http://archives.postgresql.org/pgsql...2/msg00298.php

In short, the recommendations are:
1) Use statement_timeouts if at all possible. You can do this
database wide in postgresql.conf. You can also set this on a per user
or per SQL statement basis.
2) Make step #1 does not kill autovacuum, or necessary automated
jobs. You can do this with "ALTER USER SET statement_timeout = 0".

I'm using a web page to show SELECT * FROM pg_stat_activity output
from several servers. This makes it easy to see the pids of any
long-running SQL.

http://archives.postgresql.org/pgsql...2/msg00427.php

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote
> You should be using SIGINT, not SIGTERM.
>
> regards, tom lane
>

Thank you very much for clarifying this point! It works



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hi,

On Tue, 2006-05-02 at 17:19 -0600, Dan Harris wrote:
> Is there some way I can just kill a query and not risk breaking
> everything else when I do it?

Use pg_stat_activity view to find the pid of the process (pidproc
column) and send the signal to that process. I think you are now killing
postmaster, which is wrong.

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

There is also the statement_timeout setting in postgresql.conf, but
you have to be careful with this setting. I'm not sure about
postgres 8.0 or 8.1, but in 7.4.5 this setting will terminate the
COPY statements used by pg_dumpall for backups. So I actually use
the pg_stat_activity table to kill long running queries or idle in
transactions that are hanging around (very bad for vacuum). For
example, you can do something like this to kill off idle in
transactions that are truly idle for more than 1 hour...

psql -U postgres -A -t -c "select procpid from pg_stat_activity where
current_query ilike '%idle in transaction%' and query_start < now() -
interval '1 hour'" template1 | xargs kill

Just throw that in your crontab to run every few minutes, redirect
standard error to /dev/null, and quit worrying about vacuum not
reclaiming space because some developer's code fails to commit or
rollback a transaction. Just be careful you aren't killing off
processes that are actually doing work.

-- Will Reese http://blog.rezra.com

On May 2, 2006, at 7:01 PM, Devrim GUNDUZ wrote:

> Hi,
>
> On Tue, 2006-05-02 at 17:19 -0600, Dan Harris wrote:
>> Is there some way I can just kill a query and not risk breaking
>> everything else when I do it?
>
> Use pg_stat_activity view to find the pid of the process (pidproc
> column) and send the signal to that process. I think you are now
> killing
> postmaster, which is wrong.
>
> Regards,
> --
> The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq