Today i find out one of the query is hanging in PostgreSQL, it can’t release itself, it just seem like stuck in PostgreSQL forever.
I issue a command to cancel it in PostgreSQL, but it seem not working. Query just hanging there and show an “idle in transaction” status. I have no way to terminal it, it left me no choice but go Debian terminal to issue kill command to terminate it manually.
Query hanging or not responding in PostgreSQL is because i didn’t handle transaction manager properly in my web application. When System accidently shut down, running query will hanging in PostgreSQL and my transaction manager (DataSourceTransactionManager) is not manage to rollback whole transaction. I have no idea why it’s not working. After i changed my transaction manager to JtaTransactionManager, it work correctly and no transaction hanging in middle way when system accidentally shut down.
However here i show how to terminal the hanging query in PostgreSQL. The hanging query will display as “idle in transaction” in PostgreSQL. I have to list out all PostgreSQL processes and issue a kill terminate command to terminate the query manually.
1) Issue ps -ef | grep postgres to list out all processes belong to postgres user.
mkyong:~# ps -ef | grep postgres postgres 13648 1 0 11:04 ? 00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx postgres 13651 13648 0 11:04 ? 00:00:00 postgres: logger process postgres 13653 13648 0 11:04 ? 00:00:00 postgres: writer process postgres 13654 13648 0 11:04 ? 00:00:00 postgres: wal writer process postgres 13655 13648 0 11:04 ? 00:00:00 postgres: autovacuum launcher process postgres 13656 13648 0 11:04 ? 00:00:00 postgres: stats collector process postgres 13668 13648 0 11:04 ? 00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle postgres 13689 13648 0 11:05 ? 00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle postgres 13714 13648 0 11:06 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57586) idle in transaction postgres 13721 13648 0 11:06 ? 00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle postgres 13832 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle postgres 13833 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle postgres 13834 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle postgres 13835 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle postgres 13836 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle postgres 14201 13648 0 11:23 ? 00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle postgres 14202 13648 0 11:23 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle postgres 14207 13648 0 11:24 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle root 18030 17992 0 13:46 pts/0 00:00:00 grep postgres mkyong:~#
2) Notice process id 13714, idle in transaction, this is the hanging query in PostgreSQL. Issue following command to terminate the PostgreSQL process manually.
mkyong:~# kill 13714or
mkyong:~# kill -TERM 13714or
mkyong:~# kill -15 137143) Done, hanging query is gone
mkyong:~# ps -ef | grep postgres postgres 13648 1 0 11:04 ? 00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx postgres 13651 13648 0 11:04 ? 00:00:00 postgres: logger process postgres 13653 13648 0 11:04 ? 00:00:00 postgres: writer process postgres 13654 13648 0 11:04 ? 00:00:00 postgres: wal writer process postgres 13655 13648 0 11:04 ? 00:00:00 postgres: autovacuum launcher process postgres 13656 13648 0 11:04 ? 00:00:00 postgres: stats collector process postgres 13668 13648 0 11:04 ? 00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle postgres 13689 13648 0 11:05 ? 00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle postgres 13721 13648 0 11:06 ? 00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle postgres 13832 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle postgres 13833 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle postgres 13834 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle postgres 13835 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle postgres 13836 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle postgres 14201 13648 0 11:23 ? 00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle postgres 14202 13648 0 11:23 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle postgres 14207 13648 0 11:24 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle root 18030 17992 0 13:46 pts/0 00:00:00 grep postgres mkyong:~#
Share It
