sequence locking

Hi,

I find the current behaviour of locking of sequences rather problematic.
Multiple things:

- First and foremost I find it highly dangerous that "ALTER SEQUENCE ..." is
for the biggest part not transactional. I think about the only transaction
part is the name, owner and schema.
Sure, its documented, but ...

The cited reasons for wanting that behaviour look a bit bogus to me? Why
should concurrency be important when doing an ALTER SEQUENCE?

- Its impossible to emulate proper locking yourself because locking is not
allowed for sequences

The first one looks rather hard to solve to me with my passing knowledge of
the sequence, but probably worthy of a TODO entry.

The second one looks easier. Any arguments against allowing it again? It seems
to have been allowed in prehistoric times.

Greetings,

Andres

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).

-Kevin

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...

merlin

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.

-Kevin

On Wednesday 21 Sep 2011 19:03:17 Kevin Grittner 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.
The usual trick is to lock on the oid of some database object. But I agree,
its a poor workaround for this specific problem.

Andres

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> 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.

If you think that it used to be allowed, it'd be a good idea to see
if you can find the archived discussions about changing it.

> It would be nice to allow it. I've had to create a dummy table just
> to use for locking a sequence (by convention).

One question is what you think the lock means. I believe for example
that taking a non-exclusive regular table lock on a sequence would not
prevent other sessions from doing nextval(); even an exclusive one would
not prevent them from doing so if they had pre-cached values.

regards, tom lane

On Wednesday 21 Sep 2011 19:24:55 Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> > 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.
>
> If you think that it used to be allowed, it'd be a good idea to see
> if you can find the archived discussions about changing it.
The message I was thinking about was
http://archives.postgresql.org/pgsql-hackers/2001-10/msg00930.php

> > It would be nice to allow it. I've had to create a dummy table just
> > to use for locking a sequence (by convention).
> One question is what you think the lock means. I believe for example
> that taking a non-exclusive regular table lock on a sequence would not
> prevent other sessions from doing nextval(); even an exclusive one would
> not prevent them from doing so if they had pre-cached values.
I don't see what a non-exclusive lock on a sequence should sensibly do so I
don't see a problem with not supporting them.
That already cached values are not affected by the lock seems to be pretty
logical to me - and not really problematic.
At least in my cases I would look at last_value from the sequence after
locking it- which includes the cached values so its fine that they can be used.
The case that somebody already acquired a sequence value that not visible to
other sessions has to be taken into account anyway.

Greetings,

Andres

Andres Freund <andres(at)anarazel(dot)de> wrote:
> On Wednesday 21 Sep 2011 19:24:55 Tom Lane wrote:

>> One question is what you think the lock means. I believe for
>> example that taking a non-exclusive regular table lock on a
>> sequence would not prevent other sessions from doing nextval();
>> even an exclusive one would not prevent them from doing so if
>> they had pre-cached values.
> I don't see what a non-exclusive lock on a sequence should
> sensibly do so I don't see a problem with not supporting them.
> That already cached values are not affected by the lock seems to
> be pretty logical to me - and not really problematic.
> At least in my cases I would look at last_value from the sequence
> after locking it- which includes the cached values so its fine
> that they can be used.
> The case that somebody already acquired a sequence value that not
> visible to other sessions has to be taken into account anyway.

I think all of that holds for us, as well. Our only real use for
this (so far, anyway) is in our trigger-based replication -- a
deferred AFTER INSERT trigger assigns a strictly monotonically
increasing commit number which must match the order of commit. I
don't see how getting an exclusive lock on the sequence itself could
introduce any bugs which we wouldn't have using a dummy table
created only to serve as a lock target.

Given that I can't think of any other uses for this feature, I guess
it would be pretty low on my list of priorities. As I said earlier,
"it would be nice."

-Kevin

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