Terminate hung query (idle in transaction)

Question

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?

Answer 1

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.