Re: Transaction-scope advisory locks

On Tuesday 14 December 2010 15:19:32 Merlin Moncure wrote:
> On Tue, Dec 14, 2010 at 7:07 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On Tuesday 14 December 2010 00:14:22 Marko Tiikkaja wrote:
> >> The lock space is the same though, but I don't feel strongly about it.
> >
> > I feel strongly that it needs the same locking space. I pretty frequently
> > have the need for multiple clients trying to acquiring a lock in
> > transaction scope (i.e. for accessing the cache) and one/few acquiring
> > it in session scope (for building the cache).
>
> Not that I'm necessarily against the proposal, but what does this do
> that can't already be done by locking a table or a table's row?
1. trylock without raising errors (the other possibility is nowait, but that
doesnt work very well as it ERRORs).

2. mixing session and transaction scope (I would like to have that e.g. for
materialized views. The writers uses session scope and the readers use
transaction scope. Its not that easy to make code ERROR/exception safe when
you only control some view or such. In contrast the computationally expensive
part of computing the materialized view should be way much more easy to do
sensibly in session scope).

3. nonlocking dequeuing of a table-based queue can e.g. be done with advisory
locks but not with row level locks.

Andres