postgres - how to kill all long running queries that match a particular query?

Question

I have lots of background jobs that queue up the same query.

I've been having a situation where they get stuck, and I'd like to kill the really long running queries in some simple way, that won't take down the entire DB.

How can I do this using a query?

Let's assume my query is the following, and I want to kill any that have been running for over 60 minutes

select * from some_big_table;

Answer 1

This is the best I could come up with. Note I'm running on a mac

First I generated an MD5 of my query, at the command line. I did this so it'd simplify what the query looks like, to ensure I only matched my target query and to avoid "sql injecting" myself if I mistyped my query.

# on linux, use 'md5sum' instead.
$ echo -n 'select * from some_big_table;' | md5
65007f37ff78f1e66645105412430b7c

Then I just used a subselect to filter out any other queries that hadn't been running for > 60 minutes.

SELECT pg_cancel_backend(pid)
FROM   pg_stat_activity
WHERE  
       now() - pg_stat_activity.query_start >= interval '60 minutes' AND
       md5(query) = '65007f37ff78f1e66645105412430b7c' AND    
       state = 'active';

which seemed to work fine

 pg_cancel_backend
-------------------
 t
 t
(2 rows)