CODEWITHSUNDEEP
postgresql
Brad Parks
Brad Parks

Reputation: 71961

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

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;

Upvotes: 1

Views: 3132

Answers (1)

Brad Parks
Brad Parks

Reputation: 71961

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 the following to cancel any of my target query that had 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)

Upvotes: 3

Related Questions