From 2ea5b06c7a7056dca0af1610aadebe608fbcca08 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 10 Feb 2017 15:12:32 -0500 Subject: Add CREATE SEQUENCE AS 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 Reviewed-by: Michael Paquier --- doc/src/sgml/catalogs.sgml | 20 ++++++++++++++++--- doc/src/sgml/information_schema.sgml | 4 +--- doc/src/sgml/ref/alter_sequence.sgml | 30 +++++++++++++++++++++++++---- doc/src/sgml/ref/create_sequence.sgml | 36 ++++++++++++++++++++++------------- 4 files changed, 67 insertions(+), 23 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 787cc10bf85..7d1c90a3115 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -5774,10 +5774,11 @@ - seqcycle - bool + seqtypid + oid + pg_type.oid - Whether the sequence cycles + Data type of the sequence @@ -5814,6 +5815,13 @@ Cache size of the sequence + + + seqcycle + bool + + Whether the sequence cycles + @@ -9840,6 +9848,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx pg_authid.rolname Name of sequence's owner + + data_type + regtype + pg_type.oid + Data type of the sequence + start_value bigint diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index c43e325d064..a3a19ce8ce2 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4653,9 +4653,7 @@ ORDER BY c.ordinal_position; data_type character_data - The data type of the sequence. In - PostgreSQL, this is currently always - bigint. + The data type of the sequence. diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml index 3b52e875e34..252a668189b 100644 --- a/doc/src/sgml/ref/alter_sequence.sgml +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -23,7 +23,9 @@ PostgreSQL documentation -ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ] +ALTER SEQUENCE [ IF EXISTS ] name + [ AS data_type ] + [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ RESTART [ [ WITH ] restart ] ] @@ -80,6 +82,26 @@ ALTER SEQUENCE [ IF EXISTS ] name S + + data_type + + + The optional + clause AS data_type + changes the data type of the sequence. Valid types are + are smallint, integer, + and bigint. + + + + Note that changing the data type does not automatically change the + minimum and maximum values. You can use the clauses NO + MINVALUE and NO MAXVALUE to adjust the + minimum and maximum values to the range of the new data type. + + + + increment @@ -102,7 +124,7 @@ ALTER SEQUENCE [ IF EXISTS ] name S class="parameter">minvalue determines the minimum value a sequence can generate. If NO MINVALUE is specified, the defaults of 1 and - -263 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. @@ -118,7 +140,7 @@ ALTER SEQUENCE [ IF EXISTS ] name S class="parameter">maxvalue determines the maximum value for the sequence. If NO MAXVALUE is specified, the defaults of - 263-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. @@ -300,7 +322,7 @@ ALTER SEQUENCE serial RESTART WITH 105; ALTER SEQUENCE conforms to the SQL - standard, except for the START WITH, + standard, except for the AS, START WITH, OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are PostgreSQL extensions. diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 86ff018c4ba..f1448e7ab3c 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -21,7 +21,9 @@ PostgreSQL documentation -CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ] +CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name + [ AS data_type ] + [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] @@ -110,6 +112,21 @@ SELECT * FROM name; + + data_type + + + The optional + clause AS data_type + specifies the data type of the sequence. Valid types are + are smallint, integer, + and bigint. bigint is the + default. The data type determines the default minimum and maximum + values of the sequence. + + + + increment @@ -132,9 +149,8 @@ SELECT * FROM name; class="parameter">minvalue determines the minimum value a sequence can generate. If this clause is not supplied or is specified, then - defaults will be used. The defaults are 1 and - -263 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. @@ -148,9 +164,9 @@ SELECT * FROM name; class="parameter">maxvalue determines the maximum value for the sequence. If this clause is not supplied or is specified, then - default values will be used. The defaults are - 263-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. @@ -347,12 +363,6 @@ END; CREATE SEQUENCE conforms to the SQL standard, with the following exceptions: - - - The standard's AS data_type expression is not - supported. - - Obtaining the next value is done using the nextval() -- cgit v1.2.3