Re: [JDBC] Idle in transaction state.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi folks,

I've run into an issue that I'm sure there's a fix for, I just haven't
quite figured it out on my own.

First the overview of the environment:
Linux (CentOS release 4.2)
Tomcat 5.x application server
Struts/Tiles MVC
Hibernate 3.1 Object Persistence
postgresql-8.2dev-500.jdbc2.jar for JDBC drivers

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"

in the process table. What's worse, is if I try to modify my database
schema while these processes are running (and they seem to stick around
forever) the psql session running the SQL script to do the modifications
hangs on the first statement that modifies or drops an existing table.

I'm not even sure what part of this puzzle is the root cause of the
problem. JDBC drivers? Hibernate configuration? Elvis?

Any insight is welcome and appreciated.

- --
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Peter L. Berghold Peter(at)Berghold(dot)Net
"Those who fail to learn from history are condemned to repeat it."
AIM: redcowdawg Yahoo IM: blue_cowdawg ICQ: 11455958
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Red Hat - http://enigmail.mozdev.org

iD8DBQFFAwRuUM9/01RIhaARAvouAJ9S5BT0aDd4MR2Ps0XXUOCVCU5/vACgtU2E
+drcu3MQ1wVPWq7FnanBOa0=
=xqRU
-----END PGP SIGNATURE-----

Peter,

On 9-Sep-06, at 2:14 PM, Peter L. Berghold wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi folks,
>
> I've run into an issue that I'm sure there's a fix for, I just haven't
> quite figured it out on my own.
>
> First the overview of the environment:
> Linux (CentOS release 4.2)
> Tomcat 5.x application server
> Struts/Tiles MVC
> Hibernate 3.1 Object Persistence
> postgresql-8.2dev-500.jdbc2.jar for JDBC drivers
>
> 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"

I presume you mean "idle in transaction"
>
> in the process table. What's worse, is if I try to modify my database
> schema while these processes are running (and they seem to stick
> around
> forever) the psql session running the SQL script to do the
> modifications
> hangs on the first statement that modifies or drops an existing table.
>
> I'm not even sure what part of this puzzle is the root cause of the
> problem. JDBC drivers? Hibernate configuration? Elvis?
It's the pooling implementation, it should be setting autocommit to
true when closing the connection, which really means returning the
connection to the pool.
>
> Any insight is welcome and appreciated.
>
Dave
>
> - --
> ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
> ::
> Peter L. Berghold
> Peter(at)Berghold(dot)Net
> "Those who fail to learn from history are condemned to repeat it."
> AIM: redcowdawg Yahoo IM: blue_cowdawg ICQ:
> 11455958
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.6 (GNU/Linux)
> Comment: Using GnuPG with Red Hat - http://enigmail.mozdev.org
>
> iD8DBQFFAwRuUM9/01RIhaARAvouAJ9S5BT0aDd4MR2Ps0XXUOCVCU5/vACgtU2E
> +drcu3MQ1wVPWq7FnanBOa0=
> =xqRU
> -----END PGP SIGNATURE-----
> <Peter.vcf>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

"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.

regards, tom lane

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

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
>>> 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
>