On 5/2/06, Dan Harris <fbsd@xxxxxxxxxxxxxxx> 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
