Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression

On Sun, Apr 30, 2017 at 7:00 PM, Petr Jelinek
<petr.jelinek@2ndquadrant.com> wrote:
> On 28/04/17 09:55, Michael Paquier wrote:
>> On Thu, Apr 27, 2017 at 4:10 PM, Andres Freund <andres@anarazel.de> wrote:
>>> On April 27, 2017 12:06:55 AM PDT, Michael Paquier <michael.paquier@gmail.com> wrote:
>>>> On Thu, Apr 27, 2017 at 3:23 PM, Andres Freund <andres@anarazel.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().

Yeah, though it is not strictly necessary to block nextval() by using
a ShareUpdateExclusive lock. It seems to me that Andres has a good
point upthreadt houhg: things should remain non-transactional as this
has been the case since sequences are in Postgres. Also, the docs
still mention that changes take immediately effect and ALTER SEQUENCE
changes are non-reversible. HEAD fails to keep both promises.
-- 
Michael


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs