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