sequence data type

From 693bba6619d6e3283c6f7973f932044df2b4f695 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 8 Sep 2016 12:00:00 -0400
Subject: [PATCH v2] 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.
---
 doc/src/sgml/information_schema.sgml          |  4 +-
 doc/src/sgml/ref/create_sequence.sgml         | 37 +++++++----
 src/backend/catalog/information_schema.sql    |  4 +-
 src/backend/commands/sequence.c               | 92 ++++++++++++++++++++++++--
 src/backend/parser/gram.y                     |  6 +-
 src/backend/parser/parse_utilcmd.c            |  2 +-
 src/bin/pg_dump/pg_dump.c                     | 94 +++++++++++++++++----------
 src/bin/pg_dump/t/002_pg_dump.pl              |  2 +
 src/include/catalog/pg_proc.h                 |  2 +-
 src/include/commands/sequence.h               |  6 +-
 src/include/pg_config_manual.h                |  6 --
 src/test/modules/test_pg_dump/t/001_base.pl   |  1 +
 src/test/regress/expected/sequence.out        | 45 +++++++++----
 src/test/regress/expected/sequence_1.out      | 45 +++++++++----
 src/test/regress/expected/updatable_views.out |  3 +-
 src/test/regress/sql/sequence.sql             | 20 +++++-
 16 files changed, 269 insertions(+), 100 deletions(-)

diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c43e325..a3a19ce 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4653,9 +4653,7 @@ <title><literal>sequences</literal> Columns</title>
       <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/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 62ae379..f31b595 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,9 @@
 
  <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 @@ <title>Parameters</title>
    </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,9 @@ <title>Parameters</title>
       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</>-1 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
+      plus 1.
      </para>
     </listitem>
    </varlistentry>
@@ -148,9 +165,9 @@ <title>Parameters</title>
       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 +366,6 @@ <title>Compatibility</title>
    <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.
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 00550eb..73a183e 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1531,8 +1531,8 @@ CREATE VIEW sequences AS
     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
            CAST(c.relname AS sql_identifier) AS sequence_name,
-           CAST('bigint' AS character_data) AS data_type,
-           CAST(64 AS cardinal_number) AS numeric_precision,
+           CAST(format_type(p.data_type, null) AS character_data) AS data_type,
+           CAST(CASE format_type(p.data_type, null) WHEN 'bigint' THEN 64 WHEN 'integer' THEN 32 WHEN 'smallint' THEN 16 END AS cardinal_number) AS numeric_precision,
            CAST(2 AS cardinal_number) AS numeric_precision_radix,
            CAST(0 AS cardinal_number) AS numeric_scale,
            CAST(p.start_value AS character_data) AS start_value,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index fc3a8ee..1b1e261 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -31,6 +31,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
+#include "parser/parse_type.h"
 #include "storage/lmgr.h"
 #include "storage/proc.h"
 #include "storage/smgr.h"
@@ -212,6 +213,11 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
 				coldef->colname = "log_cnt";
 				value[i - 1] = Int64GetDatum((int64) 0);
 				break;
+			case SEQ_COL_TYPE:
+				coldef->typeName = makeTypeNameFromOid(REGTYPEOID, -1);
+				coldef->colname = "sequence_type";
+				value[i - 1] = ObjectIdGetDatum(new.sequence_type);
+				break;
 			case SEQ_COL_CYCLE:
 				coldef->typeName = makeTypeNameFromOid(BOOLOID, -1);
 				coldef->colname = "is_cycled";
@@ -1166,6 +1172,7 @@ static void
 init_params(ParseState *pstate, List *options, bool isInit,
 			Form_pg_sequence new, List **owned_by)
 {
+	DefElem    *as_type = NULL;
 	DefElem    *start_value = NULL;
 	DefElem    *restart_value = NULL;
 	DefElem    *increment_by = NULL;
@@ -1181,7 +1188,15 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	{
 		DefElem    *defel = (DefElem *) lfirst(option);
 
-		if (strcmp(defel->defname, "increment") == 0)
+		if (strcmp(defel->defname, "as") == 0)
+		{
+			if (as_type)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("conflicting or redundant options")));
+			as_type = defel;
+		}
+		else if (strcmp(defel->defname, "increment") == 0)
 		{
 			if (increment_by)
 				ereport(ERROR,
@@ -1265,6 +1280,23 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (isInit)
 		new->log_cnt = 0;
 
+	/* AS type */
+	if (as_type != NULL)
+	{
+		if (!isInit)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("sequence data type cannot be changed")));
+
+		new->sequence_type = typenameTypeId(NULL, defGetTypeName(as_type));
+		if (new->sequence_type != INT2OID && new->sequence_type != INT4OID && new->sequence_type != INT8OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("sequence type must be smallint, integer, or bigint")));
+	}
+	else if (isInit)
+		new->sequence_type = INT8OID;
+
 	/* INCREMENT BY */
 	if (increment_by != NULL)
 	{
@@ -1292,12 +1324,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (max_value != NULL && max_value->arg)
 	{
 		new->max_value = defGetInt64(max_value);
+
+		if ((new->sequence_type == INT2OID && new->max_value > PG_INT16_MAX)
+			|| (new->sequence_type == INT4OID && new->max_value > PG_INT32_MAX))
+		{
+			char		bufx[100];
+
+			snprintf(bufx, sizeof(bufx), INT64_FORMAT, new->max_value);
+
+			ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MAXVALUE (%s) is too large for sequence data type %s",
+						bufx, format_type_be(new->sequence_type))));
+		}
+
 		new->log_cnt = 0;
 	}
 	else if (isInit || max_value != NULL)
 	{
 		if (new->increment_by > 0)
-			new->max_value = SEQ_MAXVALUE;		/* ascending seq */
+		{
+			/* ascending seq */
+			if (new->sequence_type == INT2OID)
+				new->max_value = PG_INT16_MAX;
+			else if (new->sequence_type == INT4OID)
+				new->max_value = PG_INT32_MAX;
+			else
+				new->max_value = PG_INT64_MAX;
+		}
 		else
 			new->max_value = -1;	/* descending seq */
 		new->log_cnt = 0;
@@ -1307,6 +1361,20 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (min_value != NULL && min_value->arg)
 	{
 		new->min_value = defGetInt64(min_value);
+
+		if ((new->sequence_type == INT2OID && new->min_value < -PG_INT16_MAX)
+			|| (new->sequence_type == INT4OID && new->min_value < -PG_INT32_MAX))
+		{
+			char		bufm[100];
+
+			snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->min_value);
+
+			ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MINVALUE (%s) is too large for sequence data type %s",
+						bufm, format_type_be(new->sequence_type))));
+		}
+
 		new->log_cnt = 0;
 	}
 	else if (isInit || min_value != NULL)
@@ -1314,7 +1382,16 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		if (new->increment_by > 0)
 			new->min_value = 1; /* ascending seq */
 		else
-			new->min_value = SEQ_MINVALUE;		/* descending seq */
+		{
+			/* descending seq */
+			/* We use the _MAX constants for symmetry. */
+			if (new->sequence_type == INT2OID)
+				new->min_value = -PG_INT16_MAX;
+			else if (new->sequence_type == INT4OID)
+				new->min_value = -PG_INT32_MAX;
+			else
+				new->min_value = -PG_INT64_MAX;
+		}
 		new->log_cnt = 0;
 	}
 
@@ -1534,8 +1611,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
 	TupleDesc	tupdesc;
-	Datum		values[5];
-	bool		isnull[5];
+	Datum		values[6];
+	bool		isnull[6];
 	SeqTable	elm;
 	Relation	seqrel;
 	Buffer		buf;
@@ -1551,7 +1628,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 				 errmsg("permission denied for sequence %s",
 						RelationGetRelationName(seqrel))));
 
-	tupdesc = CreateTemplateTupleDesc(5, false);
+	tupdesc = CreateTemplateTupleDesc(6, false);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1562,6 +1639,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "cycle_option",
 					   BOOLOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "data_type",
+					   OIDOID, -1, 0);
 
 	BlessTupleDesc(tupdesc);
 
@@ -1574,6 +1653,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	values[2] = Int64GetDatum(seq->max_value);
 	values[3] = Int64GetDatum(seq->increment_by);
 	values[4] = BoolGetDatum(seq->is_cycled);
+	values[5] = ObjectIdGetDatum(seq->sequence_type);
 
 	UnlockReleaseBuffer(buf);
 	relation_close(seqrel, NoLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1526c73..db99751 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3634,7 +3634,11 @@ SeqOptList: SeqOptElem								{ $$ = list_make1($1); }
 			| SeqOptList SeqOptElem					{ $$ = lappend($1, $2); }
 		;
 
-SeqOptElem: CACHE NumericOnly
+SeqOptElem: AS SimpleTypename
+				{
+					$$ = makeDefElem("as", (Node *)$2, @1);
+				}
+			| CACHE NumericOnly
 				{
 					$$ = makeDefElem("cache", (Node *)$2, @1);
 				}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 7a2950e..bc68367 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -456,7 +456,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		 */
 		seqstmt = makeNode(CreateSeqStmt);
 		seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
-		seqstmt->options = NIL;
+		seqstmt->options = list_make1(makeDefElem("as", (Node *) SystemTypeName(column->typeName->typeOid == INT2OID ? "int2" : (column->typeName->typeOid == INT4OID ? "int4" : "int8")), -1));
 
 		/*
 		 * If this is ALTER ADD COLUMN, make sure the sequence will be owned
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ba9c276..2d5d012 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16329,12 +16329,12 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	PGresult   *res;
 	char	   *startv,
 			   *incby,
-			   *maxv = NULL,
-			   *minv = NULL,
-			   *cache;
-	char		bufm[100],
-				bufx[100];
+			   *maxv,
+			   *minv,
+			   *cache,
+			   *seqtype;
 	bool		cycled;
+	bool		is_ascending;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
@@ -16342,41 +16342,28 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	/* Make sure we are in proper schema */
 	selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
-
-	if (fout->remoteVersion >= 80400)
+	if (fout->remoteVersion >= 100000)
+	{
+		appendPQExpBuffer(query,
+						  "SELECT sequence_name, "
+						  "start_value, increment_by, max_value, min_value, "
+						  "cache_value, is_cycled, sequence_type FROM %s",
+						  fmtId(tbinfo->dobj.name));
+	}
+	else if (fout->remoteVersion >= 80400)
 	{
 		appendPQExpBuffer(query,
 						  "SELECT sequence_name, "
-						  "start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
-						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
+						  "start_value, increment_by, max_value, min_value, "
+						  "cache_value, is_cycled, 'bigint'::name AS sequence_type FROM %s",
 						  fmtId(tbinfo->dobj.name));
 	}
 	else
 	{
 		appendPQExpBuffer(query,
 						  "SELECT sequence_name, "
-						  "0 AS start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
-						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
+						  "0 AS start_value, increment_by, max_value, min_value, "
+						  "cache_value, is_cycled, 'bigint'::name AS sequence_type FROM %s",
 						  fmtId(tbinfo->dobj.name));
 	}
 
@@ -16403,12 +16390,46 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 
 	startv = PQgetvalue(res, 0, 1);
 	incby = PQgetvalue(res, 0, 2);
-	if (!PQgetisnull(res, 0, 3))
-		maxv = PQgetvalue(res, 0, 3);
-	if (!PQgetisnull(res, 0, 4))
-		minv = PQgetvalue(res, 0, 4);
+	maxv = PQgetvalue(res, 0, 3);
+	minv = PQgetvalue(res, 0, 4);
 	cache = PQgetvalue(res, 0, 5);
 	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+	seqtype = PQgetvalue(res, 0, 7);
+
+	is_ascending = incby[0] != '-';
+
+	if (is_ascending && atoi(minv) == 1)
+		minv = NULL;
+	if (!is_ascending && atoi(maxv) == -1)
+		maxv = NULL;
+
+	if (strcmp(seqtype, "smallint") == 0)
+	{
+		if (!is_ascending && atoi(minv) == -PG_INT16_MAX)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT16_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "integer") == 0)
+	{
+		if (!is_ascending && atoi(minv) == -PG_INT32_MAX)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT32_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "bigint") == 0)
+	{
+		char		bufm[100],
+					bufx[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, -PG_INT64_MAX);
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
+
+		if (!is_ascending && strcmp(minv, bufm) == 0)
+			minv = NULL;
+		if (is_ascending && strcmp(maxv, bufx) == 0)
+			maxv = NULL;
+	}
 
 	/*
 	 * DROP must be fully qualified in case same name appears in pg_catalog
@@ -16432,6 +16453,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 					  "CREATE SEQUENCE %s\n",
 					  fmtId(tbinfo->dobj.name));
 
+	if (strcmp(seqtype, "bigint") != 0)
+		appendPQExpBuffer(query, "    AS %s\n", seqtype);
+
 	if (fout->remoteVersion >= 80400)
 		appendPQExpBuffer(query, "    START WITH %s\n", startv);
 
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 37cbdcd..2d72232 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1965,6 +1965,7 @@
 	'CREATE SEQUENCE test_table_col1_seq' => {
 		regexp => qr/^
 			\QCREATE SEQUENCE test_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
@@ -1995,6 +1996,7 @@
 	'CREATE SEQUENCE test_third_table_col1_seq' => {
 		regexp => qr/^
 			\QCREATE SEQUENCE test_third_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e2d08ba..9c7fd42 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1766,7 +1766,7 @@ DATA(insert OID = 1576 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20
 DESCR("set sequence value");
 DATA(insert OID = 1765 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ ));
 DESCR("set sequence value and is_called status");
-DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16}" "{i,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
+DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,26}" "{i,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,data_type}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
 DESCR("sequence parameters, for use by information schema");
 
 DATA(insert OID = 1579 (  varbit_in			PGNSP PGUID 12 1 0 0 0 f f f f t f i s 3 0 1562 "2275 26 23" _null_ _null_ _null_ _null_ _null_ varbit_in _null_ _null_ _null_ ));
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 392a626..54c24b7 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -32,6 +32,7 @@ typedef struct FormData_pg_sequence
 	int64		min_value;
 	int64		cache_value;
 	int64		log_cnt;
+	Oid			sequence_type;
 	bool		is_cycled;
 	bool		is_called;
 } FormData_pg_sequence;
@@ -50,8 +51,9 @@ typedef FormData_pg_sequence *Form_pg_sequence;
 #define SEQ_COL_MINVALUE		6
 #define SEQ_COL_CACHE			7
 #define SEQ_COL_LOG				8
-#define SEQ_COL_CYCLE			9
-#define SEQ_COL_CALLED			10
+#define SEQ_COL_TYPE			9
+#define SEQ_COL_CYCLE			10
+#define SEQ_COL_CALLED			11
 
 #define SEQ_COL_FIRSTCOL		SEQ_COL_NAME
 #define SEQ_COL_LASTCOL			SEQ_COL_CALLED
diff --git a/src/include/pg_config_manual.h b/src/include/pg_config_manual.h
index a2b2b61..d2b99fb 100644
--- a/src/include/pg_config_manual.h
+++ b/src/include/pg_config_manual.h
@@ -46,12 +46,6 @@
 #define INDEX_MAX_KEYS		32
 
 /*
- * Set the upper and lower bounds of sequence values.
- */
-#define SEQ_MAXVALUE	PG_INT64_MAX
-#define SEQ_MINVALUE	(-SEQ_MAXVALUE)
-
-/*
  * When we don't have native spinlocks, we use semaphores to simulate them.
  * Decreasing this value reduces consumption of OS resources; increasing it
  * may improve performance, but supplying a real spinlock implementation is
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index 55f0eb4..a6e0efb 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -226,6 +226,7 @@
 	'CREATE SEQUENCE regress_pg_dump_table_col1_seq' => {
 		regexp => qr/^
                     \QCREATE SEQUENCE regress_pg_dump_table_col1_seq\E
+                    \n\s+\QAS integer\E
                     \n\s+\QSTART WITH 1\E
                     \n\s+\QINCREMENT BY 1\E
                     \n\s+\QNO MINVALUE\E
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 4ffbe92..1141d5c 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -1,3 +1,19 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test3 AS integer;
+CREATE SEQUENCE sequence_test4 AS smallint;
+CREATE SEQUENCE sequence_test5 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS foo;
+ERROR:  type "foo" does not exist
+ALTER SEQUENCE sequence_test3 AS bigint;  -- fail
+ERROR:  sequence data type cannot be changed
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -173,9 +189,9 @@ DROP SEQUENCE sequence_test;
 CREATE SEQUENCE foo_seq;
 ALTER TABLE foo_seq RENAME TO foo_seq_new;
 SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq       |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
+ sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | sequence_type | is_cycled | is_called 
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+---------------+-----------+-----------
+ foo_seq       |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | bigint        | f         | f
 (1 row)
 
 SELECT nextval('foo_seq_new');
@@ -191,9 +207,9 @@ SELECT nextval('foo_seq_new');
 (1 row)
 
 SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq       |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
+ sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | sequence_type | is_cycled | is_called 
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+---------------+-----------+-----------
+ foo_seq       |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | bigint        | f         | t
 (1 row)
 
 DROP SEQUENCE foo_seq_new;
@@ -301,19 +317,22 @@ SELECT nextval('sequence_test2');
 (1 row)
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test3     | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | sequence_test4     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test5     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(6 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(10 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
index 05da2bf..d5ecbf8 100644
--- a/src/test/regress/expected/sequence_1.out
+++ b/src/test/regress/expected/sequence_1.out
@@ -1,3 +1,19 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test3 AS integer;
+CREATE SEQUENCE sequence_test4 AS smallint;
+CREATE SEQUENCE sequence_test5 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS foo;
+ERROR:  type "foo" does not exist
+ALTER SEQUENCE sequence_test3 AS bigint;  -- fail
+ERROR:  sequence data type cannot be changed
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -173,9 +189,9 @@ DROP SEQUENCE sequence_test;
 CREATE SEQUENCE foo_seq;
 ALTER TABLE foo_seq RENAME TO foo_seq_new;
 SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq       |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
+ sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | sequence_type | is_cycled | is_called 
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+---------------+-----------+-----------
+ foo_seq       |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | bigint        | f         | f
 (1 row)
 
 SELECT nextval('foo_seq_new');
@@ -191,9 +207,9 @@ SELECT nextval('foo_seq_new');
 (1 row)
 
 SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq       |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
+ sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | sequence_type | is_cycled | is_called 
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+---------------+-----------+-----------
+ foo_seq       |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | bigint        | f         | t
 (1 row)
 
 DROP SEQUENCE foo_seq_new;
@@ -301,19 +317,22 @@ SELECT nextval('sequence_test2');
 (1 row)
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test3     | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | sequence_test4     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test5     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(6 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(10 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index f60991e..1b451e5 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -107,6 +107,7 @@ SELECT table_name, column_name, is_updatable
  ro_view19  | min_value     | NO
  ro_view19  | cache_value   | NO
  ro_view19  | log_cnt       | NO
+ ro_view19  | sequence_type | NO
  ro_view19  | is_cycled     | NO
  ro_view19  | is_called     | NO
  ro_view2   | a             | NO
@@ -134,7 +135,7 @@ SELECT table_name, column_name, is_updatable
  rw_view16  | a             | YES
  rw_view16  | b             | YES
  rw_view16  | aa            | YES
-(46 rows)
+(47 rows)
 
 -- Read-only views
 DELETE FROM ro_view1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 98a2e7d..897d918 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -1,3 +1,18 @@
+--
+-- CREATE SEQUENCE
+--
+
+CREATE SEQUENCE sequence_test3 AS integer;
+CREATE SEQUENCE sequence_test4 AS smallint;
+CREATE SEQUENCE sequence_test5 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+CREATE SEQUENCE sequence_testx AS foo;
+
+ALTER SEQUENCE sequence_test3 AS bigint;  -- fail
+
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+
 ---
 --- test creation of SERIAL column
 ---
@@ -139,9 +154,8 @@ CREATE SEQUENCE sequence_test2 START WITH 32;
 SELECT nextval('sequence_test2');
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
 
 -- Test comments
-- 
2.10.0

From 62486c9092f21a1afc1bd9cfa50f570e9e3e92c1 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Fri, 23 Dec 2016 12:00:00 -0500
Subject: [PATCH v3] 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.
---
 doc/src/sgml/catalogs.sgml                  |  14 +++-
 doc/src/sgml/information_schema.sgml        |   4 +-
 doc/src/sgml/ref/create_sequence.sgml       |  37 ++++++----
 src/backend/catalog/information_schema.sql  |   4 +-
 src/backend/commands/sequence.c             |  95 ++++++++++++++++++++++---
 src/backend/parser/gram.y                   |   6 +-
 src/backend/parser/parse_utilcmd.c          |   2 +-
 src/bin/pg_dump/pg_dump.c                   | 105 +++++++++++++++-------------
 src/bin/pg_dump/t/002_pg_dump.pl            |   2 +
 src/include/catalog/catversion.h            |   2 +-
 src/include/catalog/pg_proc.h               |   2 +-
 src/include/catalog/pg_sequence.h           |   8 ++-
 src/include/pg_config_manual.h              |   6 --
 src/test/modules/test_pg_dump/t/001_base.pl |   1 +
 src/test/regress/expected/sequence.out      |  51 ++++++++++----
 src/test/regress/expected/sequence_1.out    |  51 ++++++++++----
 src/test/regress/sql/sequence.sql           |  24 +++++--
 17 files changed, 291 insertions(+), 123 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 493050618d..765bc12c51 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5628,10 +5628,11 @@ <title><structname>pg_sequence</> Columns</title>
      </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>
@@ -5668,6 +5669,13 @@ <title><structname>pg_sequence</> Columns</title>
       <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>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c43e325d06..a3a19ce8ce 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4653,9 +4653,7 @@ <title><literal>sequences</literal> Columns</title>
       <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/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 62ae379226..f31b59569e 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,9 @@
 
  <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 @@ <title>Parameters</title>
    </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,9 @@ <title>Parameters</title>
       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</>-1 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
+      plus 1.
      </para>
     </listitem>
    </varlistentry>
@@ -148,9 +165,9 @@ <title>Parameters</title>
       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 +366,6 @@ <title>Compatibility</title>
    <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.
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 182d2d0674..93750ab16c 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1531,8 +1531,8 @@ CREATE VIEW sequences AS
     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
            CAST(c.relname AS sql_identifier) AS sequence_name,
-           CAST('bigint' AS character_data) AS data_type,
-           CAST(64 AS cardinal_number) AS numeric_precision,
+           CAST(format_type(s.seqtypid, null) AS character_data) AS data_type,
+           CAST(_pg_numeric_precision(s.seqtypid, -1) AS cardinal_number) AS numeric_precision,
            CAST(2 AS cardinal_number) AS numeric_precision_radix,
            CAST(0 AS cardinal_number) AS numeric_scale,
            CAST(s.seqstart AS character_data) AS start_value,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 668d82771a..e53fe68a53 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -33,6 +33,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
+#include "parser/parse_type.h"
 #include "storage/lmgr.h"
 #include "storage/proc.h"
 #include "storage/smgr.h"
@@ -227,12 +228,13 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
 	memset(pgs_nulls, 0, sizeof(pgs_nulls));
 
 	pgs_values[Anum_pg_sequence_seqrelid - 1] = ObjectIdGetDatum(seqoid);
-	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
+	pgs_values[Anum_pg_sequence_seqtypid - 1] = ObjectIdGetDatum(seqform.seqtypid);
 	pgs_values[Anum_pg_sequence_seqstart - 1] = Int64GetDatumFast(seqform.seqstart);
 	pgs_values[Anum_pg_sequence_seqincrement - 1] = Int64GetDatumFast(seqform.seqincrement);
 	pgs_values[Anum_pg_sequence_seqmax - 1] = Int64GetDatumFast(seqform.seqmax);
 	pgs_values[Anum_pg_sequence_seqmin - 1] = Int64GetDatumFast(seqform.seqmin);
 	pgs_values[Anum_pg_sequence_seqcache - 1] = Int64GetDatumFast(seqform.seqcache);
+	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
 
 	tuple = heap_form_tuple(tupDesc, pgs_values, pgs_nulls);
 	simple_heap_insert(rel, tuple);
@@ -622,11 +624,11 @@ nextval_internal(Oid relid)
 	if (!HeapTupleIsValid(pgstuple))
 		elog(ERROR, "cache lookup failed for sequence %u", relid);
 	pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple);
-	cycle = pgsform->seqcycle;
 	incby = pgsform->seqincrement;
 	maxv = pgsform->seqmax;
 	minv = pgsform->seqmin;
 	cache = pgsform->seqcache;
+	cycle = pgsform->seqcycle;
 	ReleaseSysCache(pgstuple);
 
 	/* lock page' buffer and read tuple */
@@ -1221,6 +1223,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 			Form_pg_sequence seqform,
 			Form_pg_sequence_data seqdataform, List **owned_by)
 {
+	DefElem    *as_type = NULL;
 	DefElem    *start_value = NULL;
 	DefElem    *restart_value = NULL;
 	DefElem    *increment_by = NULL;
@@ -1236,7 +1239,15 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	{
 		DefElem    *defel = (DefElem *) lfirst(option);
 
-		if (strcmp(defel->defname, "increment") == 0)
+		if (strcmp(defel->defname, "as") == 0)
+		{
+			if (as_type)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("conflicting or redundant options")));
+			as_type = defel;
+		}
+		else if (strcmp(defel->defname, "increment") == 0)
 		{
 			if (increment_by)
 				ereport(ERROR,
@@ -1320,6 +1331,25 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (isInit)
 		seqdataform->log_cnt = 0;
 
+	/* AS type */
+	if (as_type != NULL)
+	{
+		if (!isInit)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("sequence data type cannot be changed")));
+
+		seqform->seqtypid = typenameTypeId(pstate, defGetTypeName(as_type));
+		if (seqform->seqtypid != INT2OID &&
+			seqform->seqtypid != INT4OID &&
+			seqform->seqtypid != INT8OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("sequence type must be smallint, integer, or bigint")));
+	}
+	else if (isInit)
+		seqform->seqtypid = INT8OID;
+
 	/* INCREMENT BY */
 	if (increment_by != NULL)
 	{
@@ -1347,12 +1377,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (max_value != NULL && max_value->arg)
 	{
 		seqform->seqmax = defGetInt64(max_value);
+
+		if ((seqform->seqtypid == INT2OID && seqform->seqmax > PG_INT16_MAX)
+			|| (seqform->seqtypid == INT4OID && seqform->seqmax > PG_INT32_MAX))
+		{
+			char		bufx[100];
+
+			snprintf(bufx, sizeof(bufx), INT64_FORMAT, seqform->seqmax);
+
+			ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MAXVALUE (%s) is too large for sequence data type %s",
+						bufx, format_type_be(seqform->seqtypid))));
+		}
+
 		seqdataform->log_cnt = 0;
 	}
 	else if (isInit || max_value != NULL)
 	{
 		if (seqform->seqincrement > 0)
-			seqform->seqmax = SEQ_MAXVALUE;		/* ascending seq */
+		{
+			/* ascending seq */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmax = PG_INT16_MAX;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmax = PG_INT32_MAX;
+			else
+				seqform->seqmax = PG_INT64_MAX;
+		}
 		else
 			seqform->seqmax = -1;	/* descending seq */
 		seqdataform->log_cnt = 0;
@@ -1362,6 +1414,21 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (min_value != NULL && min_value->arg)
 	{
 		seqform->seqmin = defGetInt64(min_value);
+
+		if ((seqform->seqtypid == INT2OID && seqform->seqmin < -PG_INT16_MAX)
+			|| (seqform->seqtypid == INT4OID && seqform->seqmin < -PG_INT32_MAX)
+			|| (seqform->seqtypid == INT8OID && seqform->seqmin < -PG_INT64_MAX))
+		{
+			char		bufm[100];
+
+			snprintf(bufm, sizeof(bufm), INT64_FORMAT, seqform->seqmin);
+
+			ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MINVALUE (%s) is too large for sequence data type %s",
+						bufm, format_type_be(seqform->seqtypid))));
+		}
+
 		seqdataform->log_cnt = 0;
 	}
 	else if (isInit || min_value != NULL)
@@ -1369,7 +1436,16 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		if (seqform->seqincrement > 0)
 			seqform->seqmin = 1; /* ascending seq */
 		else
-			seqform->seqmin = SEQ_MINVALUE;		/* descending seq */
+		{
+			/* descending seq */
+			/* We use the _MAX constants for symmetry. */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmin = -PG_INT16_MAX;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmin = -PG_INT32_MAX;
+			else
+				seqform->seqmin = -PG_INT64_MAX;
+		}
 		seqdataform->log_cnt = 0;
 	}
 
@@ -1590,8 +1666,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
 	TupleDesc	tupdesc;
-	Datum		values[6];
-	bool		isnull[6];
+	Datum		values[7];
+	bool		isnull[7];
 	HeapTuple	pgstuple;
 	Form_pg_sequence pgsform;
 
@@ -1601,7 +1677,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 				 errmsg("permission denied for sequence %s",
 						get_rel_name(relid))));
 
-	tupdesc = CreateTemplateTupleDesc(6, false);
+	tupdesc = CreateTemplateTupleDesc(7, false);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1614,6 +1690,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 					   BOOLOID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
 					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "data_type",
+					   OIDOID, -1, 0);
 
 	BlessTupleDesc(tupdesc);
 
@@ -1630,6 +1708,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	values[3] = Int64GetDatum(pgsform->seqincrement);
 	values[4] = BoolGetDatum(pgsform->seqcycle);
 	values[5] = Int64GetDatum(pgsform->seqcache);
+	values[6] = ObjectIdGetDatum(pgsform->seqtypid);
 
 	ReleaseSysCache(pgstuple);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 08cf5b78f5..dd3bc80d2f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3907,7 +3907,11 @@ SeqOptList: SeqOptElem								{ $$ = list_make1($1); }
 			| SeqOptList SeqOptElem					{ $$ = lappend($1, $2); }
 		;
 
-SeqOptElem: CACHE NumericOnly
+SeqOptElem: AS SimpleTypename
+				{
+					$$ = makeDefElem("as", (Node *)$2, @1);
+				}
+			| CACHE NumericOnly
 				{
 					$$ = makeDefElem("cache", (Node *)$2, @1);
 				}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 4f74208633..8a0f1630c7 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -469,7 +469,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		 */
 		seqstmt = makeNode(CreateSeqStmt);
 		seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
-		seqstmt->options = NIL;
+		seqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(column->typeName->typeOid, -1), -1));
 
 		/*
 		 * If this is ALTER ADD COLUMN, make sure the sequence will be owned
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e5545b31d4..57f8e12017 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15411,12 +15411,12 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	PGresult   *res;
 	char	   *startv,
 			   *incby,
-			   *maxv = NULL,
-			   *minv = NULL,
-			   *cache;
-	char		bufm[100],
-				bufx[100];
+			   *maxv,
+			   *minv,
+			   *cache,
+			   *seqtype;
 	bool		cycled;
+	bool		is_ascending;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
@@ -15424,58 +15424,32 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	/* Make sure we are in proper schema */
 	selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
-
 	if (fout->remoteVersion >= 100000)
 	{
 		appendPQExpBuffer(query,
-						  "SELECT seqstart, seqincrement, "
-						  "CASE WHEN seqincrement > 0 AND seqmax = %s THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmax = -1 THEN NULL "
-						  "     ELSE seqmax "
-						  "END AS seqmax, "
-						  "CASE WHEN seqincrement > 0 AND seqmin = 1 THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmin = %s THEN NULL "
-						  "     ELSE seqmin "
-						  "END AS seqmin, "
+						  "SELECT format_type(seqtypid, NULL), "
+						  "seqstart, seqincrement, "
+						  "seqmax, seqmin, "
 						  "seqcache, seqcycle "
 						  "FROM pg_class c "
 						  "JOIN pg_sequence s ON (s.seqrelid = c.oid) "
-						  "WHERE relname = ",
-						  bufx, bufm);
+						  "WHERE relname = ");
 		appendStringLiteralAH(query, tbinfo->dobj.name, fout);
 	}
 	else if (fout->remoteVersion >= 80400)
 	{
 		appendPQExpBuffer(query,
-						  "SELECT start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 	else
 	{
 		appendPQExpBuffer(query,
-						  "SELECT 0 AS start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "0 AS start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 
@@ -15490,14 +15464,48 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		exit_nicely(1);
 	}
 
-	startv = PQgetvalue(res, 0, 0);
-	incby = PQgetvalue(res, 0, 1);
-	if (!PQgetisnull(res, 0, 2))
-		maxv = PQgetvalue(res, 0, 2);
-	if (!PQgetisnull(res, 0, 3))
-		minv = PQgetvalue(res, 0, 3);
-	cache = PQgetvalue(res, 0, 4);
-	cycled = (strcmp(PQgetvalue(res, 0, 5), "t") == 0);
+	seqtype = PQgetvalue(res, 0, 0);
+	startv = PQgetvalue(res, 0, 1);
+	incby = PQgetvalue(res, 0, 2);
+	maxv = PQgetvalue(res, 0, 3);
+	minv = PQgetvalue(res, 0, 4);
+	cache = PQgetvalue(res, 0, 5);
+	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+
+	is_ascending = incby[0] != '-';
+
+	if (is_ascending && atoi(minv) == 1)
+		minv = NULL;
+	if (!is_ascending && atoi(maxv) == -1)
+		maxv = NULL;
+
+	if (strcmp(seqtype, "smallint") == 0)
+	{
+		if (!is_ascending && atoi(minv) == -PG_INT16_MAX)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT16_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "integer") == 0)
+	{
+		if (!is_ascending && atoi(minv) == -PG_INT32_MAX)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT32_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "bigint") == 0)
+	{
+		char		bufm[100],
+					bufx[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, -PG_INT64_MAX);
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
+
+		if (!is_ascending && strcmp(minv, bufm) == 0)
+			minv = NULL;
+		if (is_ascending && strcmp(maxv, bufx) == 0)
+			maxv = NULL;
+	}
 
 	/*
 	 * DROP must be fully qualified in case same name appears in pg_catalog
@@ -15521,6 +15529,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 					  "CREATE SEQUENCE %s\n",
 					  fmtId(tbinfo->dobj.name));
 
+	if (strcmp(seqtype, "bigint") != 0)
+		appendPQExpBuffer(query, "    AS %s\n", seqtype);
+
 	if (fout->remoteVersion >= 80400)
 		appendPQExpBuffer(query, "    START WITH %s\n", startv);
 
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 59191ccecd..f919c81aa0 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2430,6 +2430,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
@@ -2465,6 +2466,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_third_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 45596abe76..660a03c27f 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201612231
+#define CATALOG_VERSION_NO	201612317
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a6cc2eb539..6cbadc8504 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1763,7 +1763,7 @@ DATA(insert OID = 1576 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20
 DESCR("set sequence value");
 DATA(insert OID = 1765 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ ));
 DESCR("set sequence value and is_called status");
-DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20}" "{i,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
+DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20,26}" "{i,o,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size,data_type}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
 DESCR("sequence parameters, for use by information schema");
 DATA(insert OID = 4032 ( pg_sequence_last_value		PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_	pg_sequence_last_value _null_ _null_ _null_ ));
 DESCR("sequence last value");
diff --git a/src/include/catalog/pg_sequence.h b/src/include/catalog/pg_sequence.h
index 350b286e45..ef15e68a57 100644
--- a/src/include/catalog/pg_sequence.h
+++ b/src/include/catalog/pg_sequence.h
@@ -8,23 +8,25 @@
 CATALOG(pg_sequence,2224) BKI_WITHOUT_OIDS
 {
 	Oid			seqrelid;
-	bool		seqcycle;
+	Oid			seqtypid;
 	int64		seqstart;
 	int64		seqincrement;
 	int64		seqmax;
 	int64		seqmin;
 	int64		seqcache;
+	bool		seqcycle;
 } FormData_pg_sequence;
 
 typedef FormData_pg_sequence *Form_pg_sequence;
 
-#define Natts_pg_sequence				7
+#define Natts_pg_sequence				8
 #define Anum_pg_sequence_seqrelid		1
-#define Anum_pg_sequence_seqcycle		2
+#define Anum_pg_sequence_seqtypid		2
 #define Anum_pg_sequence_seqstart		3
 #define Anum_pg_sequence_seqincrement	4
 #define Anum_pg_sequence_seqmax			5
 #define Anum_pg_sequence_seqmin			6
 #define Anum_pg_sequence_seqcache		7
+#define Anum_pg_sequence_seqcycle		8
 
 #endif	/* PG_SEQUENCE_H */
diff --git a/src/include/pg_config_manual.h b/src/include/pg_config_manual.h
index 58b1db9f68..8d2b814217 100644
--- a/src/include/pg_config_manual.h
+++ b/src/include/pg_config_manual.h
@@ -51,12 +51,6 @@
 #define PARTITION_MAX_KEYS	32
 
 /*
- * Set the upper and lower bounds of sequence values.
- */
-#define SEQ_MAXVALUE	PG_INT64_MAX
-#define SEQ_MINVALUE	(-SEQ_MAXVALUE)
-
-/*
  * When we don't have native spinlocks, we use semaphores to simulate them.
  * Decreasing this value reduces consumption of OS resources; increasing it
  * may improve performance, but supplying a real spinlock implementation is
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index dc90a4aa12..eb2b310234 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -226,6 +226,7 @@
 	'CREATE SEQUENCE regress_pg_dump_table_col1_seq' => {
 		regexp => qr/^
                     \QCREATE SEQUENCE regress_pg_dump_table_col1_seq\E
+                    \n\s+\QAS integer\E
                     \n\s+\QSTART WITH 1\E
                     \n\s+\QINCREMENT BY 1\E
                     \n\s+\QNO MINVALUE\E
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index a2bdd3002b..f17ec8cda2 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -1,3 +1,21 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+ERROR:  type "nosuchtype" does not exist
+LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
+                                          ^
+ALTER SEQUENCE sequence_test4 AS bigint;  -- fail
+ERROR:  sequence data type cannot be changed
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -302,37 +320,42 @@ SELECT nextval('sequence_test2');
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
  regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test4     | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test6     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(7 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(11 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
  schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
 ------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
  public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
  public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
- public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | sequence_test4     |           1 |         1 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_test5     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test6     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 |          2147483647 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
-(7 rows)
+ public     | serialtest_f2_foo  |           1 |         1 |          2147483647 |            1 | f     |          1 |          3
+(11 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
index 5d7ab72944..31690a8277 100644
--- a/src/test/regress/expected/sequence_1.out
+++ b/src/test/regress/expected/sequence_1.out
@@ -1,3 +1,21 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+ERROR:  type "nosuchtype" does not exist
+LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
+                                          ^
+ALTER SEQUENCE sequence_test4 AS bigint;  -- fail
+ERROR:  sequence data type cannot be changed
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -302,37 +320,42 @@ SELECT nextval('sequence_test2');
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
  regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test4     | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test6     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(7 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(11 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
  schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
 ------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
  public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
  public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
- public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | sequence_test4     |           1 |         1 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_test5     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test6     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 |          2147483647 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
-(7 rows)
+ public     | serialtest_f2_foo  |           1 |         1 |          2147483647 |            1 | f     |          1 |          3
+(11 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index a79330e780..75ffa90ce7 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -1,3 +1,18 @@
+--
+-- CREATE SEQUENCE
+--
+
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+
+ALTER SEQUENCE sequence_test4 AS bigint;  -- fail
+
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+
 ---
 --- test creation of SERIAL column
 ---
@@ -143,16 +158,13 @@ CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
 
 -- Test comments
-- 
2.11.0

From 5aff9dbe7a94417aa0faf56dab37187fcbec23f5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 25 Jan 2017 09:35:58 -0500
Subject: [PATCH v4] 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.
---
 doc/src/sgml/catalogs.sgml                  |  14 +++-
 doc/src/sgml/information_schema.sgml        |   4 +-
 doc/src/sgml/ref/alter_sequence.sgml        |  30 ++++++--
 doc/src/sgml/ref/create_sequence.sgml       |  36 ++++++----
 src/backend/catalog/information_schema.sql  |   4 +-
 src/backend/commands/sequence.c             |  89 +++++++++++++++++++++---
 src/backend/parser/gram.y                   |   6 +-
 src/backend/parser/parse_utilcmd.c          |   2 +-
 src/bin/pg_dump/pg_dump.c                   | 103 +++++++++++++++-------------
 src/bin/pg_dump/t/002_pg_dump.pl            |   2 +
 src/include/catalog/catversion.h            |   2 +-
 src/include/catalog/pg_proc.h               |   2 +-
 src/include/catalog/pg_sequence.h           |   8 ++-
 src/test/modules/test_pg_dump/t/001_base.pl |   1 +
 src/test/regress/expected/sequence.out      |  52 ++++++++++----
 src/test/regress/expected/sequence_1.out    |  52 ++++++++++----
 src/test/regress/sql/sequence.sql           |  25 +++++--
 17 files changed, 312 insertions(+), 120 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 524180e011..162975746d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5774,10 +5774,11 @@ <title><structname>pg_sequence</> Columns</title>
      </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 @@ <title><structname>pg_sequence</> Columns</title>
       <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>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c43e325d06..a3a19ce8ce 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4653,9 +4653,7 @@ <title><literal>sequences</literal> Columns</title>
       <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 3b52e875e3..252a668189 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -23,7 +23,9 @@
 
  <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 @@ <title>Parameters</title>
      </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 @@ <title>Parameters</title>
         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 @@ <title>Parameters</title>
         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 @@ <title>Compatibility</title>
 
   <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 86ff018c4b..f1448e7ab3 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,9 @@
 
  <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 @@ <title>Parameters</title>
    </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 @@ <title>Parameters</title>
       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 @@ <title>Parameters</title>
       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 @@ <title>Compatibility</title>
    <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.
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 62ee2b4e0e..9a53003ecf 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1531,8 +1531,8 @@ CREATE VIEW sequences AS
     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
            CAST(c.relname AS sql_identifier) AS sequence_name,
-           CAST('bigint' AS character_data) AS data_type,
-           CAST(64 AS cardinal_number) AS numeric_precision,
+           CAST(format_type(s.seqtypid, null) AS character_data) AS data_type,
+           CAST(_pg_numeric_precision(s.seqtypid, -1) AS cardinal_number) AS numeric_precision,
            CAST(2 AS cardinal_number) AS numeric_precision_radix,
            CAST(0 AS cardinal_number) AS numeric_scale,
            CAST(s.seqstart AS character_data) AS start_value,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 0c673f5763..cfbea6f0f8 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -33,6 +33,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
+#include "parser/parse_type.h"
 #include "storage/lmgr.h"
 #include "storage/proc.h"
 #include "storage/smgr.h"
@@ -228,12 +229,13 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
 	memset(pgs_nulls, 0, sizeof(pgs_nulls));
 
 	pgs_values[Anum_pg_sequence_seqrelid - 1] = ObjectIdGetDatum(seqoid);
-	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
+	pgs_values[Anum_pg_sequence_seqtypid - 1] = ObjectIdGetDatum(seqform.seqtypid);
 	pgs_values[Anum_pg_sequence_seqstart - 1] = Int64GetDatumFast(seqform.seqstart);
 	pgs_values[Anum_pg_sequence_seqincrement - 1] = Int64GetDatumFast(seqform.seqincrement);
 	pgs_values[Anum_pg_sequence_seqmax - 1] = Int64GetDatumFast(seqform.seqmax);
 	pgs_values[Anum_pg_sequence_seqmin - 1] = Int64GetDatumFast(seqform.seqmin);
 	pgs_values[Anum_pg_sequence_seqcache - 1] = Int64GetDatumFast(seqform.seqcache);
+	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
 
 	tuple = heap_form_tuple(tupDesc, pgs_values, pgs_nulls);
 	simple_heap_insert(rel, tuple);
@@ -623,11 +625,11 @@ nextval_internal(Oid relid)
 	if (!HeapTupleIsValid(pgstuple))
 		elog(ERROR, "cache lookup failed for sequence %u", relid);
 	pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple);
-	cycle = pgsform->seqcycle;
 	incby = pgsform->seqincrement;
 	maxv = pgsform->seqmax;
 	minv = pgsform->seqmin;
 	cache = pgsform->seqcache;
+	cycle = pgsform->seqcycle;
 	ReleaseSysCache(pgstuple);
 
 	/* lock page' buffer and read tuple */
@@ -1222,6 +1224,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 			Form_pg_sequence seqform,
 			Form_pg_sequence_data seqdataform, List **owned_by)
 {
+	DefElem    *as_type = NULL;
 	DefElem    *start_value = NULL;
 	DefElem    *restart_value = NULL;
 	DefElem    *increment_by = NULL;
@@ -1237,7 +1240,16 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	{
 		DefElem    *defel = (DefElem *) lfirst(option);
 
-		if (strcmp(defel->defname, "increment") == 0)
+		if (strcmp(defel->defname, "as") == 0)
+		{
+			if (as_type)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("conflicting or redundant options"),
+						 parser_errposition(pstate, defel->location)));
+			as_type = defel;
+		}
+		else if (strcmp(defel->defname, "increment") == 0)
 		{
 			if (increment_by)
 				ereport(ERROR,
@@ -1321,6 +1333,20 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (isInit)
 		seqdataform->log_cnt = 0;
 
+	/* AS type */
+	if (as_type != NULL)
+	{
+		seqform->seqtypid = typenameTypeId(pstate, defGetTypeName(as_type));
+		if (seqform->seqtypid != INT2OID &&
+			seqform->seqtypid != INT4OID &&
+			seqform->seqtypid != INT8OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("sequence type must be smallint, integer, or bigint")));
+	}
+	else if (isInit)
+		seqform->seqtypid = INT8OID;
+
 	/* INCREMENT BY */
 	if (increment_by != NULL)
 	{
@@ -1353,12 +1379,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	else if (isInit || max_value != NULL)
 	{
 		if (seqform->seqincrement > 0)
-			seqform->seqmax = PG_INT64_MAX;		/* ascending seq */
+		{
+			/* ascending seq */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmax = PG_INT16_MAX;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmax = PG_INT32_MAX;
+			else
+				seqform->seqmax = PG_INT64_MAX;
+		}
 		else
 			seqform->seqmax = -1;	/* descending seq */
 		seqdataform->log_cnt = 0;
 	}
 
+	if ((seqform->seqtypid == INT2OID && seqform->seqmax > PG_INT16_MAX)
+		|| (seqform->seqtypid == INT4OID && seqform->seqmax > PG_INT32_MAX)
+		|| (seqform->seqtypid == INT8OID && seqform->seqmax > PG_INT64_MAX))
+	{
+		char		bufx[100];
+
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, seqform->seqmax);
+
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MAXVALUE (%s) is too large for sequence data type %s",
+						bufx, format_type_be(seqform->seqtypid))));
+	}
+
 	/* MINVALUE (null arg means NO MINVALUE) */
 	if (min_value != NULL && min_value->arg)
 	{
@@ -1370,10 +1418,32 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		if (seqform->seqincrement > 0)
 			seqform->seqmin = 1; /* ascending seq */
 		else
-			seqform->seqmin = PG_INT64_MIN;		/* descending seq */
+		{
+			/* descending seq */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmin = PG_INT16_MIN;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmin = PG_INT32_MIN;
+			else
+				seqform->seqmin = PG_INT64_MIN;
+		}
 		seqdataform->log_cnt = 0;
 	}
 
+	if ((seqform->seqtypid == INT2OID && seqform->seqmin < PG_INT16_MIN)
+		|| (seqform->seqtypid == INT4OID && seqform->seqmin < PG_INT32_MIN)
+		|| (seqform->seqtypid == INT8OID && seqform->seqmin < PG_INT64_MIN))
+	{
+		char		bufm[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, seqform->seqmin);
+
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MINVALUE (%s) is too large for sequence data type %s",
+						bufm, format_type_be(seqform->seqtypid))));
+	}
+
 	/* crosscheck min/max */
 	if (seqform->seqmin >= seqform->seqmax)
 	{
@@ -1591,8 +1661,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
 	TupleDesc	tupdesc;
-	Datum		values[6];
-	bool		isnull[6];
+	Datum		values[7];
+	bool		isnull[7];
 	HeapTuple	pgstuple;
 	Form_pg_sequence pgsform;
 
@@ -1602,7 +1672,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 				 errmsg("permission denied for sequence %s",
 						get_rel_name(relid))));
 
-	tupdesc = CreateTemplateTupleDesc(6, false);
+	tupdesc = CreateTemplateTupleDesc(7, false);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1615,6 +1685,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 					   BOOLOID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
 					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "data_type",
+					   OIDOID, -1, 0);
 
 	BlessTupleDesc(tupdesc);
 
@@ -1631,6 +1703,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	values[3] = Int64GetDatum(pgsform->seqincrement);
 	values[4] = BoolGetDatum(pgsform->seqcycle);
 	values[5] = Int64GetDatum(pgsform->seqcache);
+	values[6] = ObjectIdGetDatum(pgsform->seqtypid);
 
 	ReleaseSysCache(pgstuple);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a8e35feccc..62c2bb6ff8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3941,7 +3941,11 @@ SeqOptList: SeqOptElem								{ $$ = list_make1($1); }
 			| SeqOptList SeqOptElem					{ $$ = lappend($1, $2); }
 		;
 
-SeqOptElem: CACHE NumericOnly
+SeqOptElem: AS SimpleTypename
+				{
+					$$ = makeDefElem("as", (Node *)$2, @1);
+				}
+			| CACHE NumericOnly
 				{
 					$$ = makeDefElem("cache", (Node *)$2, @1);
 				}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0e4e7a8c80..efb682f168 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -469,7 +469,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		 */
 		seqstmt = makeNode(CreateSeqStmt);
 		seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
-		seqstmt->options = NIL;
+		seqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(column->typeName->typeOid, -1), -1));
 
 		/*
 		 * If this is ALTER ADD COLUMN, make sure the sequence will be owned
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a6de9d7bc2..5031d2d015 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15863,39 +15863,29 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	PGresult   *res;
 	char	   *startv,
 			   *incby,
-			   *maxv = NULL,
-			   *minv = NULL,
-			   *cache;
-	char		bufm[100],
-				bufx[100];
+			   *maxv,
+			   *minv,
+			   *cache,
+			   *seqtype;
 	bool		cycled;
+	bool		is_ascending;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
 
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, PG_INT64_MIN);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
-
 	if (fout->remoteVersion >= 100000)
 	{
 		/* Make sure we are in proper schema */
 		selectSourceSchema(fout, "pg_catalog");
 
 		appendPQExpBuffer(query,
-						  "SELECT seqstart, seqincrement, "
-						  "CASE WHEN seqincrement > 0 AND seqmax = %s THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmax = -1 THEN NULL "
-						  "     ELSE seqmax "
-						  "END AS seqmax, "
-						  "CASE WHEN seqincrement > 0 AND seqmin = 1 THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmin = %s THEN NULL "
-						  "     ELSE seqmin "
-						  "END AS seqmin, "
+						  "SELECT format_type(seqtypid, NULL), "
+						  "seqstart, seqincrement, "
+						  "seqmax, seqmin, "
 						  "seqcache, seqcycle "
 						  "FROM pg_class c "
 						  "JOIN pg_sequence s ON (s.seqrelid = c.oid) "
 						  "WHERE c.oid = '%u'::oid",
-						  bufx, bufm,
 						  tbinfo->dobj.catId.oid);
 	}
 	else if (fout->remoteVersion >= 80400)
@@ -15909,17 +15899,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
 		appendPQExpBuffer(query,
-						  "SELECT start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 	else
@@ -15928,17 +15910,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
 		appendPQExpBuffer(query,
-						  "SELECT 0 AS start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "0 AS start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 
@@ -15953,14 +15927,48 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		exit_nicely(1);
 	}
 
-	startv = PQgetvalue(res, 0, 0);
-	incby = PQgetvalue(res, 0, 1);
-	if (!PQgetisnull(res, 0, 2))
-		maxv = PQgetvalue(res, 0, 2);
-	if (!PQgetisnull(res, 0, 3))
-		minv = PQgetvalue(res, 0, 3);
-	cache = PQgetvalue(res, 0, 4);
-	cycled = (strcmp(PQgetvalue(res, 0, 5), "t") == 0);
+	seqtype = PQgetvalue(res, 0, 0);
+	startv = PQgetvalue(res, 0, 1);
+	incby = PQgetvalue(res, 0, 2);
+	maxv = PQgetvalue(res, 0, 3);
+	minv = PQgetvalue(res, 0, 4);
+	cache = PQgetvalue(res, 0, 5);
+	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+
+	is_ascending = incby[0] != '-';
+
+	if (is_ascending && atoi(minv) == 1)
+		minv = NULL;
+	if (!is_ascending && atoi(maxv) == -1)
+		maxv = NULL;
+
+	if (strcmp(seqtype, "smallint") == 0)
+	{
+		if (!is_ascending && atoi(minv) == PG_INT16_MIN)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT16_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "integer") == 0)
+	{
+		if (!is_ascending && atoi(minv) == PG_INT32_MIN)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT32_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "bigint") == 0)
+	{
+		char		bufm[100],
+					bufx[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, PG_INT64_MIN);
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
+
+		if (!is_ascending && strcmp(minv, bufm) == 0)
+			minv = NULL;
+		if (is_ascending && strcmp(maxv, bufx) == 0)
+			maxv = NULL;
+	}
 
 	/*
 	 * DROP must be fully qualified in case same name appears in pg_catalog
@@ -15984,6 +15992,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 					  "CREATE SEQUENCE %s\n",
 					  fmtId(tbinfo->dobj.name));
 
+	if (strcmp(seqtype, "bigint") != 0)
+		appendPQExpBuffer(query, "    AS %s\n", seqtype);
+
 	if (fout->remoteVersion >= 80400)
 		appendPQExpBuffer(query, "    START WITH %s\n", startv);
 
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 488eec30f5..e73ecfae48 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2486,6 +2486,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
@@ -2521,6 +2522,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_third_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 417cfc36ec..0432b6f141 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201701251
+#define CATALOG_VERSION_NO	201701252
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c1f492b396..5ab96099ae 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1766,7 +1766,7 @@ DATA(insert OID = 1576 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20
 DESCR("set sequence value");
 DATA(insert OID = 1765 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ ));
 DESCR("set sequence value and is_called status");
-DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20}" "{i,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
+DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20,26}" "{i,o,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size,data_type}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
 DESCR("sequence parameters, for use by information schema");
 DATA(insert OID = 4032 ( pg_sequence_last_value		PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_	pg_sequence_last_value _null_ _null_ _null_ ));
 DESCR("sequence last value");
diff --git a/src/include/catalog/pg_sequence.h b/src/include/catalog/pg_sequence.h
index 350b286e45..ef15e68a57 100644
--- a/src/include/catalog/pg_sequence.h
+++ b/src/include/catalog/pg_sequence.h
@@ -8,23 +8,25 @@
 CATALOG(pg_sequence,2224) BKI_WITHOUT_OIDS
 {
 	Oid			seqrelid;
-	bool		seqcycle;
+	Oid			seqtypid;
 	int64		seqstart;
 	int64		seqincrement;
 	int64		seqmax;
 	int64		seqmin;
 	int64		seqcache;
+	bool		seqcycle;
 } FormData_pg_sequence;
 
 typedef FormData_pg_sequence *Form_pg_sequence;
 
-#define Natts_pg_sequence				7
+#define Natts_pg_sequence				8
 #define Anum_pg_sequence_seqrelid		1
-#define Anum_pg_sequence_seqcycle		2
+#define Anum_pg_sequence_seqtypid		2
 #define Anum_pg_sequence_seqstart		3
 #define Anum_pg_sequence_seqincrement	4
 #define Anum_pg_sequence_seqmax			5
 #define Anum_pg_sequence_seqmin			6
 #define Anum_pg_sequence_seqcache		7
+#define Anum_pg_sequence_seqcycle		8
 
 #endif	/* PG_SEQUENCE_H */
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index 70719a36a7..250f53cffe 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -226,6 +226,7 @@
 	'CREATE SEQUENCE regress_pg_dump_table_col1_seq' => {
 		regexp => qr/^
                     \QCREATE SEQUENCE regress_pg_dump_table_col1_seq\E
+                    \n\s+\QAS integer\E
                     \n\s+\QSTART WITH 1\E
                     \n\s+\QINCREMENT BY 1\E
                     \n\s+\QNO MINVALUE\E
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index a2bdd3002b..768ba3b03f 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -1,3 +1,22 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+ERROR:  type "nosuchtype" does not exist
+LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
+                                          ^
+ALTER SEQUENCE sequence_test4 AS smallint;  -- fails
+ERROR:  MAXVALUE (2147483647) is too large for sequence data type smallint
+ALTER SEQUENCE sequence_test4 AS smallint NO MINVALUE NO MAXVALUE;
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -302,37 +321,42 @@ SELECT nextval('sequence_test2');
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
  regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test4     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test6     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(7 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(11 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
  schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
 ------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
  public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
  public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
- public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | sequence_test4     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test5     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test6     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 |          2147483647 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
-(7 rows)
+ public     | serialtest_f2_foo  |           1 |         1 |          2147483647 |            1 | f     |          1 |          3
+(11 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
index 5d7ab72944..ae7d834e20 100644
--- a/src/test/regress/expected/sequence_1.out
+++ b/src/test/regress/expected/sequence_1.out
@@ -1,3 +1,22 @@
+--
+-- CREATE SEQUENCE
+--
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+ERROR:  type "nosuchtype" does not exist
+LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
+                                          ^
+ALTER SEQUENCE sequence_test4 AS smallint;  -- fails
+ERROR:  MAXVALUE (2147483647) is too large for sequence data type smallint
+ALTER SEQUENCE sequence_test4 AS smallint NO MINVALUE NO MAXVALUE;
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is too large for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is too large for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -302,37 +321,42 @@ SELECT nextval('sequence_test2');
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
  regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test4     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | sequence_test6     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1             | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(7 rows)
+ regression       | public          | serialtest_f2_foo  | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647          | 1         | NO
+(11 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
  schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
 ------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
  public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
  public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
- public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | sequence_test4     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test5     |           1 |         1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test6     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 |          2147483647 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
-(7 rows)
+ public     | serialtest_f2_foo  |           1 |         1 |          2147483647 |            1 | f     |          1 |          3
+(11 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index a79330e780..a30a4933d1 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -1,3 +1,19 @@
+--
+-- CREATE SEQUENCE
+--
+
+CREATE SEQUENCE sequence_test4 AS integer;
+CREATE SEQUENCE sequence_test5 AS smallint;
+CREATE SEQUENCE sequence_test6 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+
+ALTER SEQUENCE sequence_test4 AS smallint;  -- fails
+ALTER SEQUENCE sequence_test4 AS smallint NO MINVALUE NO MAXVALUE;
+
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+
 ---
 --- test creation of SERIAL column
 ---
@@ -143,16 +159,13 @@ CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
 
 -- Test comments
-- 
2.11.0

From 533c4dcd04ffab9b8177307039f6c9ebcd6808b9 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Fri, 27 Jan 2017 12:43:14 -0500
Subject: [PATCH] Additional test coverage for sequences

---
 src/test/regress/expected/sequence.out | 231 ++++++++++++++++++++++++++++-----
 src/test/regress/sql/sequence.sql      |  95 ++++++++++++--
 2 files changed, 286 insertions(+), 40 deletions(-)

diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index ad03a31a4e..c19b5f5ef6 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -1,3 +1,33 @@
+--
+-- CREATE SEQUENCE
+--
+-- various error cases
+CREATE UNLOGGED SEQUENCE sequence_testx;
+ERROR:  unlogged sequences are not supported
+CREATE SEQUENCE sequence_testx INCREMENT BY 0;
+ERROR:  INCREMENT must not be zero
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;
+ERROR:  MINVALUE (20) must be less than MAXVALUE (-1)
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20;
+ERROR:  MINVALUE (1) must be less than MAXVALUE (-20)
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10;
+ERROR:  START value (10) cannot be greater than MAXVALUE (-1)
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10;
+ERROR:  START value (-10) cannot be less than MINVALUE (1)
+CREATE SEQUENCE sequence_testx CACHE 0;
+ERROR:  CACHE (0) must be greater than zero
+-- OWNED BY errors
+CREATE SEQUENCE sequence_testx OWNED BY nobody;  -- nonsense word
+ERROR:  invalid OWNED BY option
+HINT:  Specify OWNED BY table.column or OWNED BY NONE.
+CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename;  -- not a table
+ERROR:  referenced relation "pg_tables" is not a table or foreign table
+CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname;  -- not same schema
+ERROR:  sequence must be in same schema as table it is linked to
+CREATE TABLE sequence_test_table (a int);
+CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
+ERROR:  column "b" of relation "sequence_test_table" does not exist
+DROP TABLE sequence_test_table;
 ---
 --- test creation of SERIAL column
 ---
@@ -242,17 +272,38 @@ DROP SEQUENCE myseq2;
 -- Alter sequence
 --
 ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
-	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
 NOTICE:  relation "sequence_test2" does not exist, skipping
+ALTER SEQUENCE pg_class CYCLE;  -- error, not a sequence
+ERROR:  "pg_class" is not a sequence
 CREATE SEQUENCE sequence_test2 START WITH 32;
+CREATE SEQUENCE sequence_test4 INCREMENT BY -1;
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+      32
+(1 row)
+
+SELECT nextval('sequence_test4');
+ nextval 
+---------
+      -1
+(1 row)
+
+ALTER SEQUENCE sequence_test2 RESTART;
 SELECT nextval('sequence_test2');
  nextval 
 ---------
       32
 (1 row)
 
+ALTER SEQUENCE sequence_test2 RESTART WITH 0;  -- error
+ERROR:  RESTART value (0) cannot be less than MINVALUE (1)
+ALTER SEQUENCE sequence_test4 RESTART WITH 40;  -- error
+ERROR:  RESTART value (40) cannot be greater than MAXVALUE (-1)
+-- test CYCLE and NO CYCLE
 ALTER SEQUENCE sequence_test2 RESTART WITH 24
-	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
 SELECT nextval('sequence_test2');
  nextval 
 ---------
@@ -277,13 +328,26 @@ SELECT nextval('sequence_test2');
       36
 (1 row)
 
-SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');  -- cycled
  nextval 
 ---------
        5
 (1 row)
 
-ALTER SEQUENCE sequence_test2 RESTART;
+ALTER SEQUENCE sequence_test2 RESTART WITH 24
+  NO CYCLE;
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+      24
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+      28
+(1 row)
+
 SELECT nextval('sequence_test2');
  nextval 
 ---------
@@ -296,45 +360,121 @@ SELECT nextval('sequence_test2');
       36
 (1 row)
 
+SELECT nextval('sequence_test2');  -- error
+ERROR:  nextval: reached maximum value of sequence "sequence_test2" (36)
+ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24
+  INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE;
 SELECT nextval('sequence_test2');
  nextval 
 ---------
-       5
+     -24
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -28
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -32
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -36
+(1 row)
+
+SELECT nextval('sequence_test2');  -- cycled
+ nextval 
+---------
+      -5
+(1 row)
+
+ALTER SEQUENCE sequence_test2 RESTART WITH -24
+  NO CYCLE;
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -24
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -28
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -32
+(1 row)
+
+SELECT nextval('sequence_test2');
+ nextval 
+---------
+     -36
+(1 row)
+
+SELECT nextval('sequence_test2');  -- error
+ERROR:  nextval: reached minimum value of sequence "sequence_test2" (-36)
+-- reset
+ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 32 START WITH 32
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+SELECT setval('sequence_test2', -100);  -- error
+ERROR:  setval: value -100 is out of bounds for sequence "sequence_test2" (5..36)
+SELECT setval('sequence_test2', 100);  -- error
+ERROR:  setval: value 100 is out of bounds for sequence "sequence_test2" (5..36)
+SELECT setval('sequence_test2', 5);
+ setval 
+--------
+      5
 (1 row)
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
- sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
-------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
- regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
- regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(7 rows)
+ sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option 
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------
+ regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5                    | 36                  | 4         | YES
+ regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test4     | bigint    |                64 |                       2 |             0 | -1          | -9223372036854775808 | -1                  | -1        | NO
+ regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest_f2_foo  | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+(9 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
- schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
-------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
- public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
- public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
- public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
-(7 rows)
+ schemaname |    sequencename    | start_value |      min_value       |      max_value      | increment_by | cycle | cache_size | last_value 
+------------+--------------------+-------------+----------------------+---------------------+--------------+-------+------------+------------
+ public     | sequence_test2     |          32 |                    5 |                  36 |            4 | t     |          1 |          5
+ public     | sequence_test3     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | sequence_test4     |          -1 | -9223372036854775808 |                  -1 |           -1 | f     |          1 |         -1
+ public     | serialtest2_f2_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f5_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f6_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest_f2_foo  |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          3
+(9 rows)
+
+SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
+ start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size 
+-------------+----------------------+---------------+-----------+--------------+------------
+          -1 | -9223372036854775808 |            -1 |        -1 | f            |          1
+(1 row)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
@@ -524,6 +664,35 @@ SELECT lastval();
 (1 row)
 
 ROLLBACK;
+-- setval
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION regress_seq_user;
+CREATE SEQUENCE seq3;
+REVOKE ALL ON seq3 FROM regress_seq_user;
+SAVEPOINT save;
+SELECT setval('seq3', 5);
+ERROR:  permission denied for sequence seq3
+ROLLBACK TO save;
+GRANT UPDATE ON seq3 TO regress_seq_user;
+SELECT setval('seq3', 5);
+ setval 
+--------
+      5
+(1 row)
+
+SELECT nextval('seq3');
+ nextval 
+---------
+       6
+(1 row)
+
+ROLLBACK;
+-- ALTER SEQUENCE
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION regress_seq_user;
+ALTER SEQUENCE sequence_test2 START WITH 1;
+ERROR:  must be owner of relation sequence_test2
+ROLLBACK;
 -- Sequences should get wiped out as well:
 DROP TABLE serialTest, serialTest2;
 -- Make sure sequences are gone:
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index df59f90f04..46c7cabb68 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -1,3 +1,24 @@
+--
+-- CREATE SEQUENCE
+--
+
+-- various error cases
+CREATE UNLOGGED SEQUENCE sequence_testx;
+CREATE SEQUENCE sequence_testx INCREMENT BY 0;
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20;
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10;
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10;
+CREATE SEQUENCE sequence_testx CACHE 0;
+
+-- OWNED BY errors
+CREATE SEQUENCE sequence_testx OWNED BY nobody;  -- nonsense word
+CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename;  -- not a table
+CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname;  -- not same schema
+CREATE TABLE sequence_test_table (a int);
+CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
+DROP TABLE sequence_test_table;
+
 ---
 --- test creation of SERIAL column
 ---
@@ -120,43 +141,80 @@ CREATE TEMP TABLE t1 (
 --
 
 ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
-	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+
+ALTER SEQUENCE pg_class CYCLE;  -- error, not a sequence
 
 CREATE SEQUENCE sequence_test2 START WITH 32;
+CREATE SEQUENCE sequence_test4 INCREMENT BY -1;
 
 SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test4');
 
+ALTER SEQUENCE sequence_test2 RESTART;
+SELECT nextval('sequence_test2');
+
+ALTER SEQUENCE sequence_test2 RESTART WITH 0;  -- error
+ALTER SEQUENCE sequence_test4 RESTART WITH 40;  -- error
+
+-- test CYCLE and NO CYCLE
 ALTER SEQUENCE sequence_test2 RESTART WITH 24
-	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');  -- cycled
+
+ALTER SEQUENCE sequence_test2 RESTART WITH 24
+  NO CYCLE;
+SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');  -- error
 
-ALTER SEQUENCE sequence_test2 RESTART;
+ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24
+  INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE;
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');  -- cycled
 
+ALTER SEQUENCE sequence_test2 RESTART WITH -24
+  NO CYCLE;
+SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');  -- error
 
+-- reset
+ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 32 START WITH 32
+  INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+
+SELECT setval('sequence_test2', -100);  -- error
+SELECT setval('sequence_test2', 100);  -- error
+SELECT setval('sequence_test2', 5);
 
 CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+SELECT * FROM information_schema.sequences
+  WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequence_name ASC;
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
-WHERE sequencename IN
-  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
-   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
   ORDER BY sequencename ASC;
 
+
+SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
+
+
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
 COMMENT ON SEQUENCE sequence_test2 IS 'will work';
@@ -264,6 +322,25 @@ CREATE SEQUENCE seq3;
 SELECT lastval();
 ROLLBACK;
 
+-- setval
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION regress_seq_user;
+CREATE SEQUENCE seq3;
+REVOKE ALL ON seq3 FROM regress_seq_user;
+SAVEPOINT save;
+SELECT setval('seq3', 5);
+ROLLBACK TO save;
+GRANT UPDATE ON seq3 TO regress_seq_user;
+SELECT setval('seq3', 5);
+SELECT nextval('seq3');
+ROLLBACK;
+
+-- ALTER SEQUENCE
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION regress_seq_user;
+ALTER SEQUENCE sequence_test2 START WITH 1;
+ROLLBACK;
+
 -- Sequences should get wiped out as well:
 DROP TABLE serialTest, serialTest2;
 
-- 
2.11.0

From ce2680ef0bbbb72a9a4dc2cb879a70610d71ad24 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Fri, 27 Jan 2017 23:52:53 -0500
Subject: [PATCH v5] 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.
---
 doc/src/sgml/catalogs.sgml                  |  14 +++-
 doc/src/sgml/information_schema.sgml        |   4 +-
 doc/src/sgml/ref/alter_sequence.sgml        |  30 ++++++--
 doc/src/sgml/ref/create_sequence.sgml       |  36 ++++++----
 src/backend/catalog/information_schema.sql  |   4 +-
 src/backend/commands/sequence.c             |  89 +++++++++++++++++++++---
 src/backend/parser/gram.y                   |   6 +-
 src/backend/parser/parse_utilcmd.c          |   2 +-
 src/bin/pg_dump/pg_dump.c                   | 103 +++++++++++++++-------------
 src/bin/pg_dump/t/002_pg_dump.pl            |   2 +
 src/include/catalog/catversion.h            |   2 +-
 src/include/catalog/pg_proc.h               |   2 +-
 src/include/catalog/pg_sequence.h           |   8 ++-
 src/test/modules/test_pg_dump/t/001_base.pl |   1 +
 src/test/regress/expected/sequence.out      |  49 +++++++++----
 src/test/regress/sql/sequence.sql           |  13 ++++
 16 files changed, 266 insertions(+), 99 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 086fafc694..f57937a17a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5774,10 +5774,11 @@ <title><structname>pg_sequence</> Columns</title>
      </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 @@ <title><structname>pg_sequence</> Columns</title>
       <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>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c43e325d06..a3a19ce8ce 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4653,9 +4653,7 @@ <title><literal>sequences</literal> Columns</title>
       <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 3b52e875e3..252a668189 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -23,7 +23,9 @@
 
  <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 @@ <title>Parameters</title>
      </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 @@ <title>Parameters</title>
         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 @@ <title>Parameters</title>
         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 @@ <title>Compatibility</title>
 
   <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 86ff018c4b..f1448e7ab3 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,9 @@
 
  <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 @@ <title>Parameters</title>
    </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 @@ <title>Parameters</title>
       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 @@ <title>Parameters</title>
       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 @@ <title>Compatibility</title>
    <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.
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 62ee2b4e0e..9a53003ecf 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1531,8 +1531,8 @@ CREATE VIEW sequences AS
     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
            CAST(c.relname AS sql_identifier) AS sequence_name,
-           CAST('bigint' AS character_data) AS data_type,
-           CAST(64 AS cardinal_number) AS numeric_precision,
+           CAST(format_type(s.seqtypid, null) AS character_data) AS data_type,
+           CAST(_pg_numeric_precision(s.seqtypid, -1) AS cardinal_number) AS numeric_precision,
            CAST(2 AS cardinal_number) AS numeric_precision_radix,
            CAST(0 AS cardinal_number) AS numeric_scale,
            CAST(s.seqstart AS character_data) AS start_value,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 0c673f5763..624c62b427 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -33,6 +33,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
+#include "parser/parse_type.h"
 #include "storage/lmgr.h"
 #include "storage/proc.h"
 #include "storage/smgr.h"
@@ -228,12 +229,13 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
 	memset(pgs_nulls, 0, sizeof(pgs_nulls));
 
 	pgs_values[Anum_pg_sequence_seqrelid - 1] = ObjectIdGetDatum(seqoid);
-	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
+	pgs_values[Anum_pg_sequence_seqtypid - 1] = ObjectIdGetDatum(seqform.seqtypid);
 	pgs_values[Anum_pg_sequence_seqstart - 1] = Int64GetDatumFast(seqform.seqstart);
 	pgs_values[Anum_pg_sequence_seqincrement - 1] = Int64GetDatumFast(seqform.seqincrement);
 	pgs_values[Anum_pg_sequence_seqmax - 1] = Int64GetDatumFast(seqform.seqmax);
 	pgs_values[Anum_pg_sequence_seqmin - 1] = Int64GetDatumFast(seqform.seqmin);
 	pgs_values[Anum_pg_sequence_seqcache - 1] = Int64GetDatumFast(seqform.seqcache);
+	pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
 
 	tuple = heap_form_tuple(tupDesc, pgs_values, pgs_nulls);
 	simple_heap_insert(rel, tuple);
@@ -623,11 +625,11 @@ nextval_internal(Oid relid)
 	if (!HeapTupleIsValid(pgstuple))
 		elog(ERROR, "cache lookup failed for sequence %u", relid);
 	pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple);
-	cycle = pgsform->seqcycle;
 	incby = pgsform->seqincrement;
 	maxv = pgsform->seqmax;
 	minv = pgsform->seqmin;
 	cache = pgsform->seqcache;
+	cycle = pgsform->seqcycle;
 	ReleaseSysCache(pgstuple);
 
 	/* lock page' buffer and read tuple */
@@ -1222,6 +1224,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 			Form_pg_sequence seqform,
 			Form_pg_sequence_data seqdataform, List **owned_by)
 {
+	DefElem    *as_type = NULL;
 	DefElem    *start_value = NULL;
 	DefElem    *restart_value = NULL;
 	DefElem    *increment_by = NULL;
@@ -1237,7 +1240,16 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	{
 		DefElem    *defel = (DefElem *) lfirst(option);
 
-		if (strcmp(defel->defname, "increment") == 0)
+		if (strcmp(defel->defname, "as") == 0)
+		{
+			if (as_type)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("conflicting or redundant options"),
+						 parser_errposition(pstate, defel->location)));
+			as_type = defel;
+		}
+		else if (strcmp(defel->defname, "increment") == 0)
 		{
 			if (increment_by)
 				ereport(ERROR,
@@ -1321,6 +1333,20 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	if (isInit)
 		seqdataform->log_cnt = 0;
 
+	/* AS type */
+	if (as_type != NULL)
+	{
+		seqform->seqtypid = typenameTypeId(pstate, defGetTypeName(as_type));
+		if (seqform->seqtypid != INT2OID &&
+			seqform->seqtypid != INT4OID &&
+			seqform->seqtypid != INT8OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("sequence type must be smallint, integer, or bigint")));
+	}
+	else if (isInit)
+		seqform->seqtypid = INT8OID;
+
 	/* INCREMENT BY */
 	if (increment_by != NULL)
 	{
@@ -1353,12 +1379,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	else if (isInit || max_value != NULL)
 	{
 		if (seqform->seqincrement > 0)
-			seqform->seqmax = PG_INT64_MAX;		/* ascending seq */
+		{
+			/* ascending seq */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmax = PG_INT16_MAX;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmax = PG_INT32_MAX;
+			else
+				seqform->seqmax = PG_INT64_MAX;
+		}
 		else
 			seqform->seqmax = -1;	/* descending seq */
 		seqdataform->log_cnt = 0;
 	}
 
+	if ((seqform->seqtypid == INT2OID && (seqform->seqmax < PG_INT16_MIN || seqform->seqmax > PG_INT16_MAX))
+		|| (seqform->seqtypid == INT4OID && (seqform->seqmax < PG_INT32_MIN || seqform->seqmax > PG_INT32_MAX))
+		|| (seqform->seqtypid == INT8OID && (seqform->seqmax < PG_INT64_MIN || seqform->seqmax > PG_INT64_MAX)))
+	{
+		char		bufx[100];
+
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, seqform->seqmax);
+
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MAXVALUE (%s) is out of range for sequence data type %s",
+						bufx, format_type_be(seqform->seqtypid))));
+	}
+
 	/* MINVALUE (null arg means NO MINVALUE) */
 	if (min_value != NULL && min_value->arg)
 	{
@@ -1370,10 +1418,32 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		if (seqform->seqincrement > 0)
 			seqform->seqmin = 1; /* ascending seq */
 		else
-			seqform->seqmin = PG_INT64_MIN;		/* descending seq */
+		{
+			/* descending seq */
+			if (seqform->seqtypid == INT2OID)
+				seqform->seqmin = PG_INT16_MIN;
+			else if (seqform->seqtypid == INT4OID)
+				seqform->seqmin = PG_INT32_MIN;
+			else
+				seqform->seqmin = PG_INT64_MIN;
+		}
 		seqdataform->log_cnt = 0;
 	}
 
+	if ((seqform->seqtypid == INT2OID && (seqform->seqmin < PG_INT16_MIN || seqform->seqmin > PG_INT16_MAX))
+		|| (seqform->seqtypid == INT4OID && (seqform->seqmin < PG_INT32_MIN || seqform->seqmin > PG_INT32_MAX))
+		|| (seqform->seqtypid == INT8OID && (seqform->seqmin < PG_INT64_MIN || seqform->seqmin > PG_INT64_MAX)))
+	{
+		char		bufm[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, seqform->seqmin);
+
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("MINVALUE (%s) is out of range for sequence data type %s",
+						bufm, format_type_be(seqform->seqtypid))));
+	}
+
 	/* crosscheck min/max */
 	if (seqform->seqmin >= seqform->seqmax)
 	{
@@ -1591,8 +1661,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
 	TupleDesc	tupdesc;
-	Datum		values[6];
-	bool		isnull[6];
+	Datum		values[7];
+	bool		isnull[7];
 	HeapTuple	pgstuple;
 	Form_pg_sequence pgsform;
 
@@ -1602,7 +1672,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 				 errmsg("permission denied for sequence %s",
 						get_rel_name(relid))));
 
-	tupdesc = CreateTemplateTupleDesc(6, false);
+	tupdesc = CreateTemplateTupleDesc(7, false);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1615,6 +1685,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 					   BOOLOID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
 					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "data_type",
+					   OIDOID, -1, 0);
 
 	BlessTupleDesc(tupdesc);
 
@@ -1631,6 +1703,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
 	values[3] = Int64GetDatum(pgsform->seqincrement);
 	values[4] = BoolGetDatum(pgsform->seqcycle);
 	values[5] = Int64GetDatum(pgsform->seqcache);
+	values[6] = ObjectIdGetDatum(pgsform->seqtypid);
 
 	ReleaseSysCache(pgstuple);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a4edea08a3..09ac4572ab 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3941,7 +3941,11 @@ SeqOptList: SeqOptElem								{ $$ = list_make1($1); }
 			| SeqOptList SeqOptElem					{ $$ = lappend($1, $2); }
 		;
 
-SeqOptElem: CACHE NumericOnly
+SeqOptElem: AS SimpleTypename
+				{
+					$$ = makeDefElem("as", (Node *)$2, @1);
+				}
+			| CACHE NumericOnly
 				{
 					$$ = makeDefElem("cache", (Node *)$2, @1);
 				}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0e4e7a8c80..efb682f168 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -469,7 +469,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		 */
 		seqstmt = makeNode(CreateSeqStmt);
 		seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
-		seqstmt->options = NIL;
+		seqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(column->typeName->typeOid, -1), -1));
 
 		/*
 		 * If this is ALTER ADD COLUMN, make sure the sequence will be owned
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a6de9d7bc2..5031d2d015 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15863,39 +15863,29 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	PGresult   *res;
 	char	   *startv,
 			   *incby,
-			   *maxv = NULL,
-			   *minv = NULL,
-			   *cache;
-	char		bufm[100],
-				bufx[100];
+			   *maxv,
+			   *minv,
+			   *cache,
+			   *seqtype;
 	bool		cycled;
+	bool		is_ascending;
 	PQExpBuffer query = createPQExpBuffer();
 	PQExpBuffer delqry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
 
-	snprintf(bufm, sizeof(bufm), INT64_FORMAT, PG_INT64_MIN);
-	snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
-
 	if (fout->remoteVersion >= 100000)
 	{
 		/* Make sure we are in proper schema */
 		selectSourceSchema(fout, "pg_catalog");
 
 		appendPQExpBuffer(query,
-						  "SELECT seqstart, seqincrement, "
-						  "CASE WHEN seqincrement > 0 AND seqmax = %s THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmax = -1 THEN NULL "
-						  "     ELSE seqmax "
-						  "END AS seqmax, "
-						  "CASE WHEN seqincrement > 0 AND seqmin = 1 THEN NULL "
-						  "     WHEN seqincrement < 0 AND seqmin = %s THEN NULL "
-						  "     ELSE seqmin "
-						  "END AS seqmin, "
+						  "SELECT format_type(seqtypid, NULL), "
+						  "seqstart, seqincrement, "
+						  "seqmax, seqmin, "
 						  "seqcache, seqcycle "
 						  "FROM pg_class c "
 						  "JOIN pg_sequence s ON (s.seqrelid = c.oid) "
 						  "WHERE c.oid = '%u'::oid",
-						  bufx, bufm,
 						  tbinfo->dobj.catId.oid);
 	}
 	else if (fout->remoteVersion >= 80400)
@@ -15909,17 +15899,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
 		appendPQExpBuffer(query,
-						  "SELECT start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 	else
@@ -15928,17 +15910,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
 
 		appendPQExpBuffer(query,
-						  "SELECT 0 AS start_value, increment_by, "
-				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-				   "     WHEN increment_by < 0 AND max_value = -1 THEN NULL "
-						  "     ELSE max_value "
-						  "END AS max_value, "
-					"CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
-				   "     WHEN increment_by < 0 AND min_value = %s THEN NULL "
-						  "     ELSE min_value "
-						  "END AS min_value, "
+						  "SELECT 'bigint'::name AS sequence_type, "
+						  "0 AS start_value, increment_by, max_value, min_value, "
 						  "cache_value, is_cycled FROM %s",
-						  bufx, bufm,
 						  fmtId(tbinfo->dobj.name));
 	}
 
@@ -15953,14 +15927,48 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 		exit_nicely(1);
 	}
 
-	startv = PQgetvalue(res, 0, 0);
-	incby = PQgetvalue(res, 0, 1);
-	if (!PQgetisnull(res, 0, 2))
-		maxv = PQgetvalue(res, 0, 2);
-	if (!PQgetisnull(res, 0, 3))
-		minv = PQgetvalue(res, 0, 3);
-	cache = PQgetvalue(res, 0, 4);
-	cycled = (strcmp(PQgetvalue(res, 0, 5), "t") == 0);
+	seqtype = PQgetvalue(res, 0, 0);
+	startv = PQgetvalue(res, 0, 1);
+	incby = PQgetvalue(res, 0, 2);
+	maxv = PQgetvalue(res, 0, 3);
+	minv = PQgetvalue(res, 0, 4);
+	cache = PQgetvalue(res, 0, 5);
+	cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+
+	is_ascending = incby[0] != '-';
+
+	if (is_ascending && atoi(minv) == 1)
+		minv = NULL;
+	if (!is_ascending && atoi(maxv) == -1)
+		maxv = NULL;
+
+	if (strcmp(seqtype, "smallint") == 0)
+	{
+		if (!is_ascending && atoi(minv) == PG_INT16_MIN)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT16_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "integer") == 0)
+	{
+		if (!is_ascending && atoi(minv) == PG_INT32_MIN)
+			minv = NULL;
+		if (is_ascending && atoi(maxv) == PG_INT32_MAX)
+			maxv = NULL;
+	}
+	else if (strcmp(seqtype, "bigint") == 0)
+	{
+		char		bufm[100],
+					bufx[100];
+
+		snprintf(bufm, sizeof(bufm), INT64_FORMAT, PG_INT64_MIN);
+		snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
+
+		if (!is_ascending && strcmp(minv, bufm) == 0)
+			minv = NULL;
+		if (is_ascending && strcmp(maxv, bufx) == 0)
+			maxv = NULL;
+	}
 
 	/*
 	 * DROP must be fully qualified in case same name appears in pg_catalog
@@ -15984,6 +15992,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 					  "CREATE SEQUENCE %s\n",
 					  fmtId(tbinfo->dobj.name));
 
+	if (strcmp(seqtype, "bigint") != 0)
+		appendPQExpBuffer(query, "    AS %s\n", seqtype);
+
 	if (fout->remoteVersion >= 80400)
 		appendPQExpBuffer(query, "    START WITH %s\n", startv);
 
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 488eec30f5..e73ecfae48 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2486,6 +2486,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
@@ -2521,6 +2522,7 @@
 		catch_all => 'CREATE ... commands',
 		regexp => qr/^
 			\QCREATE SEQUENCE test_third_table_col1_seq\E
+			\n\s+\QAS integer\E
 			\n\s+\QSTART WITH 1\E
 			\n\s+\QINCREMENT BY 1\E
 			\n\s+\QNO MINVALUE\E
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 417cfc36ec..0432b6f141 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201701251
+#define CATALOG_VERSION_NO	201701252
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 31c828a3f2..874f1a9dcf 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1766,7 +1766,7 @@ DATA(insert OID = 1576 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20
 DESCR("set sequence value");
 DATA(insert OID = 1765 (  setval			PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ ));
 DESCR("set sequence value and is_called status");
-DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20}" "{i,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
+DATA(insert OID = 3078 (  pg_sequence_parameters	PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20,26}" "{i,o,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size,data_type}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
 DESCR("sequence parameters, for use by information schema");
 DATA(insert OID = 4032 ( pg_sequence_last_value		PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_	pg_sequence_last_value _null_ _null_ _null_ ));
 DESCR("sequence last value");
diff --git a/src/include/catalog/pg_sequence.h b/src/include/catalog/pg_sequence.h
index 350b286e45..ef15e68a57 100644
--- a/src/include/catalog/pg_sequence.h
+++ b/src/include/catalog/pg_sequence.h
@@ -8,23 +8,25 @@
 CATALOG(pg_sequence,2224) BKI_WITHOUT_OIDS
 {
 	Oid			seqrelid;
-	bool		seqcycle;
+	Oid			seqtypid;
 	int64		seqstart;
 	int64		seqincrement;
 	int64		seqmax;
 	int64		seqmin;
 	int64		seqcache;
+	bool		seqcycle;
 } FormData_pg_sequence;
 
 typedef FormData_pg_sequence *Form_pg_sequence;
 
-#define Natts_pg_sequence				7
+#define Natts_pg_sequence				8
 #define Anum_pg_sequence_seqrelid		1
-#define Anum_pg_sequence_seqcycle		2
+#define Anum_pg_sequence_seqtypid		2
 #define Anum_pg_sequence_seqstart		3
 #define Anum_pg_sequence_seqincrement	4
 #define Anum_pg_sequence_seqmax			5
 #define Anum_pg_sequence_seqmin			6
 #define Anum_pg_sequence_seqcache		7
+#define Anum_pg_sequence_seqcycle		8
 
 #endif	/* PG_SEQUENCE_H */
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index c8e8d4a94c..bb2a65782a 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -305,6 +305,7 @@
 	'CREATE SEQUENCE regress_pg_dump_table_col1_seq' => {
 		regexp => qr/^
                     \QCREATE SEQUENCE regress_pg_dump_table_col1_seq\E
+                    \n\s+\QAS integer\E
                     \n\s+\QSTART WITH 1\E
                     \n\s+\QINCREMENT BY 1\E
                     \n\s+\QNO MINVALUE\E
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index d062e91d26..f339489151 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -28,6 +28,23 @@ CREATE TABLE sequence_test_table (a int);
 CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
 ERROR:  column "b" of relation "sequence_test_table" does not exist
 DROP TABLE sequence_test_table;
+-- sequence data types
+CREATE SEQUENCE sequence_test5 AS integer;
+CREATE SEQUENCE sequence_test6 AS smallint;
+CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+ERROR:  sequence type must be smallint, integer, or bigint
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+ERROR:  type "nosuchtype" does not exist
+LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
+                                          ^
+ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
+ERROR:  MAXVALUE (2147483647) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+ERROR:  MINVALUE (-100000) is out of range for sequence data type smallint
 ---
 --- test creation of SERIAL column
 ---
@@ -445,13 +462,16 @@ SELECT * FROM information_schema.sequences
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5                    | 36                  | 4         | YES
  regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  regression       | public          | sequence_test4     | bigint    |                64 |                       2 |             0 | -1          | -9223372036854775808 | -1                  | -1        | NO
- regression       | public          | serialtest1_f2_foo | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
- regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
+ regression       | public          | sequence_test6     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
+ regression       | public          | sequence_test7     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | serialtest1_f2_foo | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
+ regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
+ regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
-(9 rows)
+(12 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
@@ -462,18 +482,21 @@ WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
  public     | sequence_test2     |          32 |                    5 |                  36 |            4 | t     |          1 |          5
  public     | sequence_test3     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
  public     | sequence_test4     |          -1 | -9223372036854775808 |                  -1 |           -1 | f     |          1 |         -1
- public     | serialtest1_f2_foo |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          3
- public     | serialtest2_f2_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f3_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
- public     | serialtest2_f4_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | sequence_test5     |           1 |                    1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test6     |           1 |                    1 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test7     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest1_f2_foo |           1 |                    1 |          2147483647 |            1 | f     |          1 |          3
+ public     | serialtest2_f2_seq |           1 |                    1 |          2147483647 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
-(9 rows)
+(12 rows)
 
 SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
- start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size 
--------------+----------------------+---------------+-----------+--------------+------------
-          -1 | -9223372036854775808 |            -1 |        -1 | f            |          1
+ start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size | data_type 
+-------------+----------------------+---------------+-----------+--------------+------------+-----------
+          -1 | -9223372036854775808 |            -1 |        -1 | f            |          1 |        20
 (1 row)
 
 -- Test comments
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 4b9824c3cc..0fbd255967 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -19,6 +19,19 @@ CREATE TABLE sequence_test_table (a int);
 CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
 DROP TABLE sequence_test_table;
 
+-- sequence data types
+CREATE SEQUENCE sequence_test5 AS integer;
+CREATE SEQUENCE sequence_test6 AS smallint;
+CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_testx AS text;
+CREATE SEQUENCE sequence_testx AS nosuchtype;
+
+ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
+ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
+
+CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
+CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
+
 ---
 --- test creation of SERIAL column
 ---
-- 
2.11.0

From 87ccaf77e293629f3d3f580be2b6146a9926b792 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 29 Mar 2017 13:15:08 -0400
Subject: [PATCH] Adjust min/max values when changing sequence type

When changing the type of a sequence, adjust the min/max values of the
sequence if it looks like the previous values were the default values.
Previously, it would leave the old values in place, requiring manual
adjustments even in the usual/default cases.
---
 doc/src/sgml/ref/alter_sequence.sgml   | 13 +++++++++----
 src/backend/commands/sequence.c        | 35 ++++++++++++++++++++++++++++------
 src/test/regress/expected/sequence.out | 14 +++++++++-----
 src/test/regress/sql/sequence.sql      |  8 +++++---
 4 files changed, 52 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
index 252a668189..5c912ab892 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -94,10 +94,15 @@ <title>Parameters</title>
        </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.
+        Changing the data type automatically changes the minimum and maximum
+        values of the sequence if and only if the previous minimum and maximum
+        values were the minimum or maximum value of the old data type (in
+        other words, if the sequence had been created using <literal>NO
+        MINVALUE</literal> or <literal>NO MAXVALUE</literal>, implicitly or
+        explicitly).  Otherwise, the minimum and maximum values are preserved,
+        unless new values are given as part of the same command.  If the
+        minimum and maximum values do not fit into the new data type, an error
+        will be generated.
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index d547db714e..3f0e9d550f 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1232,6 +1232,8 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	DefElem    *cache_value = NULL;
 	DefElem    *is_cycled = NULL;
 	ListCell   *option;
+	bool		reset_max_value = false;
+	bool		reset_min_value = false;
 
 	*owned_by = NIL;
 
@@ -1335,13 +1337,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	/* AS type */
 	if (as_type != NULL)
 	{
-		seqform->seqtypid = typenameTypeId(pstate, defGetTypeName(as_type));
-		if (seqform->seqtypid != INT2OID &&
-			seqform->seqtypid != INT4OID &&
-			seqform->seqtypid != INT8OID)
+		Oid		newtypid = typenameTypeId(pstate, defGetTypeName(as_type));
+
+		if (newtypid != INT2OID &&
+			newtypid != INT4OID &&
+			newtypid != INT8OID)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("sequence type must be smallint, integer, or bigint")));
+
+		if (!isInit)
+		{
+			/*
+			 * When changing type and the old sequence min/max values were the
+			 * min/max of the old type, adjust sequence min/max values to
+			 * min/max of new type.  (Otherwise, the user chose explicit
+			 * min/max values, which we'll leave alone.)
+			 */
+			if ((seqform->seqtypid == INT2OID && seqform->seqmax == PG_INT16_MAX) ||
+				(seqform->seqtypid == INT4OID && seqform->seqmax == PG_INT32_MAX) ||
+				(seqform->seqtypid == INT8OID && seqform->seqmax == PG_INT64_MAX))
+				reset_max_value = true;
+			if ((seqform->seqtypid == INT2OID && seqform->seqmin == PG_INT16_MIN) ||
+				(seqform->seqtypid == INT4OID && seqform->seqmin == PG_INT32_MIN) ||
+				(seqform->seqtypid == INT8OID && seqform->seqmin == PG_INT64_MIN))
+				reset_min_value = true;
+		}
+
+		seqform->seqtypid = newtypid;
 	}
 	else if (isInit)
 		seqform->seqtypid = INT8OID;
@@ -1375,7 +1398,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		seqform->seqmax = defGetInt64(max_value);
 		seqdataform->log_cnt = 0;
 	}
-	else if (isInit || max_value != NULL)
+	else if (isInit || max_value != NULL || reset_max_value)
 	{
 		if (seqform->seqincrement > 0)
 		{
@@ -1412,7 +1435,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 		seqform->seqmin = defGetInt64(min_value);
 		seqdataform->log_cnt = 0;
 	}
-	else if (isInit || min_value != NULL)
+	else if (isInit || min_value != NULL || reset_min_value)
 	{
 		if (seqform->seqincrement > 0)
 			seqform->seqmin = 1; /* ascending seq */
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index f339489151..a5de1f32e6 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -32,19 +32,21 @@ DROP TABLE sequence_test_table;
 CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
 CREATE SEQUENCE sequence_testx AS text;
 ERROR:  sequence type must be smallint, integer, or bigint
 CREATE SEQUENCE sequence_testx AS nosuchtype;
 ERROR:  type "nosuchtype" does not exist
 LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
                                           ^
-ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
-ERROR:  MAXVALUE (2147483647) is out of range for sequence data type smallint
-ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
 CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
 ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
 CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
 ERROR:  MINVALUE (-100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
+ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
 ---
 --- test creation of SERIAL column
 ---
@@ -465,13 +467,14 @@ SELECT * FROM information_schema.sequences
  regression       | public          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | sequence_test6     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | sequence_test7     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test8     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 20000               | 1         | NO
  regression       | public          | serialtest1_f2_foo | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
-(12 rows)
+(13 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
@@ -485,13 +488,14 @@ WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
  public     | sequence_test5     |           1 |                    1 |               32767 |            1 | f     |          1 |           
  public     | sequence_test6     |           1 |                    1 |               32767 |            1 | f     |          1 |           
  public     | sequence_test7     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | sequence_test8     |           1 |                    1 |               20000 |            1 | f     |          1 |           
  public     | serialtest1_f2_foo |           1 |                    1 |          2147483647 |            1 | f     |          1 |          3
  public     | serialtest2_f2_seq |           1 |                    1 |          2147483647 |            1 | f     |          1 |          2
  public     | serialtest2_f3_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f4_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
-(12 rows)
+(13 rows)
 
 SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
  start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size | data_type 
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 0fbd255967..8f8d54bf77 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -23,15 +23,17 @@ CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
 CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
 CREATE SEQUENCE sequence_testx AS text;
 CREATE SEQUENCE sequence_testx AS nosuchtype;
 
-ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
-ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
-
 CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
 CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
 
+ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
+
 ---
 --- test creation of SERIAL column
 ---
-- 
2.12.2

From 03db510539b4f0ebc4fea35dd07bcb7630198e5c Mon Sep 17 00:00:00 2001
From: Vitaly Burovoy <vitaly.burovoy@gmail.com>
Date: Fri, 31 Mar 2017 02:25:23 +0000
Subject: [PATCH 2/2] Fix overriding max/min value of a sequence to +-1 on
 "ALTER...AS type"

If the max/min value match the bounds of the old type and neither
MAXVALUE/MINVALUE nor NO MAXVALUE/NO MINVALUE are set, change values to the
bounds of the new type, not treat it as NO MAXVALUE/NO MINVALUE.
---
 src/backend/commands/sequence.c        |  4 ++--
 src/test/regress/expected/sequence.out | 41 ++++++++++++++++++++++++++++++++--
 src/test/regress/sql/sequence.sql      | 21 +++++++++++++++++
 3 files changed, 62 insertions(+), 4 deletions(-)

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 3f0e9d5..c2ee7d6 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1400,7 +1400,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	}
 	else if (isInit || max_value != NULL || reset_max_value)
 	{
-		if (seqform->seqincrement > 0)
+		if (seqform->seqincrement > 0 || reset_max_value)
 		{
 			/* ascending seq */
 			if (seqform->seqtypid == INT2OID)
@@ -1437,7 +1437,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
 	}
 	else if (isInit || min_value != NULL || reset_min_value)
 	{
-		if (seqform->seqincrement > 0)
+		if (seqform->seqincrement > 0 && !reset_min_value)
 			seqform->seqmin = 1; /* ascending seq */
 		else
 		{
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index a5de1f3..7464d7c 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -33,6 +33,11 @@ CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
 CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
+CREATE SEQUENCE sequence_test9 AS integer MINVALUE -100000 START 1;
+CREATE SEQUENCE sequence_testa AS smallint;
+CREATE SEQUENCE sequence_testb AS smallint INCREMENT -1;
+CREATE SEQUENCE sequence_testc AS smallint MINVALUE -32768;
+CREATE SEQUENCE sequence_testd AS smallint MAXVALUE 32767 INCREMENT -1;
 CREATE SEQUENCE sequence_testx AS text;
 ERROR:  sequence type must be smallint, integer, or bigint
 CREATE SEQUENCE sequence_testx AS nosuchtype;
@@ -47,6 +52,28 @@ ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
 ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
 ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
 ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
+ALTER SEQUENCE sequence_test9 AS smallint;  -- fail, min has to be adjusted
+ERROR:  MINVALUE (-100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test9 AS smallint MINVALUE -20000;  -- ok now
+ALTER SEQUENCE sequence_testa AS int;  -- enlarge a single bound
+ALTER SEQUENCE sequence_testb AS int;  -- enlarge a single bound
+ALTER SEQUENCE sequence_testc AS int;  -- enlarge both bounds
+ALTER SEQUENCE sequence_testd AS int;  -- enlarge both bounds
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN('sequence_testa', 'sequence_testb', 'sequence_testc', 'sequence_testd')
+  ORDER BY sequencename ASC;
+ schemaname |  sequencename  | start_value |  min_value  | max_value  | increment_by | cycle | cache_size | last_value 
+------------+----------------+-------------+-------------+------------+--------------+-------+------------+------------
+ public     | sequence_testa |           1 |           1 | 2147483647 |            1 | f     |          1 |           
+ public     | sequence_testb |          -1 | -2147483648 |         -1 |           -1 | f     |          1 |           
+ public     | sequence_testc |      -32768 | -2147483648 | 2147483647 |            1 | f     |          1 |           
+ public     | sequence_testd |       32767 | -2147483648 | 2147483647 |           -1 | f     |          1 |           
+(4 rows)
+
+ALTER SEQUENCE sequence_testa AS int;  -- shrink a single bound
+ALTER SEQUENCE sequence_testb AS int;  -- shrink both bounds
+ALTER SEQUENCE sequence_testc AS int;  -- shrink both bounds
 ---
 --- test creation of SERIAL column
 ---
@@ -468,13 +495,18 @@ SELECT * FROM information_schema.sequences
  regression       | public          | sequence_test6     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | sequence_test7     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  regression       | public          | sequence_test8     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 20000               | 1         | NO
+ regression       | public          | sequence_test9     | smallint  |                16 |                       2 |             0 | 1           | -20000               | 32767               | 1         | NO
+ regression       | public          | sequence_testa     | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
+ regression       | public          | sequence_testb     | integer   |                32 |                       2 |             0 | -1          | -2147483648          | -1                  | -1        | NO
+ regression       | public          | sequence_testc     | integer   |                32 |                       2 |             0 | -32768      | -2147483648          | 2147483647          | 1         | NO
+ regression       | public          | sequence_testd     | integer   |                32 |                       2 |             0 | 32767       | -2147483648          | 2147483647          | -1        | NO
  regression       | public          | serialtest1_f2_foo | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
-(13 rows)
+(18 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
@@ -489,13 +521,18 @@ WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
  public     | sequence_test6     |           1 |                    1 |               32767 |            1 | f     |          1 |           
  public     | sequence_test7     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
  public     | sequence_test8     |           1 |                    1 |               20000 |            1 | f     |          1 |           
+ public     | sequence_test9     |           1 |               -20000 |               32767 |            1 | f     |          1 |           
+ public     | sequence_testa     |           1 |                    1 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_testb     |          -1 |          -2147483648 |                  -1 |           -1 | f     |          1 |           
+ public     | sequence_testc     |      -32768 |          -2147483648 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_testd     |       32767 |          -2147483648 |          2147483647 |           -1 | f     |          1 |           
  public     | serialtest1_f2_foo |           1 |                    1 |          2147483647 |            1 | f     |          1 |          3
  public     | serialtest2_f2_seq |           1 |                    1 |          2147483647 |            1 | f     |          1 |          2
  public     | serialtest2_f3_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f4_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f5_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
  public     | serialtest2_f6_seq |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |          2
-(13 rows)
+(18 rows)
 
 SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
  start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size | data_type 
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 8f8d54b..1d04f65 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -24,6 +24,11 @@ CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
 CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
+CREATE SEQUENCE sequence_test9 AS integer MINVALUE -100000 START 1;
+CREATE SEQUENCE sequence_testa AS smallint;
+CREATE SEQUENCE sequence_testb AS smallint INCREMENT -1;
+CREATE SEQUENCE sequence_testc AS smallint MINVALUE -32768;
+CREATE SEQUENCE sequence_testd AS smallint MAXVALUE 32767 INCREMENT -1;
 CREATE SEQUENCE sequence_testx AS text;
 CREATE SEQUENCE sequence_testx AS nosuchtype;
 
@@ -33,6 +38,22 @@ CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
 ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
 ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
 ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
+ALTER SEQUENCE sequence_test9 AS smallint;  -- fail, min has to be adjusted
+ALTER SEQUENCE sequence_test9 AS smallint MINVALUE -20000;  -- ok now
+
+ALTER SEQUENCE sequence_testa AS int;  -- enlarge a single bound
+ALTER SEQUENCE sequence_testb AS int;  -- enlarge a single bound
+ALTER SEQUENCE sequence_testc AS int;  -- enlarge both bounds
+ALTER SEQUENCE sequence_testd AS int;  -- enlarge both bounds
+
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN('sequence_testa', 'sequence_testb', 'sequence_testc', 'sequence_testd')
+  ORDER BY sequencename ASC;
+
+ALTER SEQUENCE sequence_testa AS int;  -- shrink a single bound
+ALTER SEQUENCE sequence_testb AS int;  -- shrink both bounds
+ALTER SEQUENCE sequence_testc AS int;  -- shrink both bounds
 
 ---
 --- test creation of SERIAL column
-- 
2.7.3