Heroku Postgres - terminate hung query (idle in transaction)
This is a general Postgres answer, and not specific to heroku
(The simple-stupid answer to this question may be ... just restart postgresql. Assuming that's not desirable or not an option ...)
Find the PID by running this sql:
SELECT pid , query, * from pg_stat_activity
WHERE state != 'idle' ORDER BY xact_start;
(The query may need mending dependent of the version of postgres - eventually, just select * from pg_stat_activity). You'll find the pid in the first (left) column, and the first (top) row is likely to be the query you'd like to terminate. I'll assume the pid is 1234 below.
You may cancel a query through SQL (i.e. without shell access) as long as it's yours or you have super user access:
select pg_cancel_backend(1234);
That's a "friendly" request to cancel the 1234-query, and with some luck it will disappear after a while. Eventually, this is more efficient:
select pg_terminate_backend(1234);
If you have shell access and root or postgres permissions you can also do it from the shell. To "cancel" one can do:
kill -INT 1234
and to "terminate", simply:
kill 1234
DO NOT:
kill -9 1234
... that will often result in the the whole postgres server going down in flames, then you may as well restart postgres. Postgres is pretty robust, so the data won't be corrupted, but I'd recommend against using "kill -9" in any case :-)
A long-lasting "idle in transaction" often means that the transaction was not terminated with a "commit" or a "rollback", meaning that the application is buggy or not properly designed to work with transactional databases. Long-lasting "idle in transaction" should be avoided, as it also can cause major performance problems.
Try this:
select pg_terminate_backend(pid int)
More about this you can find here. This should be 'cleaner' solution of this problem than killing process by system.
You can install the heroku-pg-extras add-on and run the following command to get the PID:
heroku pg:locks --app <your-app>
Then just do:
heroku pg:kill <pid> --app <your-app>
NOTE: --force option can be used to issue pg_terminate_backend which drops the entire connection for that query.
If heroku pg:locks does not list anything, try heroku pg:ps.
For more information check out:
https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall