Thread: Re: Idle in transaction
Sorry. Forgot to mention the postgres version
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
From: Sharma, Sid
Sent: Friday, July 17, 2009 10:05 AM
To: pgsql-general@postgresql.org
Subject: Idle in transaction
Hi
I’m a postgres newbie. I just implemented a new web application using postgres.
When I look at the db connections (via ps), I notice that all existing connections are in ‘Idle in Transaction’ state.
They never go to idle state.
The application itself remains functional and responsive. It has been up for over 36 hours now without any issues.
What is the significance of this state? Does this imply a transaction leak? Then why am I not noticing deadlocks, timeouts etc.
Thanks
Sid
On Friday 17 July 2009 07:17:15 am Sharma, Sid wrote: > Sorry. Forgot to mention the postgres version > > PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 > > > > ________________________________ > > From: Sharma, Sid > Sent: Friday, July 17, 2009 10:05 AM > To: pgsql-general@postgresql.org > Subject: Idle in transaction > > > > Hi > > I'm a postgres newbie. I just implemented a new web application using > postgres. > > When I look at the db connections (via ps), I notice that all existing > connections are in 'Idle in Transaction' state. > > They never go to idle state. > > The application itself remains functional and responsive. It has been up > for over 36 hours now without any issues. > > What is the significance of this state? Does this imply a transaction > leak? Then why am I not noticing deadlocks, timeouts etc. > > Thanks > > Sid In my case it was caused by a select statement that I neither issued a commit or rollback. So you have started a transaction without closing it somewhere in your app. My app continued to work without apparent issue. The only thing I noticed was pgAdmin3 could not make changes to the data structure without closing my app. So somewhere in your code you have started a transaction without closing it. Johnf
Sharma, Sid wrote: > Sorry. Forgot to mention the postgres version > > PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Why on earth are you using that version? At the very least upgrade to 8.1.17 and if this is a new app, why not use 8.4? > When I look at the db connections (via ps), I notice that all existing > connections are in 'Idle in Transaction' state. > > They never go to idle state. You don't say how you are connecting to the DB, but it's probably your connection manager. Are you running some sort of connection pooling perhaps? -- Richard Huxton Archonet Ltd
On Fri, Jul 17, 2009 at 8:17 AM, Sharma, Sid<ssharma@bjs.com> wrote: > Sorry. Forgot to mention the postgres version > > PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Oh, and update to the latest 8.1.x version. There are some nasty bugs in 8.1.3 if I recall. Won't fix this issue, but why run old un-updated pg code?