Add CREATE SEQUENCE AS <data type> clause

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

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

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

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

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

5 files changed, 90 insertions, 12 deletions

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 62ee2b4e0ee..9a53003ecff 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/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b4c24251798..38be9cf1a0c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -169,6 +169,7 @@ CREATE OR REPLACE VIEW pg_sequences AS
         N.nspname AS schemaname,
         C.relname AS sequencename,
         pg_get_userbyid(C.relowner) AS sequenceowner,
+        S.seqtypid::regtype AS data_type,
         S.seqstart AS start_value,
         S.seqmin AS min_value,
         S.seqmax AS max_value,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index e6f87543df8..e0df642254f 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -34,6 +34,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"
@@ -229,12 +230,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);
 	CatalogTupleInsert(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,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,
@@ -1320,6 +1332,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)
 	{
@@ -1352,12 +1378,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)
 	{
@@ -1369,10 +1417,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)
 	{
@@ -1590,8 +1660,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 +1671,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 +1684,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 +1702,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 cf97be512d9..174773bdf31 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 8d1939445b5..0f78abaae20 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