Re: Transaction settings: nowait

On Thu, May 28, 2009 at 9:52 PM, Yasser Idris <luckyasser(at)gmail(dot)com> wrote:
>
> Dude, all what u wrote make sense. Only your missing one thing, consider the
> following scenario that u already reply to:
>
>>> For. example: I forget to make commit, or rollback on exception then all
>>> resources I used (updated) is locked.
>
>>Yes - that's an application bug.
>
> Even if it's application bug, resources shouldn't be locked indefinitely.

But some people run very long running transactions on purpose. Should
we break their applications to make you happy?

> What if there are other clients that access this db? Forget abt this, what
> if the same problem arises because of a network failure during a transaction
> that issued some locks?? What's the solution then, should the resources be
> locked forever till someone finds out and kills the process manually?

No, if the network connection is lost, then tcp_keepalive_timeout will
kick in and the process will eventually get killed off.

> Although the odds of this scenario might look unlikely to happen, the
> results are extremely inconvenient. That's why those features (lock timeout,
> transaction time out) are there in every other dbms. It's not abt that u
> don't want ur client to wait much before aborting a statement, it's more of
> telling ur stupid server to abort a transaction or release a lock in case of
> non-graceful abortion (without commit, rollback, or release lock) from the
> client side whether it was a programmers fault or any other reason

I agree that those would be useful settings / features to have. But
there are other ways of handling this situation. One of the most
common is to use a firewall between your db and the other servers that
times out idle connections, which will result in pg seeing the lost
connection after the tcp keepalive timeout and killing the session.
It's not perfect, but it does work.