Thread: Re: [GENERAL] Idle in transaction state.

Re: [GENERAL] Idle in transaction state.

From
Jack Orenstein
Date:
Tom Lane wrote:
> "Peter L. Berghold" <Peter@berghold.net> writes:
>> What I'm seeing is the first time my web application is being run there
>> is a bunch of processes running around that look like:
>> "postgres: peter peter_trialdb 127.0.0.1(46222) idle"
>
> "idle" is fine, "idle in transaction" is not so fine, because those
> might be holding locks that block things like schema changes.  You need
> to figure out why your client-side code isn't closing out its
> transactions promptly.
>
> You'd probably be better off asking on the pgsql-jdbc list about this,
> as the folks likely to know about Java-stack issues hang out there.

I haven't seen the discussion show up on the JDBC list so I'll post
to both lists.

I noticed the same problem in my JDBC/postgresql application. My
application does Connection.setAutoCommit(false) for every connection;
connections were kept in a home-grown connection pool.

I began to suspect that turning off auto-commit was resulting in "idle
in transaction" processes, and also causing VACUUM to reclaim fewer
tuple versions than I thought it should be reclaiming. This was true
even though my application always either commits or aborts before
returning the connection to the pool.

Now what I do is setAutoCommit(true) after the commit or abort, and
before returning the connection to the pool. Then, on taking a
connection from the pool, I setAutoCommit(false). This seems to have
solved both problems. I don't really understand why this works,
however. I would expect the commit or abort to suffice.

(I can post a test program demonstrating the problem if there is
interest.)

Jack Orenstein



Re: [GENERAL] Idle in transaction state.

From
Dave Cramer
Date:
On 10-Sep-06, at 12:12 AM, Jack Orenstein wrote:

> Tom Lane wrote:
>> "Peter L. Berghold" <Peter@berghold.net> writes:
>>> What I'm seeing is the first time my web application is being run
>>> there
>>> is a bunch of processes running around that look like:
>>> "postgres: peter peter_trialdb 127.0.0.1(46222) idle"
>> "idle" is fine, "idle in transaction" is not so fine, because those
>> might be holding locks that block things like schema changes.  You
>> need
>> to figure out why your client-side code isn't closing out its
>> transactions promptly.
>> You'd probably be better off asking on the pgsql-jdbc list about
>> this,
>> as the folks likely to know about Java-stack issues hang out there.
>
> I haven't seen the discussion show up on the JDBC list so I'll post
> to both lists.
>
> I noticed the same problem in my JDBC/postgresql application. My
> application does Connection.setAutoCommit(false) for every connection;
> connections were kept in a home-grown connection pool.
>
> I began to suspect that turning off auto-commit was resulting in "idle
> in transaction" processes, and also causing VACUUM to reclaim fewer
> tuple versions than I thought it should be reclaiming. This was true
> even though my application always either commits or aborts before
> returning the connection to the pool.
>
> Now what I do is setAutoCommit(true) after the commit or abort, and
> before returning the connection to the pool. Then, on taking a
> connection from the pool, I setAutoCommit(false). This seems to have
> solved both problems. I don't really understand why this works,
> however. I would expect the commit or abort to suffice.
>
> (I can post a test program demonstrating the problem if there is
> interest.)

Pools should set autocommit to true upon close(). Since pools wrap
close() the driver has no way to deal with being put back into a pool
and left idling in a transaction.

Dave
>
> Jack Orenstein
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>