[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
On Wednesday 21 Sep 2011 19:03:17 Kevin Grittner wrote: Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
Kevin Grittner kevin.gritt...@wicourts.gov writes: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
On Wednesday 21 Sep 2011 19:24:55 Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sequence locking
On Wed, Sep 21, 2011 at 12:03 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Merlin Moncure mmonc...@gmail.com wrote: On Wed, Sep 21, 2011 at 11:51 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
