Re: sequence locking

On Wed, Sep 21, 2011 at 12:03 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner
>> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>> Andres Freund <andres(at)anarazel(dot)de> wrote:
>>>
>>>> - Its impossible to emulate proper locking yourself because
>>>> locking is not allowed for sequences
>>>
>>>> Any arguments against allowing it again? It seems to have been
>>>> allowed in prehistoric times.
>>>
>>> It would be nice to allow it.  I've had to create a dummy table
>>> just to use for locking a sequence (by convention).
>>
>> another (better?) way is advisory locks...
>
> Not under 9.0 or earlier if you want the lock to last until the end
> of the transaction.  Also, the fact that advisory locks are only on
> numbers, without any mechanism for mapping those to character
> strings, makes them poorly suited to many tasks.

hm, this was one of the things I used advisory locks for -- in fact,
not having to hold the lock for the duration of the transaction was
useful for cases of sequence locking.

Basically, you steal the sequence oid for a lock id and wrap nextval()
with an advisory sharelock. Then, if you need to do some type of
heavy duty operation, like reserve a contiguous block of identifiers,
you can full lock the same lock and block everyone.

If the locks were full transaction locks, that would essentially
serialize all transactions that sharelocked the sequence...no bueno.
So, considering all that, what are the actual use cases for proper
locking of sequence locks (outside of the metadata stuff you can lock
now)?

merlin