DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=lWkCE+87CNGSPNiU89622F7Db+bEwD9Fp9XqOt0Ywh1coWh6EGYEoW8qVy7ZcQAuTnoSlGjtGc4Cf3HSyUHR3akfHqVdkwmYNg6J9RCryjwmf1kZn0iGmMMTiVxLuUcteL2N+BRUpin6aB4CeAJDPiCvhuQPNIsMIg21rB1WA/4=
Message-ID: <6d8daee30605021643k4415462oec2ea8487fc521b7@mail.gmail.com>
Date: Tue, 2 May 2006 16:43:52 -0700
From: "Tony Wasson" <ajwasson@gmail.com>
To: "Dan Harris" <fbsd@drivefaster.net>
Subject: Re: Killing long-running queries
Cc: "PostgreSQL Performance" <pgsql-performance@postgresql.org>
In-Reply-To: <4457E918.10007@drivefaster.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4457E918.10007@drivefaster.net>

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 =3D 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