Skip site navigation (1) Skip section navigation (2)

"Idle in Transaction" and hung connections

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: "Idle in Transaction" and hung connections
Date: 2004-04-29 19:53:40
Message-ID: 71E37EF6B7DCC1499CEA0316A256832801057916@loki.wc.globexplorer.net (view raw or flat)
Thread:
Lists: 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.8 USE_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 side that 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" for each 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 are in 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 to send, 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 to have brought down a server, or at least rendered it non-functional.

Thanks for any advice !

Greg Williamson
DBA
GlobeXplorer LLC

Responses

pgsql-general by date

Next:From: Andrew RawnsleyDate: 2004-04-29 19:57:59
Subject: Re: postgresql idle
Previous:From: Andrew SullivanDate: 2004-04-29 19:41:32
Subject: Re: Timestamp problems...wrong weeks.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group