Re: Killing long-running queries

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-general/2006-02/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-general/2006-02/msg00427.php