Re: [GENERAL] Idle in transaction state.

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