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
