Hi everyone, I have a problem that I've been struggling with for quite some time. Every once in a while I will get a connection that goes to idle in transaction on an in-house programmed application that connects with JDBC. That happens fairly regularly and the programmers are trying to clean that up, but sometimes the idle in transaction connection makes the PG server entirely unresponsive. I'm not getting connection refused, nothing. All connections existing or new, JDBC or psql, just hang. I've already got full query logging on to try to catch the problem query or connection so I can give the developers somewhere to look to resolve their issue with the application, but since queries are logged with runtimes I'm assuming they are only logged after they are complete. And since it's idle in transaction it never completes so it never gets logged. Our application is connecting as an unprivileged user named rmstomcat, and the database is limited to 400 connections out of 512. I'm not running out of connections as I've got reserved connections set, and even connecting as user postgres with psql the connection just hangs. The server doesn't appear to be running out of memory when this happens and nothing is printed in the log. The only thing that resolves it is doing a kill on the PID of any idle in transaction connections existing at the time causing them to roll back. Then everything else picks up right where it left off and works again.
Can anyone give me any hints about why PG becomes unresponsive? Or how to fix it so it doesn't? My server is 9.1.2 right now. I will be upgrading to the latest 9.1 series soon, but until 9.2 can be run through our development/testing cycle I can't upgrade to 9.2. That will take about 6-10 months. Thanks! Scot Kreienkamp This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.
