"Idle in Transaction" and hung connections - Mailing list pgsql-general

From Gregory S. Williamson
Subject "Idle in Transaction" and hung connections
Date
Msg-id 71E37EF6B7DCC1499CEA0316A256832801057916@loki.wc.globexplorer.net
Whole thread Raw
Responses Re: "Idle in Transaction" and hung connections
List pgsql-general
Dear peoples,

Periodically we are getting runaway postgres processes on our Linux (2.4.21-0.13 on Dell servers), using 7.4 and GIS
(0.8USE_GEOS=1 USE_PROJ=1 USE_STATS=1). 

All of the queries come in from remote servers using JDBC/proxool; once every 4 hours we have a process on the client
sidethat cleans out old connections. 

All the processes are doing is single queries -- no inserts or updates.

Very occasionally we will see a thread go wild, taking up a huge amount of processor time (the load will climb by "1"
foreach process -- usual load is around .2, when these hit the load rises to 1.x all the way up to a load of about 40
once).The pg_stat_activity shows these conections as being old -- much older than any live thread. All such connections
arein a state of "IDLE IN TRANSACTION" which seems odd as these are all queries and presumably each query is a complete
transaction.My tenative theory is that something is killing the client while the server side still thinks it has data
tosend, or some such variant. The client machines don't have a corresponding connection to the one on the postgres
server.

Killing the runaways with a -15 seems to bring the load back down and all is well, until it happens again.

Does anyone have any ideas what might be triggering this ? It is mostly an annoyance but on a couple of occasions seems
tohave brought down a server, or at least rendered it non-functional. 

Thanks for any advice !

Greg Williamson
DBA
GlobeXplorer LLC

pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: Timestamp problems...wrong weeks.
Next
From: Andrew Rawnsley
Date:
Subject: Re: postgresql idle