On Mon, Jul 26, 2021 at 04:57:53PM +0900, Michael Paquier wrote: > FWIW, like Ashutosh upthread, my vote would be to do nothing here in > terms of behavior changes as this is just breaking a behavior for the > sake of breaking it, so there are chances that this is going to piss > some users that relied accidentally on the existing behavior.
In short, I would be tempted with something like the attached, that documents RESTART in CREATE SEQUENCE, while describing its behavior according to START. In terms of regression tests, there is already a lot in this area with ALTER SEQUENCE, but I think that having two tests makes sense for CREATE SEQUENCE: one for RESTART without a value and one with, where both explicitely set START. Thoughts? -- Michael
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 71c2b0f1df..7f5835d52f 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -308,6 +308,23 @@ DROP SEQUENCE t1_f1_seq;
ERROR: sequence "t1_f1_seq" does not exist
-- Now OK:
DROP SEQUENCE myseq2;
+-- Interactions between START and RESTART at creation
+CREATE SEQUENCE test_seq2 START 150 RESTART 200;
+SELECT nextval('test_seq2'); -- 200, per RESTART
+ nextval
+---------
+ 200
+(1 row)
+
+DROP SEQUENCE test_seq2;
+CREATE SEQUENCE test_seq2 START 50 RESTART;
+SELECT nextval('test_seq2'); -- 50, per new START value
+ nextval
+---------
+ 50
+(1 row)
+
+DROP SEQUENCE test_seq2;
--
-- Alter sequence
--
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 7928ee23ee..9d379a9d63 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -167,6 +167,14 @@ DROP SEQUENCE t1_f1_seq;
-- Now OK:
DROP SEQUENCE myseq2;
+-- Interactions between START and RESTART at creation
+CREATE SEQUENCE test_seq2 START 150 RESTART 200;
+SELECT nextval('test_seq2'); -- 200, per RESTART
+DROP SEQUENCE test_seq2;
+CREATE SEQUENCE test_seq2 START 50 RESTART;
+SELECT nextval('test_seq2'); -- 50, per new START value
+DROP SEQUENCE test_seq2;
+
--
-- Alter sequence
--
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index e4085804a4..1683e11d4c 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -25,7 +25,9 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="param
[ AS <replaceable class="parameter">data_type</replaceable> ]
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
- [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
+ [ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
+ [ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
+ [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
</synopsis>
</refsynopsisdiv>
@@ -185,6 +187,22 @@ SELECT * FROM <replaceable>name</replaceable>;
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">restart</replaceable></term>
+ <listitem>
+ <para>
+ The optional clause <literal>RESTART [ WITH <replaceable
+ class="parameter">restart</replaceable> ]</literal> changes the
+ start value of the sequence. When specified alongside
+ <literal>START</literal>, the value specified with
+ <literal>RESTART WITH</literal> takes priority. If
+ <literal>RESTART</literal> is specified without a value, the
+ start value of the sequence is the one defined by
+ <literal>START</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">cache</replaceable></term>
<listitem>
signature.asc
Description: PGP signature
