From: | Jack Orenstein <jao(at)geophile(dot)com> |
---|---|
To: | General <pgsql-general(at)postgresql(dot)org>, pgsql-jdbc(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter(at)berghold(dot)net |
Subject: | Re: Idle in transaction state. |
Date: | 2006-09-10 04:12:37 |
Message-ID: | 450390B5.1000801@geophile.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-jdbc |
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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Hyde | 2006-09-10 04:39:02 | Re: Data Warehouse |
Previous Message | Jack Orenstein | 2006-09-10 02:45:25 | Re: How to use SPI_saveplan |
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2006-09-10 12:21:02 | Re: [JDBC] Idle in transaction state. |
Previous Message | Tom Lane | 2006-09-10 00:49:34 | Re: Idle in transaction state. |