How to kill the idle connection in Postgresql.?

Question

I am using java servlets along with pgadmin 9.1.Problem is the connection in servlets was not closed properly so it leads to blank screen if it reaches the maximum connection.I don't want every user to extend maximum connections in pgadmin...i have used the below query in starting and end ending point of servlet but its showing error like..connection terminated due to administrator command..

ResultSet rs_1q=st_Query3.executeQuery("SELECT pg_terminate_backend(pg_stat_activity.procpid)FROM pg_stat_activity WHERE pg_stat_activity.current_query = '<IDLE>' AND procpid <> pg_backend_pid();");

Answer 1

Generally, as @Rahul points out, its not advisable to kill connections. But if it's your last resort, this is how to terminate idle connections:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'databasename'
AND pid <> pg_backend_pid()
AND state in ('idle');

Answer 2

I dont think that killing a connection is an ideal solution as there may be certain connection which might be genuinely waiting for the transaction to get completed. Instead I would suggest you to set a timeout for your transacion.

However if you are desperate to kill the idle connections then you can try like this:

Use shell script and do "ps auxwww|grep 'idle in transaction'" which will return list of all "idle in transaction." processes. Then you can use "awk" and find of each line of output to get the the process id, and finally you can use a "kill <pid>" to each process.