Re: Connection Idle in transaction

Yes, this sucks - I've run into this as well.  This doesn't happen when
autocommit is set to true.

In my application, connections come from a connection pool that I
control, so I set autocommit to true when adding or returning
connections to the pool, and set it to false when retrieving connections
from the pool.  The connections that are sitting idle in the pool aren't
actually "idle in transaction".

The real problem (for us, at least) was that connections that are idle
in transaction effectively hang on to old transaction IDs, so a
connection that's not used for several days can basically block vacuums
from vacuuming old rows.  Once we changed our pool to have the above
behavior, these problems went away.

mike

-----Original Message-----
From: Gaetano Mendola [mailto:mendola@bigfoot.com]
Sent: Thursday, April 08, 2004 3:21 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Connection Idle in transaction


Hi all,
I'm facing a problem with the unfamous:

"idle in transaction"

problem. I'm using the JDBC driver.

Mainly the problem is that the JDBC interface doesn't
provide the method begin() for a transaction object,
of course this is not a JDBC postgres interface problem.

Let me explain what happen using the JDBC interface



Client  Side            |    Server Side
---------------------------------------------------

1) Open a connection    |    Connection accepted
                         |                       <- Connection Idle
2) set autocommit false |    begin;
                         |                       <- Idle in transaction
3) select now();        |    select now();
                         |                       <- Idle in transaction
4) commit;              |    commit; begin;
                         |                       <- Idle in transaction
5) select now();        |    select now();
                         |                       <- Idle in transaction
6) rollback;            |    rollback; begin;
                         |                       <- Idle in transaction


as you can easily understand there is no window time larger enough with
a connection  idle, I thin that the JDBC behaviour ( with the server I
mean ) is not really correct: if the application is waiting for a user
entry then the connection remain: idle in transaction.

This is the behaviour that I think it's better:


Client  Side            |    Server Side
---------------------------------------------------

1) Open a connection    |    Connection accepted
                         |                       <- Connection Idle
2) set autocommit false |    NOP
                         |                       <- Connection Idle
3) select now();        |    begin; select now();
                         |                       <- Idle in transaction
4) commit;              |    commit;
                         |                       <- Connection Idle
5) select now();        |    begin; select now();
                         |                       <- Idle in transaction
6) select now();        |    select now();
                         |                       <- Idle in transaction
7) rollback;            |    rollback;
                         |                       <- Connection Idle


AS you can see the JDBC driver must do a begin only before the first
statement.

Am I missing something ?





Regards
Gaetano Mendola




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)