Re: [GENERAL] Idle in transaction state.

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
>