Add CREATE SEQUENCE AS <data type> clause

This stores a data type, required to be an integer type, with the
sequence.  The sequences min and max values default to the range
supported by the type, and they cannot be set to values exceeding that
range.  The internal implementation of the sequence is not affected.

Change the serial types to create sequences of the appropriate type.
This makes sure that the min and max values of the sequence for a serial
column match the range of values supported by the table column.  So the
sequence can no longer overflow the table column.

This also makes monitoring for sequence exhaustion/wraparound easier,
which currently requires various contortions to cross-reference the
sequences with the table columns they are used with.

This commit also effectively reverts the pg_sequence column reordering
in f3b421da5f4addc95812b9db05a24972b8fd9739, because the new seqtypid
column allows us to fill the hole in the struct and create a more
natural overall column ordering.

Reviewed-by: Steve Singer <steve@ssinger.info>
Reviewed-by: Michael Paquier <michael.paquier@gmail.com>

4 files changed, 67 insertions, 23 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 787cc10bf85..7d1c90a3115 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5774,10 +5774,11 @@
      </row>
 
      <row>
-      <entry><structfield>seqcycle</structfield></entry>
-      <entry><type>bool</type></entry>
+      <entry><structfield>seqtypid</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
       <entry></entry>
-      <entry>Whether the sequence cycles</entry>
+      <entry>Data type of the sequence</entry>
      </row>
 
      <row>
@@ -5814,6 +5815,13 @@
       <entry></entry>
       <entry>Cache size of the sequence</entry>
      </row>
+
+     <row>
+      <entry><structfield>seqcycle</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>Whether the sequence cycles</entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -9841,6 +9849,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
       <entry>Name of sequence's owner</entry>
      </row>
      <row>
+      <entry><structfield>data_type</structfield></entry>
+      <entry><type>regtype</type></entry>
+      <entry><literal><link linkend="catalog-pg-authid"><structname>pg_type</structname></link>.oid</literal></entry>
+      <entry>Data type of the sequence</entry>
+     </row>
+     <row>
       <entry><structfield>start_value</structfield></entry>
       <entry><type>bigint</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c43e325d064..a3a19ce8ce2 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4653,9 +4653,7 @@ ORDER BY c.ordinal_position;
       <entry><literal>data_type</literal></entry>
       <entry><type>character_data</type></entry>
       <entry>
-       The data type of the sequence.  In
-       <productname>PostgreSQL</productname>, this is currently always
-       <literal>bigint</literal>.
+       The data type of the sequence.
       </entry>
      </row>
 
diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
index 3b52e875e34..252a668189b 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -23,7 +23,9 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
+ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
+    [ 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> ]
     [ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
@@ -81,6 +83,26 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
      </varlistentry>
 
      <varlistentry>
+      <term><replaceable class="parameter">data_type</replaceable></term>
+      <listitem>
+       <para>
+        The optional
+        clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
+        changes the data type of the sequence.  Valid types are
+        are <literal>smallint</literal>, <literal>integer</literal>,
+        and <literal>bigint</literal>.
+       </para>
+
+       <para>
+        Note that changing the data type does not automatically change the
+        minimum and maximum values.  You can use the clauses <literal>NO
+        MINVALUE</literal> and <literal>NO MAXVALUE</literal> to adjust the
+        minimum and maximum values to the range of the new data type.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable class="parameter">increment</replaceable></term>
       <listitem>
        <para>
@@ -102,7 +124,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
         class="parameter">minvalue</replaceable></literal> determines
         the minimum value a sequence can generate. If <literal>NO
         MINVALUE</literal> is specified, the defaults of 1 and
-        -2<superscript>63</> for ascending and descending sequences,
+        the minimum value of the data type for ascending and descending sequences,
         respectively, will be used.  If neither option is specified,
         the current minimum value will be maintained.
        </para>
@@ -118,7 +140,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
         class="parameter">maxvalue</replaceable></literal> determines
         the maximum value for the sequence. If <literal>NO
         MAXVALUE</literal> is specified, the defaults of
-        2<superscript>63</>-1 and -1 for ascending and descending
+        the maximum value of the data type and -1 for ascending and descending
         sequences, respectively, will be used.  If neither option is
         specified, the current maximum value will be maintained.
        </para>
@@ -300,7 +322,7 @@ ALTER SEQUENCE serial RESTART WITH 105;
 
   <para>
    <command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
-   standard, except for the <literal>START WITH</>,
+   standard, except for the <literal>AS</literal>, <literal>START WITH</>,
    <literal>OWNED BY</>, <literal>OWNER TO</>, <literal>RENAME TO</>, and
    <literal>SET SCHEMA</literal> clauses, which are
    <productname>PostgreSQL</productname> extensions.
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 86ff018c4ba..f1448e7ab3c 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,9 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
+CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
+    [ 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 ]
     [ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
@@ -111,6 +113,21 @@ SELECT * FROM <replaceable>name</replaceable>;
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="parameter">data_type</replaceable></term>
+    <listitem>
+     <para>
+      The optional
+      clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
+      specifies the data type of the sequence.  Valid types are
+      are <literal>smallint</literal>, <literal>integer</literal>,
+      and <literal>bigint</literal>.  <literal>bigint</literal> is the
+      default.  The data type determines the default minimum and maximum
+      values of the sequence.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="parameter">increment</replaceable></term>
     <listitem>
      <para>
@@ -132,9 +149,8 @@ SELECT * FROM <replaceable>name</replaceable>;
       class="parameter">minvalue</replaceable></literal> determines
       the minimum value a sequence can generate. If this clause is not
       supplied or <option>NO MINVALUE</option> is specified, then
-      defaults will be used.  The defaults are 1 and
-      -2<superscript>63</> for ascending and descending sequences,
-      respectively.
+      defaults will be used.  The default for an ascending sequence is 1.  The
+      default for a descending sequence is the minimum value of the data type.
      </para>
     </listitem>
    </varlistentry>
@@ -148,9 +164,9 @@ SELECT * FROM <replaceable>name</replaceable>;
       class="parameter">maxvalue</replaceable></literal> determines
       the maximum value for the sequence. If this clause is not
       supplied or <option>NO MAXVALUE</option> is specified, then
-      default values will be used.  The defaults are
-      2<superscript>63</>-1 and -1 for ascending and descending
-      sequences, respectively.
+      default values will be used.  The default for an ascending sequence is
+      the maximum value of the data type.  The default for a descending
+      sequence is -1.
      </para>
     </listitem>
    </varlistentry>
@@ -349,12 +365,6 @@ END;
    <itemizedlist>
     <listitem>
      <para>
-      The standard's <literal>AS <replaceable>data_type</></literal> expression is not
-      supported.
-     </para>
-    </listitem>
-    <listitem>
-     <para>
       Obtaining the next value is done using the <function>nextval()</>
       function instead of the standard's <command>NEXT VALUE FOR</command>
       expression.