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 PostgreSQL answer, and not specific to Heroku
The simple-stupid answer to this question may be ... just restart postgresql!
Here is another way of quickly killing all long-lasting "idle in transaction":
SELECT pg_terminate_backend(pid) from pg_stat_activity
WHERE state in ('<IDLE> in transaction', 'idle in transaction')
AND now()-xact_start>interval '1 minute';
Find the PID by running this sql*):
SELECT now()-xact_start as trans_time, pid, query from pg_stat_activity
WHERE state != 'idle' ORDER BY xact_start;
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. Use select * to get more information about the queries. 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 - so to properly fix this issue, it's needed to ensure the application always does a commit or a rollback after running queries, even read-only queries (it's also possible to enable auto-commit).
Long-lasting "idle in transaction" should be avoided, as it may (dependent on your usage pattern) cause major performance problems.
Footnotes: The query may need mending on very old or future versions of PostgreSQL, and on very old versions of PostgreSQL only superuser can cancel queries.