Re: Killing long-running queries

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