Обсуждение: 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@anarazel.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.Grittner@wicourts.gov> wrote: > Andres Freund <andres@anarazel.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@gmail.com> wrote: > On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> Andres Freund <andres@anarazel.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@gmail.com> wrote: > > On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner > > > > <Kevin.Grittner@wicourts.gov> wrote: > >> Andres Freund <andres@anarazel.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.Grittner@wicourts.gov> writes:
> Andres Freund <andres@anarazel.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.Grittner@wicourts.gov> writes: > > Andres Freund <andres@anarazel.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@anarazel.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.Grittner@wicourts.gov> wrote: > Merlin Moncure <mmoncure@gmail.com> wrote: >> On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner >> <Kevin.Grittner@wicourts.gov> wrote: >>> Andres Freund <andres@anarazel.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