Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2ea5b06

Browse files
committed
Add CREATE SEQUENCE AS <data type> clause
This stores a data type, required to be an integer type, with thesequence. The sequences min and max values default to the rangesupported by the type, and they cannot be set to values exceeding thatrange. 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 serialcolumn match the range of values supported by the table column. So thesequence can no longer overflow the table column.This also makes monitoring for sequence exhaustion/wraparound easier,which currently requires various contortions to cross-reference thesequences with the table columns they are used with.This commit also effectively reverts the pg_sequence column reorderinginf3b421d, because the new seqtypidcolumn allows us to fill the hole in the struct and create a morenatural overall column ordering.Reviewed-by: Steve Singer <steve@ssinger.info>Reviewed-by: Michael Paquier <michael.paquier@gmail.com>
1 parent9401883 commit2ea5b06

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>Whetherthe sequence cycles</entry>
5781+
<entry>Data type ofthe 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. Thedefaults are
152-
2<superscript>63</>-1 and -1 for ascending and descending
153-
sequences, respectively.
167+
default values will be used. Thedefault 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.nspnameAS sql_identifier)AS sequence_schema,
15331533
CAST(c.relnameAS sql_identifier)AS sequence_name,
1534-
CAST('bigint'AS character_data)AS data_type,
1535-
CAST(64AS 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(2AS cardinal_number)AS numeric_precision_radix,
15371537
CAST(0AS cardinal_number)AS numeric_scale,
15381538
CAST(s.seqstartAS 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.nspnameAS schemaname,
170170
C.relnameAS sequencename,
171171
pg_get_userbyid(C.relowner)AS sequenceowner,
172+
S.seqtypid::regtypeAS data_type,
172173
S.seqstartAS start_value,
173174
S.seqminAS min_value,
174175
S.seqmaxAS 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_sequenceseqform,
12221224
Form_pg_sequence_dataseqdataform,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+
elseif (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+
elseif (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
elseif (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+
elseif (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+
charbufx[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+
elseif (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+
charbufm[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
Oidrelid=PG_GETARG_OID(0);
15921662
TupleDesctupdesc;
1593-
Datumvalues[6];
1594-
boolisnull[6];
1663+
Datumvalues[7];
1664+
boolisnull[7];
15951665
HeapTuplepgstuple;
15961666
Form_pg_sequencepgsform;
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)
[8]ページ先頭
©2009-2025 Movatter.jp