Terminate hung query (idle in transaction)

I'm using Heroku with the Crane Postgres option and I was running a query on the database from my local machine when my local machine crashed. If I run

select * from pg_stat_activity

one of the entries has

<IDLE> in transaction

in the current_query_text column.

As a result, I can't drop the table that was being written to by the query that was terminated. I have tried using pg_cancel_backend(N) and it returns True but nothing seems to happen.

How can I terminate this process so that I can drop the table?

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. If required, the following is more of a "hard terminate" command which could cause it to cancel more quickly:

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

To terminate all running queries:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active'; 

from here