Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression

On 2017-05-19 08:31:15 -0400, Robert Haas wrote:
> On Thu, May 18, 2017 at 4:54 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > There's still weird behaviour, unfortunately. If you do an ALTER
> > SEQUENCE changing minval/maxval w/ restart in a transaction, and abort,
> > you'll a) quite possibly not be able to use the sequence anymore,
> > because it may of bounds b) DDL still isn't transactional.
>
> Your emails would be a bit easier to understand if you included a few
> more words.

Yea - I'd explained this one already somewhere upthread, and I'd hoped
it'd be enough, but I probably was wrong.

> I'm guessing "may of bounds" is supposed to say "may be out of bounds"?

Yes.

Consider a scenario like:

S1: CREATE SEQUENCE oobounds MINVALUE 1 START 1;
S1: SELECT nextval('oobounds'); -> 1
S2: BEGIN;
S2: ALTER SEQUENCE oobounds MAXVALUE -10 START -10 MINVALUE -1000 INCREMENT BY -1 RESTART;
S2: SELECT nextval('oobounds'); -> -10
S2: ROLLBACK;
S1: SELECT * FROM pg_sequence WHERE seqrelid = 'oobounds'::regclass;
┌──────────┬──────────┬──────────┬──────────────┬─────────────────────┬────────┬──────────┬──────────┐
│ seqrelid │ seqtypid │ seqstart │ seqincrement │ seqmax │ seqmin │ seqcache │ seqcycle │
├──────────┼──────────┼──────────┼──────────────┼─────────────────────┼────────┼──────────┼──────────┤
│ 203401 │ 20 │ 1 │ 1 │ 9223372036854775807 │ 1 │ 1 │ f │
└──────────┴──────────┴──────────┴──────────────┴─────────────────────┴────────┴──────────┴──────────┘
S1: SELECT nextval('oobounds'); -> -9

Ooops.

Two issues: Firstly, we get a value smaller than seqmin, obviously
that's not ok. But even if we'd error out, it'd imo still not be ok,
because we have a command that behaves partially transactionally
(keeping the seqmax/min transactionally), partially not (keeping the
current sequence state at -9).

- Andres