Re: CREATE SEQUENCE with RESTART option - Mailing list pgsql-hackers

On Wed, Apr 7, 2021 at 3:56 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Hi,
>
> It looks like we do allow $subject which has following behaviour:
> create sequence myseq restart 200;    --> sequence is starting from
> restart value overriding start value
> create sequence myseq start 100 restart 200; --> sequence is starting
> from restart value overriding start value
> create sequence myseq start 100 restart; --> sequence is starting from
> start value no overriding of start value occurs
> create sequence myseq restart; --> sequence is starting from default
> start value no overriding of start value occurs
>
> While we have documented the "restart" option behaviour for ALTER
> SEQUENCE, we have no mention of it in the CREATE SEQUENCE docs page.
> Do we need to document the above behaviour for CREATE SEQUENCE?
> Alternatively, do we need to throw an error if the user is not
> supposed to use the "restart" option with CREATE SEQUENCE?
>
> IMO, allowing the "restart" option for CREATE SEQUENCE doesn't make
> sense when we have the "start" option, so it's better to throw an
> error.

Using restart in CREATE SEQUENCE command looks, umm, funny. But
looking at the code it makes me wonder whether it's intentional.

1567     /* RESTART [WITH] */
1568     if (restart_value != NULL)
1569     {
1570         if (restart_value->arg != NULL)
1571             seqdataform->last_value = defGetInt64(restart_value);
1572         else
1573             seqdataform->last_value = seqform->seqstart;
1574         seqdataform->is_called = false;
1575         seqdataform->log_cnt = 0;
1576     }
1577     else if (isInit)
1578     {
1579         seqdataform->last_value = seqform->seqstart;
1580         seqdataform->is_called = false;
1581     }

"restart" as the name suggests "restarts" a sequence from a given
value or start of sequence. "start" on the other hand specifies the
"start" value of sequence and is also the value used to "restart" by
default from.

So here's what will happen in each of the cases you mentioned

> create sequence myseq restart 200;    --> sequence is starting from
> restart value overriding start value

the first time sequence will be used it will use value 200, but if
someone does a "restart" it will start from default start of that
sequence.

> create sequence myseq start 100 restart 200; --> sequence is starting
> from restart value overriding start value

the first time sequence will be used it will use value 200, but if
someone does a "restart" it will start from 100

> create sequence myseq start 100 restart; --> sequence is starting from
> start value no overriding of start value occurs

the first time sequence will be used it will use value 100, and if
someone does a "restart" it will start from 100

> create sequence myseq restart; --> sequence is starting from default
> start value no overriding of start value occurs

this is equivalent to "create sequence myseq"

This is the behaviour implied when we read
https://www.postgresql.org/docs/13/sql-createsequence.html and
https://www.postgresql.org/docs/13/sql-altersequence.html together.

At best CREATE SEQUENCE .... START ... RESTART ... can be a shorthand
for CREATE SEQUENCE ... START; ALTER SEQUENCE ... RESTART run back to
back. So it looks useful but in rare cases.

Said all that I agree that if we are supporting CREATE SEQUENCE ...
RESTART then we should document it, correctly. If that's not the
intention, we should disallow RESTART with CREATE SEQUENCE.

--
Best Wishes,
Ashutosh Bapat