Skip to content

Commit 2ea5b06

Browse files
peterepetere
committed
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 f3b421d, 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>
1 parent 9401883 commit 2ea5b06

File tree

18 files changed

+274
-99
lines changed

18 files changed

+274
-99
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5774,10 +5774,11 @@
57745774
</row>
57755775

57765776
<row>
5777-
<entry><structfield>seqcycle</structfield></entry>
5778-
<entry><type>bool</type></entry>
5777+
<entry><structfield>seqtypid</structfield></entry>
5778+
<entry><type>oid</type></entry>
5779+
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
57795780
<entry></entry>
5780-
<entry>Whether the sequence cycles</entry>
5781+
<entry>Data type of the sequence</entry>
57815782
</row>
57825783

57835784
<row>
@@ -5814,6 +5815,13 @@
58145815
<entry></entry>
58155816
<entry>Cache size of the sequence</entry>
58165817
</row>
5818+
5819+
<row>
5820+
<entry><structfield>seqcycle</structfield></entry>
5821+
<entry><type>bool</type></entry>
5822+
<entry></entry>
5823+
<entry>Whether the sequence cycles</entry>
5824+
</row>
58175825
</tbody>
58185826
</tgroup>
58195827
</table>
@@ -9840,6 +9848,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
98409848
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
98419849
<entry>Name of sequence's owner</entry>
98429850
</row>
9851+
<row>
9852+
<entry><structfield>data_type</structfield></entry>
9853+
<entry><type>regtype</type></entry>
9854+
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_type</structname></link>.oid</literal></entry>
9855+
<entry>Data type of the sequence</entry>
9856+
</row>
98439857
<row>
98449858
<entry><structfield>start_value</structfield></entry>
98459859
<entry><type>bigint</type></entry>

‎doc/src/sgml/information_schema.sgml

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4653,9 +4653,7 @@ ORDER BY c.ordinal_position;
46534653
<entry><literal>data_type</literal></entry>
46544654
<entry><type>character_data</type></entry>
46554655
<entry>
4656-
The data type of the sequence. In
4657-
<productname>PostgreSQL</productname>, this is currently always
4658-
<literal>bigint</literal>.
4656+
The data type of the sequence.
46594657
</entry>
46604658
</row>
46614659

‎doc/src/sgml/ref/alter_sequence.sgml

Lines changed: 26 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,9 @@ PostgreSQL documentation
2323

2424
<refsynopsisdiv>
2525
<synopsis>
26-
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
26+
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
27+
[ AS <replaceable class="parameter">data_type</replaceable> ]
28+
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2729
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
2830
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
2931
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
@@ -80,6 +82,26 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
8082
</listitem>
8183
</varlistentry>
8284

85+
<varlistentry>
86+
<term><replaceable class="parameter">data_type</replaceable></term>
87+
<listitem>
88+
<para>
89+
The optional
90+
clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
91+
changes the data type of the sequence. Valid types are
92+
are <literal>smallint</literal>, <literal>integer</literal>,
93+
and <literal>bigint</literal>.
94+
</para>
95+
96+
<para>
97+
Note that changing the data type does not automatically change the
98+
minimum and maximum values. You can use the clauses <literal>NO
99+
MINVALUE</literal> and <literal>NO MAXVALUE</literal> to adjust the
100+
minimum and maximum values to the range of the new data type.
101+
</para>
102+
</listitem>
103+
</varlistentry>
104+
83105
<varlistentry>
84106
<term><replaceable class="parameter">increment</replaceable></term>
85107
<listitem>
@@ -102,7 +124,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
102124
class="parameter">minvalue</replaceable></literal> determines
103125
the minimum value a sequence can generate. If <literal>NO
104126
MINVALUE</literal> is specified, the defaults of 1 and
105-
-2<superscript>63</> for ascending and descending sequences,
127+
the minimum value of the data type for ascending and descending sequences,
106128
respectively, will be used. If neither option is specified,
107129
the current minimum value will be maintained.
108130
</para>
@@ -118,7 +140,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
118140
class="parameter">maxvalue</replaceable></literal> determines
119141
the maximum value for the sequence. If <literal>NO
120142
MAXVALUE</literal> is specified, the defaults of
121-
2<superscript>63</>-1 and -1 for ascending and descending
143+
the maximum value of the data type and -1 for ascending and descending
122144
sequences, respectively, will be used. If neither option is
123145
specified, the current maximum value will be maintained.
124146
</para>
@@ -300,7 +322,7 @@ ALTER SEQUENCE serial RESTART WITH 105;
300322

301323
<para>
302324
<command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
303-
standard, except for the <literal>START WITH</>,
325+
standard, except for the <literal>AS</literal>, <literal>START WITH</>,
304326
<literal>OWNED BY</>, <literal>OWNER TO</>, <literal>RENAME TO</>, and
305327
<literal>SET SCHEMA</literal> clauses, which are
306328
<productname>PostgreSQL</productname> extensions.

‎doc/src/sgml/ref/create_sequence.sgml

Lines changed: 23 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,9 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
24+
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
25+
[ AS <replaceable class="parameter">data_type</replaceable> ]
26+
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2527
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
2628
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
2729
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
@@ -110,6 +112,21 @@ SELECT * FROM <replaceable>name</replaceable>;
110112
</listitem>
111113
</varlistentry>
112114

115+
<varlistentry>
116+
<term><replaceable class="parameter">data_type</replaceable></term>
117+
<listitem>
118+
<para>
119+
The optional
120+
clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
121+
specifies the data type of the sequence. Valid types are
122+
are <literal>smallint</literal>, <literal>integer</literal>,
123+
and <literal>bigint</literal>. <literal>bigint</literal> is the
124+
default. The data type determines the default minimum and maximum
125+
values of the sequence.
126+
</para>
127+
</listitem>
128+
</varlistentry>
129+
113130
<varlistentry>
114131
<term><replaceable class="parameter">increment</replaceable></term>
115132
<listitem>
@@ -132,9 +149,8 @@ SELECT * FROM <replaceable>name</replaceable>;
132149
class="parameter">minvalue</replaceable></literal> determines
133150
the minimum value a sequence can generate. If this clause is not
134151
supplied or <option>NO MINVALUE</option> is specified, then
135-
defaults will be used. The defaults are 1 and
136-
-2<superscript>63</> for ascending and descending sequences,
137-
respectively.
152+
defaults will be used. The default for an ascending sequence is 1. The
153+
default for a descending sequence is the minimum value of the data type.
138154
</para>
139155
</listitem>
140156
</varlistentry>
@@ -148,9 +164,9 @@ SELECT * FROM <replaceable>name</replaceable>;
148164
class="parameter">maxvalue</replaceable></literal> determines
149165
the maximum value for the sequence. If this clause is not
150166
supplied or <option>NO MAXVALUE</option> is specified, then
151-
default values will be used. The defaults are
152-
2<superscript>63</>-1 and -1 for ascending and descending
153-
sequences, respectively.
167+
default values will be used. The default for an ascending sequence is
168+
the maximum value of the data type. The default for a descending
169+
sequence is -1.
154170
</para>
155171
</listitem>
156172
</varlistentry>
@@ -347,12 +363,6 @@ END;
347363
<command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
348364
standard, with the following exceptions:
349365
<itemizedlist>
350-
<listitem>
351-
<para>
352-
The standard's <literal>AS <replaceable>data_type</></literal> expression is not
353-
supported.
354-
</para>
355-
</listitem>
356366
<listitem>
357367
<para>
358368
Obtaining the next value is done using the <function>nextval()</>

‎src/backend/catalog/information_schema.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1531,8 +1531,8 @@ CREATE VIEW sequences AS
15311531
SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
15321532
CAST(nc.nspname AS sql_identifier) AS sequence_schema,
15331533
CAST(c.relname AS sql_identifier) AS sequence_name,
1534-
CAST('bigint' AS character_data) AS data_type,
1535-
CAST(64 AS cardinal_number) AS numeric_precision,
1534+
CAST(format_type(s.seqtypid, null) AS character_data) AS data_type,
1535+
CAST(_pg_numeric_precision(s.seqtypid, -1) AS cardinal_number) AS numeric_precision,
15361536
CAST(2 AS cardinal_number) AS numeric_precision_radix,
15371537
CAST(0 AS cardinal_number) AS numeric_scale,
15381538
CAST(s.seqstart AS character_data) AS start_value,

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -169,6 +169,7 @@ CREATE OR REPLACE VIEW pg_sequences AS
169169
N.nspname AS schemaname,
170170
C.relname AS sequencename,
171171
pg_get_userbyid(C.relowner) AS sequenceowner,
172+
S.seqtypid::regtype AS data_type,
172173
S.seqstart AS start_value,
173174
S.seqmin AS min_value,
174175
S.seqmax AS max_value,

‎src/backend/commands/sequence.c

Lines changed: 81 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@
3434
#include "funcapi.h"
3535
#include "miscadmin.h"
3636
#include "nodes/makefuncs.h"
37+
#include "parser/parse_type.h"
3738
#include "storage/lmgr.h"
3839
#include "storage/proc.h"
3940
#include "storage/smgr.h"
@@ -229,12 +230,13 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
229230
memset(pgs_nulls, 0, sizeof(pgs_nulls));
230231

231232
pgs_values[Anum_pg_sequence_seqrelid - 1] = ObjectIdGetDatum(seqoid);
232-
pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
233+
pgs_values[Anum_pg_sequence_seqtypid - 1] = ObjectIdGetDatum(seqform.seqtypid);
233234
pgs_values[Anum_pg_sequence_seqstart - 1] = Int64GetDatumFast(seqform.seqstart);
234235
pgs_values[Anum_pg_sequence_seqincrement - 1] = Int64GetDatumFast(seqform.seqincrement);
235236
pgs_values[Anum_pg_sequence_seqmax - 1] = Int64GetDatumFast(seqform.seqmax);
236237
pgs_values[Anum_pg_sequence_seqmin - 1] = Int64GetDatumFast(seqform.seqmin);
237238
pgs_values[Anum_pg_sequence_seqcache - 1] = Int64GetDatumFast(seqform.seqcache);
239+
pgs_values[Anum_pg_sequence_seqcycle - 1] = BoolGetDatum(seqform.seqcycle);
238240

239241
tuple = heap_form_tuple(tupDesc, pgs_values, pgs_nulls);
240242
CatalogTupleInsert(rel, tuple);
@@ -622,11 +624,11 @@ nextval_internal(Oid relid)
622624
if (!HeapTupleIsValid(pgstuple))
623625
elog(ERROR, "cache lookup failed for sequence %u", relid);
624626
pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple);
625-
cycle = pgsform->seqcycle;
626627
incby = pgsform->seqincrement;
627628
maxv = pgsform->seqmax;
628629
minv = pgsform->seqmin;
629630
cache = pgsform->seqcache;
631+
cycle = pgsform->seqcycle;
630632
ReleaseSysCache(pgstuple);
631633

632634
/* lock page' buffer and read tuple */
@@ -1221,6 +1223,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
12211223
Form_pg_sequence seqform,
12221224
Form_pg_sequence_data seqdataform, List **owned_by)
12231225
{
1226+
DefElem *as_type = NULL;
12241227
DefElem *start_value = NULL;
12251228
DefElem *restart_value = NULL;
12261229
DefElem *increment_by = NULL;
@@ -1236,7 +1239,16 @@ init_params(ParseState *pstate, List *options, bool isInit,
12361239
{
12371240
DefElem *defel = (DefElem *) lfirst(option);
12381241

1239-
if (strcmp(defel->defname, "increment") == 0)
1242+
if (strcmp(defel->defname, "as") == 0)
1243+
{
1244+
if (as_type)
1245+
ereport(ERROR,
1246+
(errcode(ERRCODE_SYNTAX_ERROR),
1247+
errmsg("conflicting or redundant options"),
1248+
parser_errposition(pstate, defel->location)));
1249+
as_type = defel;
1250+
}
1251+
else if (strcmp(defel->defname, "increment") == 0)
12401252
{
12411253
if (increment_by)
12421254
ereport(ERROR,
@@ -1320,6 +1332,20 @@ init_params(ParseState *pstate, List *options, bool isInit,
13201332
if (isInit)
13211333
seqdataform->log_cnt = 0;
13221334

1335+
/* AS type */
1336+
if (as_type != NULL)
1337+
{
1338+
seqform->seqtypid = typenameTypeId(pstate, defGetTypeName(as_type));
1339+
if (seqform->seqtypid != INT2OID &&
1340+
seqform->seqtypid != INT4OID &&
1341+
seqform->seqtypid != INT8OID)
1342+
ereport(ERROR,
1343+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1344+
errmsg("sequence type must be smallint, integer, or bigint")));
1345+
}
1346+
else if (isInit)
1347+
seqform->seqtypid = INT8OID;
1348+
13231349
/* INCREMENT BY */
13241350
if (increment_by != NULL)
13251351
{
@@ -1352,12 +1378,34 @@ init_params(ParseState *pstate, List *options, bool isInit,
13521378
else if (isInit || max_value != NULL)
13531379
{
13541380
if (seqform->seqincrement > 0)
1355-
seqform->seqmax = PG_INT64_MAX; /* ascending seq */
1381+
{
1382+
/* ascending seq */
1383+
if (seqform->seqtypid == INT2OID)
1384+
seqform->seqmax = PG_INT16_MAX;
1385+
else if (seqform->seqtypid == INT4OID)
1386+
seqform->seqmax = PG_INT32_MAX;
1387+
else
1388+
seqform->seqmax = PG_INT64_MAX;
1389+
}
13561390
else
13571391
seqform->seqmax = -1; /* descending seq */
13581392
seqdataform->log_cnt = 0;
13591393
}
13601394

1395+
if ((seqform->seqtypid == INT2OID && (seqform->seqmax < PG_INT16_MIN || seqform->seqmax > PG_INT16_MAX))
1396+
|| (seqform->seqtypid == INT4OID && (seqform->seqmax < PG_INT32_MIN || seqform->seqmax > PG_INT32_MAX))
1397+
|| (seqform->seqtypid == INT8OID && (seqform->seqmax < PG_INT64_MIN || seqform->seqmax > PG_INT64_MAX)))
1398+
{
1399+
char bufx[100];
1400+
1401+
snprintf(bufx, sizeof(bufx), INT64_FORMAT, seqform->seqmax);
1402+
1403+
ereport(ERROR,
1404+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1405+
errmsg("MAXVALUE (%s) is out of range for sequence data type %s",
1406+
bufx, format_type_be(seqform->seqtypid))));
1407+
}
1408+
13611409
/* MINVALUE (null arg means NO MINVALUE) */
13621410
if (min_value != NULL && min_value->arg)
13631411
{
@@ -1369,10 +1417,32 @@ init_params(ParseState *pstate, List *options, bool isInit,
13691417
if (seqform->seqincrement > 0)
13701418
seqform->seqmin = 1; /* ascending seq */
13711419
else
1372-
seqform->seqmin = PG_INT64_MIN; /* descending seq */
1420+
{
1421+
/* descending seq */
1422+
if (seqform->seqtypid == INT2OID)
1423+
seqform->seqmin = PG_INT16_MIN;
1424+
else if (seqform->seqtypid == INT4OID)
1425+
seqform->seqmin = PG_INT32_MIN;
1426+
else
1427+
seqform->seqmin = PG_INT64_MIN;
1428+
}
13731429
seqdataform->log_cnt = 0;
13741430
}
13751431

1432+
if ((seqform->seqtypid == INT2OID && (seqform->seqmin < PG_INT16_MIN || seqform->seqmin > PG_INT16_MAX))
1433+
|| (seqform->seqtypid == INT4OID && (seqform->seqmin < PG_INT32_MIN || seqform->seqmin > PG_INT32_MAX))
1434+
|| (seqform->seqtypid == INT8OID && (seqform->seqmin < PG_INT64_MIN || seqform->seqmin > PG_INT64_MAX)))
1435+
{
1436+
char bufm[100];
1437+
1438+
snprintf(bufm, sizeof(bufm), INT64_FORMAT, seqform->seqmin);
1439+
1440+
ereport(ERROR,
1441+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1442+
errmsg("MINVALUE (%s) is out of range for sequence data type %s",
1443+
bufm, format_type_be(seqform->seqtypid))));
1444+
}
1445+
13761446
/* crosscheck min/max */
13771447
if (seqform->seqmin >= seqform->seqmax)
13781448
{
@@ -1590,8 +1660,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
15901660
{
15911661
Oid relid = PG_GETARG_OID(0);
15921662
TupleDesc tupdesc;
1593-
Datum values[6];
1594-
bool isnull[6];
1663+
Datum values[7];
1664+
bool isnull[7];
15951665
HeapTuple pgstuple;
15961666
Form_pg_sequence pgsform;
15971667

@@ -1601,7 +1671,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
16011671
errmsg("permission denied for sequence %s",
16021672
get_rel_name(relid))));
16031673

1604-
tupdesc = CreateTemplateTupleDesc(6, false);
1674+
tupdesc = CreateTemplateTupleDesc(7, false);
16051675
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
16061676
INT8OID, -1, 0);
16071677
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1614,6 +1684,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
16141684
BOOLOID, -1, 0);
16151685
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
16161686
INT8OID, -1, 0);
1687+
TupleDescInitEntry(tupdesc, (AttrNumber) 7, "data_type",
1688+
OIDOID, -1, 0);
16171689

16181690
BlessTupleDesc(tupdesc);
16191691

@@ -1630,6 +1702,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
16301702
values[3] = Int64GetDatum(pgsform->seqincrement);
16311703
values[4] = BoolGetDatum(pgsform->seqcycle);
16321704
values[5] = Int64GetDatum(pgsform->seqcache);
1705+
values[6] = ObjectIdGetDatum(pgsform->seqtypid);
16331706

16341707
ReleaseSysCache(pgstuple);
16351708

0 commit comments

Comments
 (0)