Re: "Idle in Transaction" revisited.

[snip]
> I'm amazed...   Sounds like noone is doing any serious work with JDBC, or 
> only has short lived 'applet' kind of applications where long persistance 
> connections aren't an issue.

In our experience, long running transactions are a PITA for all databases, even if they seam to work fine.
It is always possible to design the work flow so that the transactions are short lived:
 - don't ever open a transaction and then wait for user input;
 - use cooperative locking instead of data base locking for long lived locks;
 - chunk transactions when processing huge amounts of data;

Regarding cooperative locking and the fact that after a crash the lock
stays: I'm not sure how your application is designed, but in ours after
a crash we definitely want it to stay there, so we can manually check
the consistency of the data before releasing the lock for further
processing. I have bad experience with automatic recovery after crashes.

Just my 2c,
Csaba.


> I can think of a bunch of scenarios where this would cause problems...
> 
> *  An service app that opens several connections for various threads,
>    and one or another of them goes idle for a long time ... No vacuuming
>    during this state.
> 
> *  Thread has been idle for awhile, then goes to do a SELECT and it will
>    only see data that was committed prior to this threads last COMMIT.
> 
> etc etc etc. 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org