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(at)postgresql(dot)org