Re: idle in transaction query makes server unresponsive

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Albe Laurenz
> Sent: Wednesday, September 26, 2012 5:15 AM
> To: Scot Kreienkamp; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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

[Scot Kreienkamp]
I'm willing to give it a try, but I've never done it before.  What do I need to do?



This message is intended only for the individual or entity to which it is addressed. It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws. If you are not the intended recipient,
pleasenote that you are strictly prohibited from disseminating or distributing this information (other than to the
intendedrecipient) or copying this information. If you have received this communication in error, please notify us
immediatelyby e-mail or by telephone at the above number. Thank you.