Re: Connection Idle in transaction

David Wall wrote:
>>It's not a lock issue as I understand it. It's that the presence of an
>>open "old" transaction means that for rows that have been updated since
>>that transaction started, VACUUM cannot delete the "old" version of the
>
> row.
>
>
> Why would the "old" transaction have anything locked up if it hadn't done a
> select, update, insert or delete?

Empirically, an open transaction that has run no queries *does* prevent
some rows from being vacuumed. I haven't delved into the backend code to
see why exactly; asking on -hackers may be a better bet.

> I'm not sure what state information is maintained in the JDBC library, but
> assuming it can maintain state, it shouldn't be hard for it to know whether
> it's in autocommit mode or not, and if not, then only issue the 'begin' when
> a statement is first created on the connection, and then reset that state
> after a commit/rollback.

That's the plan; it just needs implementing. There are also some
interactions with changing transaction isolation levels that should be
dealt with at the same time.

This is somewhere on my todo list and I'll get to it eventually if noone
else does, but there's quite a bit of other stuff ahead of it at the
moment..

-O