Re: Concurrent ALTER SEQUENCE RESTART Regression

On 2017-04-30 12:00:47 +0200, Petr Jelinek wrote:
> 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().

Well, previously it wasn't transactional and didn't block, but largely
consistently so. Now it's a weird mix: RESTART is not transactional,
MAXVAL etc are transactional - even when done at the same time as
RESTART -, but don't block, so you get inconsistent results until the
transaction commits. And you get failures that are not consistent with
transactional DDL.

- Andres