On 10-Sep-06, at 12:12 AM, Jack Orenstein wrote:
> Tom Lane wrote:
>> "Peter L. Berghold" <Peter(at)berghold(dot)net> writes:
>>> What I'm seeing is the first time my web application is being run
>>> 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
>> 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
>> 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
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.
> Jack Orenstein
> ---------------------------(end of
> TIP 3: Have you checked our extensive FAQ?
In response to
pgsql-jdbc by date
|Next:||From: jbras||Date: 2006-09-11 09:21:59|
|Subject: Re: postgresql / openoffice|
|Previous:||From: Jack Orenstein||Date: 2006-09-10 04:12:37|
|Subject: Re: Idle in transaction state.|
pgsql-general by date
|Next:||From: mj||Date: 2006-09-10 13:51:10|
|Subject: Rules / Triggers and Return Row Count|
|Previous:||From: Dino Vliet||Date: 2006-09-10 11:46:46|
|Subject: execute in pl/pgsql|