Re: Concurrent ALTER SEQUENCE RESTART Regression

On 28/04/17 09:55, Michael Paquier wrote:
> On Thu, Apr 27, 2017 at 4:10 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> On April 27, 2017 12:06:55 AM PDT, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>>> On Thu, Apr 27, 2017 at 3:23 PM, Andres Freund <andres(at)anarazel(dot)de>
>>> wrote:
>>>> More fun:
>>>>
>>>> A: CREATE SEQUENCE someseq;
>>>> A: BEGIN;
>>>> A: ALTER SEQUENCE someseq MAXVALUE 10;
>>>> B: SELECT nextval('someseq') FROM generate_series(1, 1000);
>>>>
>>>> => ignores maxvalue
>>>
>>> Well, for this one that's because the catalog change is
>>> transactional...
>>
>> Or because the locking model is borked.
>
> The operation actually relies heavily on the fact that the exclusive
> lock on the buffer of pg_sequence is hold until the end of the catalog
> update. And using heap_inplace_update() seems mandatory to me as the
> metadata update should be non-transactional, giving the attached. I
> have added some isolation tests. Thoughts? The attached makes HEAD map
> with the pre-9.6 behavior.
>

The question is if we want the metadata update to be transactional or
not (I don't know what was Peter's goal here). If we did want
transactionality, we'd have to change lock levels for the sequence
relation in ALTER SEQUENCE so that it blocks other ALTERs and nextval().

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services