Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Terminate hung query (idle in transaction)

Tags:

postgresql

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?

like image 553
alan Avatar asked Jul 02 '12 10:07

alan


People also ask

How do you kill a transaction in PostgreSQL?

We need to utilize two functions within the SELECT query on the query area to kill a process. The first one is pg_cancel_backend() function and second is pg_terminate_backend() function. The pg_cancel_backend() function is used to simply cancel out the query of a database using the process ID for a particular query.

How do you stop a query in pgAdmin?

Go to Dashboard in your pgAdmin. At the bottom, in the Server Activity section, under the Sessions Tab, you can see all the Active queries. Now, notice the cross button and the Stop button to the left of each query.

How do I kill a blocked session in PostgreSQL?

You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function.


1 Answers

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.

like image 143
tobixen Avatar answered Sep 22 '22 18:09

tobixen