Re: idle in transaction query makes server unresponsive

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