Обсуждение: Re: Idle in transaction

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?