Scot Kreienkamp wrote: > 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. Yes, see if upgrading to 9.1 makes the problem disappear. It is surprising that you cannot even start new connections. You could try to "strace" the postmaster during a connection attempt and see what happens. Maybe that helps to spot the place where things go wrong. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
