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