'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?

postgresql


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

To terminate all running queries:

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

from here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 evgenek
Solution 2 josh p
Solution 3 rogerdpack

Related Questions