From 5f65225fa37bd244ee5487448ac542b2b2f47296 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 20 Mar 2003 07:02:11 +0000 Subject: [PATCH] Todo items: Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values Also updated create sequence docs to mention NO MINVALUE, & NO MAXVALUE. New Files: doc/src/sgml/ref/alter_sequence.sgml src/test/regress/expected/sequence.out src/test/regress/sql/sequence.sql ALTER SEQUENCE is NOT transactional. It behaves similarly to setval(). It matches the proposed SQL200N spec, as well as Oracle in most ways -- Oracle lacks RESTART WITH for some strange reason. -- Rod Taylor --- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/alter_sequence.sgml | 280 ++++++++++++++++++++++ doc/src/sgml/ref/create_sequence.sgml | 18 +- doc/src/sgml/reference.sgml | 3 +- src/backend/commands/sequence.c | 183 +++++++++++--- src/backend/nodes/copyfuncs.c | 16 +- src/backend/nodes/equalfuncs.c | 14 +- src/backend/parser/gram.y | 23 +- src/backend/parser/keywords.c | 3 +- src/backend/tcop/postgres.c | 8 +- src/backend/tcop/utility.c | 7 +- src/include/commands/sequence.h | 3 +- src/include/nodes/nodes.h | 3 +- src/include/nodes/parsenodes.h | 11 +- src/test/regress/expected/alter_table.out | 10 - src/test/regress/expected/create_misc.out | 28 --- src/test/regress/expected/sequence.out | 73 ++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 3 +- src/test/regress/sql/alter_table.sql | 5 - src/test/regress/sql/create_misc.sql | 21 -- src/test/regress/sql/sequence.sql | 39 +++ 22 files changed, 631 insertions(+), 125 deletions(-) create mode 100644 doc/src/sgml/ref/alter_sequence.sgml create mode 100644 src/test/regress/expected/sequence.out create mode 100644 src/test/regress/sql/sequence.sql diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 53cb7d4b18..a922e933e1 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml new file mode 100644 index 0000000000..c6c3a7e33c --- /dev/null +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -0,0 +1,280 @@ + + + + + ALTER SEQUENCE + SQL - Language Statements + + + + ALTER SEQUENCE + + + alter the definition of a sequence generator + + + + + 1999-07-20 + + +ALTER SEQUENCE seqname [ INCREMENT [ BY ] increment ] + [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] + [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + + + + + 1998-09-11 + + + Inputs + + + + + + seqname + + + The name (optionally schema-qualified) of a sequence to be altered. + + + + + + increment + + + The + + clause is optional. A positive value will make an + ascending sequence, a negative one a descending sequence. + If unspecified, the old increment value will be maintained. + + + + + + minvalue + NO MINVALUE + + + The optional clause + determines the minimum value + a sequence can generate. If is specified, + the defaults of 1 and -2^63-1 for ascending and descending sequences, respectively, will be used. If neither option is specified, the current minimum + value will be maintained. + + + + + + maxvalue + NO MAXVALUE + + + The optional clause + determines the maximum value for the sequence. If + is specified, the defaults are 2^63-1 and -1 for + ascending and descending sequences, respectively, will be used. If + neither option is specified, the current maximum value will be + maintained. + + + + + + start + + + The optional enables the sequence to re-begin anywhere. + + + + + + cache + + + The option + enables sequence numbers to be preallocated + and stored in memory for faster access. The minimum + value is 1 (only one value can be generated at a time, i.e., no cache). + If unspecified, the old cache value will be maintained. + + + + + + CYCLE + + + The optional keyword may be used to enable + the sequence to wrap around when the + maxvalue or + minvalue has been + reached by + an ascending or descending sequence respectively. If the limit is + reached, the next number generated will be the + minvalue or + maxvalue, + respectively. + + + + + + NO CYCLE + + + If the optional keyword is specified, any + calls to nextval after the sequence has reached + its maximum value will return an error. If neither + or are specified, + the old cycle behaviour will be maintained. + + + + + + + + + + 1998-09-11 + + + Outputs + + + + + + +ALTER SEQUENCE + + + + Message returned if the command is successful. + + + + + +ERROR: AlterSequence: MINVALUE (start) can't be >= MAXVALUE (max) + + + + If the specified starting value is out of range. + + + + + +ERROR: AlterSequence: START value (start) can't be < MINVALUE (min) + + + + If the specified starting value is out of range. + + + + + +ERROR: AlterSequence: MINVALUE (min) can't be >= MAXVALUE (max) + + + + If the minimum and maximum values are inconsistent. + + + + + + + + + + + Description + + + + See for limitations, and uses + of sequences. + + + + + + Usage + + + Restart a sequence called serial, at 105: + + +ALTER SEQUENCE serial RESTART WITH 105; + + + + + + Notes + + + To avoid blocking of concurrent transactions that obtain numbers from the same + sequence, a nextval operation is never rolled back; that is, once a value has + been fetched it is considered used, even if the transaction that did the nextval + later aborts. This means that aborted transactions may leave unused "holes" in + the sequence of assigned values. setval operations are never rolled back, either. + + + + + + + Compatibility + + + + + 2003-03-02 + + + SQL99 + + + + ALTER SEQUENCE is a PostgreSQL + language extension. + There is no ALTER SEQUENCE statement + in SQL99. + + + + + + diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index a357e434e3..550605447f 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ @@ -22,7 +22,7 @@ PostgreSQL documentation CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT [ BY ] increment ] - [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] + [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] @@ -63,7 +63,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname The - + clause is optional. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is one (1). @@ -73,12 +73,14 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname minvalue + NO MINVALUE The optional clause determines the minimum value - a sequence can generate. The defaults are 1 and -2^63-1 for + a sequence can generate. If this clause is not supplied or + is specified, then defaults will be used. The defaults are 1 and -2^63-1 for ascending and descending sequences, respectively. @@ -86,13 +88,15 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname maxvalue + NO MAXVALUE The optional clause determines the maximum - value for the sequence. The defaults are 2^63-1 and -1 for - ascending and descending sequences, respectively. + value for the sequence. If this clause is not supplied or + is specified, then default values will be used. + The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively. @@ -101,7 +105,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqnamestart - The optional enables the sequence to begin anywhere. The default starting value is diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 9f5cdf8b91..3486ca4182 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -49,6 +49,7 @@ PostgreSQL Reference Manual &alterDatabase; &alterDomain; &alterGroup; + &alterSequence; &alterTable; &alterTrigger; &alterUser; diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 74068181e4..460c616dd4 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/sequence.c,v 1.93 2003/03/20 05:18:14 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/sequence.c,v 1.94 2003/03/20 07:02:07 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -71,7 +71,7 @@ static void init_sequence(const char *caller, RangeVar *relation, SeqTable *p_elm, Relation *p_rel); static Form_pg_sequence read_info(const char *caller, SeqTable elm, Relation rel, Buffer *buf); -static void init_params(CreateSeqStmt *seq, Form_pg_sequence new); +static void init_params(char *caller, List *options, Form_pg_sequence new); static void do_setval(RangeVar *sequence, int64 next, bool iscalled); /* @@ -95,8 +95,16 @@ DefineSequence(CreateSeqStmt *seq) int i; NameData name; + /* Values are NULL (or false) by default */ + new.last_value = NULL; + new.increment_by = NULL; + new.max_value = NULL; + new.min_value = NULL; + new.cache_value = NULL; + new.is_cycled = false; + /* Check and set values */ - init_params(seq, &new); + init_params("DefineSequence", seq->options, &new); /* * Create relation (and fill *null & *value) @@ -289,6 +297,90 @@ DefineSequence(CreateSeqStmt *seq) heap_close(rel, NoLock); } +/* + * AlterSequence + * + * Modify the defition of a sequence relation + */ +void +AlterSequence(AlterSeqStmt *stmt) +{ + SeqTable elm; + Relation seqrel; + Buffer buf; + Page page; + Form_pg_sequence seq; + FormData_pg_sequence new; + + /* open and AccessShareLock sequence */ + init_sequence("setval", stmt->sequence, &elm, &seqrel); + + /* Allow DROP to sequence owner only*/ + if (!pg_class_ownercheck(elm->relid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, stmt->sequence->relname); + + /* lock page' buffer and read tuple into new sequence structure */ + seq = read_info("nextval", elm, seqrel, &buf); + page = BufferGetPage(buf); + + new.increment_by = seq->increment_by; + new.max_value = seq->max_value; + new.min_value = seq->min_value; + new.cache_value = seq->cache_value; + new.is_cycled = seq->is_cycled; + new.last_value = seq->last_value; + + /* Check and set values */ + init_params("AlterSequence", stmt->options, &new); + + seq->increment_by = new.increment_by; + seq->max_value = new.max_value; + seq->min_value = new.min_value; + seq->cache_value = new.cache_value; + seq->is_cycled = new.is_cycled; + if (seq->last_value != new.last_value) + { + seq->last_value = new.last_value; + seq->is_called = false; + seq->log_cnt = 1; + } + + START_CRIT_SECTION(); + + /* XLOG stuff */ + if (!seqrel->rd_istemp) + { + xl_seq_rec xlrec; + XLogRecPtr recptr; + XLogRecData rdata[2]; + + xlrec.node = seqrel->rd_node; + rdata[0].buffer = InvalidBuffer; + rdata[0].data = (char *) &xlrec; + rdata[0].len = sizeof(xl_seq_rec); + rdata[0].next = &(rdata[1]); + + rdata[1].buffer = InvalidBuffer; + rdata[1].data = (char *) page + ((PageHeader) page)->pd_upper; + rdata[1].len = ((PageHeader) page)->pd_special - + ((PageHeader) page)->pd_upper; + rdata[1].next = NULL; + + recptr = XLogInsert(RM_SEQ_ID, XLOG_SEQ_LOG | XLOG_NO_TRAN, rdata); + + PageSetLSN(page, recptr); + PageSetSUI(page, ThisStartUpID); + } + + END_CRIT_SECTION(); + + LockBuffer(buf, BUFFER_LOCK_UNLOCK); + + WriteBuffer(buf); + + relation_close(seqrel, NoLock); +} + Datum nextval(PG_FUNCTION_ARGS) @@ -761,7 +853,7 @@ read_info(const char *caller, SeqTable elm, static void -init_params(CreateSeqStmt *seq, Form_pg_sequence new) +init_params(char *caller, List *options, Form_pg_sequence new) { DefElem *last_value = NULL; DefElem *increment_by = NULL; @@ -771,76 +863,92 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new) bool is_cycled_set = false; List *option; - new->is_cycled = false; - foreach(option, seq->options) + foreach(option, options) { DefElem *defel = (DefElem *) lfirst(option); if (strcmp(defel->defname, "increment") == 0) { if (increment_by) - elog(ERROR, "DefineSequence: INCREMENT BY defined twice"); + elog(ERROR, "%s: INCREMENT BY defined twice", caller); + increment_by = defel; + } - else if (strcmp(defel->defname, "start") == 0) + /* + * start is for a new sequence + * restart is for alter + */ + else if ((new->last_value == NULL && strcmp(defel->defname, "start") == 0) + || (new->last_value != NULL && strcmp(defel->defname, "restart") == 0)) { if (last_value) - elog(ERROR, "DefineSequence: LAST VALUE defined twice"); + elog(ERROR, "%s: LAST VALUE defined twice", caller); last_value = defel; } else if (strcmp(defel->defname, "maxvalue") == 0) { if (max_value) - elog(ERROR, "DefineSequence: MAX VALUE defined twice"); + elog(ERROR, "%s: MAX VALUE defined twice", caller); max_value = defel; } else if (strcmp(defel->defname, "minvalue") == 0) { if (min_value) - elog(ERROR, "DefineSequence: MIN VALUE defined twice"); + elog(ERROR, "%s: MIN VALUE defined twice", caller); min_value = defel; } else if (strcmp(defel->defname, "cache") == 0) { if (cache_value) - elog(ERROR, "DefineSequence: CACHE defined twice"); + elog(ERROR, "%s: CACHE defined twice", caller); cache_value = defel; } else if (strcmp(defel->defname, "cycle") == 0) { if (is_cycled_set) - elog(ERROR, "DefineSequence: CYCLE defined twice"); + elog(ERROR, "%s: CYCLE defined twice", caller); is_cycled_set = true; new->is_cycled = (defel->arg != NULL); } else - elog(ERROR, "DefineSequence: option \"%s\" not recognized", + elog(ERROR, "%s: option \"%s\" not recognized", caller, defel->defname); } - if (increment_by == (DefElem *) NULL) /* INCREMENT BY */ + /* INCREMENT BY */ + if (new->increment_by == NULL && increment_by == (DefElem *) NULL) new->increment_by = 1; - else if ((new->increment_by = defGetInt64(increment_by)) == 0) - elog(ERROR, "DefineSequence: can't INCREMENT by 0"); + else if (increment_by != (DefElem *) NULL) + { + if (defGetInt64(increment_by) == 0) + elog(ERROR, "%s: can't INCREMENT by 0", caller); + + new->increment_by = defGetInt64(increment_by); + } - if (max_value == (DefElem *) NULL || !max_value->arg) /* MAXVALUE */ + /* MAXVALUE */ + if ((new->max_value == NULL && max_value == (DefElem *) NULL) + || (max_value != (DefElem *) NULL && !max_value->arg)) { if (new->increment_by > 0) - new->max_value = SEQ_MAXVALUE; /* ascending seq */ + new->max_value = SEQ_MAXVALUE; /* ascending seq */ else - new->max_value = -1; /* descending seq */ + new->max_value = -1; /* descending seq */ } - else + else if (max_value != (DefElem *) NULL) new->max_value = defGetInt64(max_value); - if (min_value == (DefElem *) NULL || !min_value->arg) /* MINVALUE */ + /* MINVALUE */ + if ((new->min_value == NULL && min_value == (DefElem *) NULL) + || (min_value != (DefElem *) NULL && !min_value->arg)) { if (new->increment_by > 0) - new->min_value = 1; /* ascending seq */ + new->min_value = 1; /* ascending seq */ else - new->min_value = SEQ_MINVALUE; /* descending seq */ + new->min_value = SEQ_MINVALUE; /* descending seq */ } - else + else if (min_value != (DefElem *) NULL) new->min_value = defGetInt64(min_value); if (new->min_value >= new->max_value) @@ -850,18 +958,19 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new) snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->min_value); snprintf(bufx, sizeof(bufx), INT64_FORMAT, new->max_value); - elog(ERROR, "DefineSequence: MINVALUE (%s) must be less than MAXVALUE (%s)", - bufm, bufx); + elog(ERROR, "%s: MINVALUE (%s) must be less than MAXVALUE (%s)", + caller, bufm, bufx); } - if (last_value == (DefElem *) NULL) /* START WITH */ + /* START WITH */ + if (new->last_value == NULL && last_value == (DefElem *) NULL) { if (new->increment_by > 0) new->last_value = new->min_value; /* ascending seq */ else new->last_value = new->max_value; /* descending seq */ } - else + else if (last_value != (DefElem *) NULL) new->last_value = defGetInt64(last_value); if (new->last_value < new->min_value) @@ -871,8 +980,8 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new) snprintf(bufs, sizeof(bufs), INT64_FORMAT, new->last_value); snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->min_value); - elog(ERROR, "DefineSequence: START value (%s) can't be less than MINVALUE (%s)", - bufs, bufm); + elog(ERROR, "%s: START value (%s) can't be less than MINVALUE (%s)", + caller, bufs, bufm); } if (new->last_value > new->max_value) { @@ -881,21 +990,21 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new) snprintf(bufs, sizeof(bufs), INT64_FORMAT, new->last_value); snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->max_value); - elog(ERROR, "DefineSequence: START value (%s) can't be greater than MAXVALUE (%s)", - bufs, bufm); + elog(ERROR, "%s: START value (%s) can't be greater than MAXVALUE (%s)", + caller, bufs, bufm); } - if (cache_value == (DefElem *) NULL) /* CACHE */ + /* CACHE */ + if (cache_value == (DefElem *) NULL) new->cache_value = 1; else if ((new->cache_value = defGetInt64(cache_value)) <= 0) { char buf[100]; snprintf(buf, sizeof(buf), INT64_FORMAT, new->cache_value); - elog(ERROR, "DefineSequence: CACHE (%s) can't be <= 0", - buf); + elog(ERROR, "%s: CACHE (%s) can't be <= 0", + caller, buf); } - } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 9ea51d589b..c043b8b249 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.246 2003/03/10 03:53:49 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.247 2003/03/20 07:02:08 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -2064,6 +2064,17 @@ _copyCreateSeqStmt(CreateSeqStmt *from) return newnode; } +static AlterSeqStmt * +_copyAlterSeqStmt(AlterSeqStmt *from) +{ + AlterSeqStmt *newnode = makeNode(AlterSeqStmt); + + COPY_NODE_FIELD(sequence); + COPY_NODE_FIELD(options); + + return newnode; +} + static VariableSetStmt * _copyVariableSetStmt(VariableSetStmt *from) { @@ -2741,6 +2752,9 @@ copyObject(void *from) case T_CreateSeqStmt: retval = _copyCreateSeqStmt(from); break; + case T_AlterSeqStmt: + retval = _copyAlterSeqStmt(from); + break; case T_VariableSetStmt: retval = _copyVariableSetStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 3dd552bdd5..3f55d67cff 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -18,7 +18,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.189 2003/03/10 03:53:49 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.190 2003/03/20 07:02:08 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -1066,6 +1066,15 @@ _equalCreateSeqStmt(CreateSeqStmt *a, CreateSeqStmt *b) return true; } +static bool +_equalAlterSeqStmt(AlterSeqStmt *a, AlterSeqStmt *b) +{ + COMPARE_NODE_FIELD(sequence); + COMPARE_NODE_FIELD(options); + + return true; +} + static bool _equalVariableSetStmt(VariableSetStmt *a, VariableSetStmt *b) { @@ -1864,6 +1873,9 @@ equal(void *a, void *b) case T_CreateSeqStmt: retval = _equalCreateSeqStmt(a, b); break; + case T_AlterSeqStmt: + retval = _equalAlterSeqStmt(a, b); + break; case T_VariableSetStmt: retval = _equalVariableSetStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7b28bdc915..1fa5d95798 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.406 2003/03/11 19:40:23 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.407 2003/03/20 07:02:08 momjian Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -129,7 +129,7 @@ static void doNegateFloat(Value *v); %type stmt schema_stmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt - AlterTableStmt AlterUserStmt AlterUserSetStmt + AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt @@ -377,7 +377,7 @@ static void doNegateFloat(Value *v); PROCEDURE READ REAL RECHECK REFERENCES REINDEX RELATIVE RENAME REPLACE - RESET RESTRICT RETURNS REVOKE RIGHT ROLLBACK ROW ROWS + RESET RESTART RESTRICT RETURNS REVOKE RIGHT ROLLBACK ROW ROWS RULE SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE @@ -478,6 +478,7 @@ stmt : AlterDatabaseSetStmt | AlterDomainStmt | AlterGroupStmt + | AlterSeqStmt | AlterTableStmt | AlterUserSetStmt | AlterUserStmt @@ -1864,6 +1865,7 @@ CreateAsElement: * * QUERY : * CREATE SEQUENCE seqname + * ALTER SEQUENCE seqname * *****************************************************************************/ @@ -1878,6 +1880,16 @@ CreateSeqStmt: } ; +AlterSeqStmt: + ALTER SEQUENCE qualified_name OptSeqList + { + AlterSeqStmt *n = makeNode(AlterSeqStmt); + n->sequence = $3; + n->options = $4; + $$ = (Node *)n; + } + ; + OptSeqList: OptSeqList OptSeqElem { $$ = lappend($1, $2); } | /*EMPTY*/ { $$ = NIL; } ; @@ -1918,6 +1930,10 @@ OptSeqElem: CACHE NumericOnly { $$ = makeDefElem("start", (Node *)$3); } + | RESTART opt_with NumericOnly + { + $$ = makeDefElem("restart", (Node *)$3); + } ; opt_by: BY {} @@ -7163,6 +7179,7 @@ unreserved_keyword: | RENAME | REPLACE | RESET + | RESTART | RESTRICT | RETURNS | REVOKE diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index 49432cb957..83608d95ed 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.135 2003/03/11 19:40:23 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.136 2003/03/20 07:02:10 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -248,6 +248,7 @@ static const ScanKeyword ScanKeywords[] = { {"rename", RENAME}, {"replace", REPLACE}, {"reset", RESET}, + {"restart", RESTART}, {"restrict", RESTRICT}, {"returns", RETURNS}, {"revoke", REVOKE}, diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index ec717d8c12..dd2ed6e500 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/postgres.c,v 1.317 2003/03/10 03:53:51 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/postgres.c,v 1.318 2003/03/20 07:02:10 momjian Exp $ * * NOTES * this is the "main" module of the postgres backend and @@ -1789,7 +1789,7 @@ PostgresMain(int argc, char *argv[], const char *username) if (!IsUnderPostmaster) { puts("\nPOSTGRES backend interactive interface "); - puts("$Revision: 1.317 $ $Date: 2003/03/10 03:53:51 $\n"); + puts("$Revision: 1.318 $ $Date: 2003/03/20 07:02:10 $\n"); } /* @@ -2381,6 +2381,10 @@ CreateCommandTag(Node *parsetree) tag = "CREATE SEQUENCE"; break; + case T_AlterSeqStmt: + tag = "ALTER SEQUENCE"; + break; + case T_RemoveAggrStmt: tag = "DROP AGGREGATE"; break; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index baf74aa3cc..4d533633d6 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.195 2003/03/11 19:40:23 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.196 2003/03/20 07:02:11 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -183,6 +183,7 @@ check_xact_readonly(Node *parsetree) case T_AlterDatabaseSetStmt: case T_AlterDomainStmt: case T_AlterGroupStmt: + case T_AlterSeqStmt: case T_AlterTableStmt: case T_RenameStmt: case T_AlterUserStmt: @@ -748,6 +749,10 @@ ProcessUtility(Node *parsetree, DefineSequence((CreateSeqStmt *) parsetree); break; + case T_AlterSeqStmt: + AlterSequence((AlterSeqStmt *) parsetree); + break; + case T_RemoveAggrStmt: RemoveAggregate((RemoveAggrStmt *) parsetree); break; diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h index d4513de8c2..a0a18f0f33 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: sequence.h,v 1.22 2003/03/20 05:18:15 momjian Exp $ + * $Id: sequence.h,v 1.23 2003/03/20 07:02:11 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -84,6 +84,7 @@ extern Datum setval(PG_FUNCTION_ARGS); extern Datum setval_and_iscalled(PG_FUNCTION_ARGS); extern void DefineSequence(CreateSeqStmt *stmt); +extern void AlterSequence(AlterSeqStmt *stmt); extern void seq_redo(XLogRecPtr lsn, XLogRecord *rptr); extern void seq_undo(XLogRecPtr lsn, XLogRecord *rptr); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 3304ea89e4..7ccaeca348 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: nodes.h,v 1.138 2003/03/10 03:53:51 tgl Exp $ + * $Id: nodes.h,v 1.139 2003/03/20 07:02:11 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -220,6 +220,7 @@ typedef enum NodeTag T_VacuumStmt, T_ExplainStmt, T_CreateSeqStmt, + T_AlterSeqStmt, T_VariableSetStmt, T_VariableShowStmt, T_VariableResetStmt, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 216ed04c76..b215f8a98f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.233 2003/03/11 19:40:23 tgl Exp $ + * $Id: parsenodes.h,v 1.234 2003/03/20 07:02:11 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -1043,7 +1043,7 @@ typedef struct DropGroupStmt } DropGroupStmt; /* ---------------------- - * Create SEQUENCE Statement + * {Create|Alter} SEQUENCE Statement * ---------------------- */ @@ -1054,6 +1054,13 @@ typedef struct CreateSeqStmt List *options; } CreateSeqStmt; +typedef struct AlterSeqStmt +{ + NodeTag type; + RangeVar *sequence; /* the sequence to alter */ + List *options; +} AlterSeqStmt; + /* ---------------------- * Create {Aggregate|Operator|Type} Statement * ---------------------- diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 3cdb45edfb..341f4ded1e 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -286,16 +286,6 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 5; (5 rows) DROP VIEW tmp_view_new; --- renaming sequences -CREATE SEQUENCE foo_seq; -ALTER TABLE foo_seq RENAME TO foo_seq_new; -SELECT * FROM foo_seq_new; - sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ----------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- - foo_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f -(1 row) - -DROP SEQUENCE foo_seq_new; -- toast-like relation name alter table stud_emp rename to pg_toast_stud_emp; alter table pg_toast_stud_emp rename to stud_emp; diff --git a/src/test/regress/expected/create_misc.out b/src/test/regress/expected/create_misc.out index a2620f0b67..2189e30049 100644 --- a/src/test/regress/expected/create_misc.out +++ b/src/test/regress/expected/create_misc.out @@ -132,31 +132,3 @@ INSERT INTO iportaltest (i, d, p) VALUES (1, 3.567, '(3.0,1.0),(4.0,2.0)'::polygon); INSERT INTO iportaltest (i, d, p) VALUES (2, 89.05, '(4.0,2.0),(3.0,1.0)'::polygon); ---- ---- test creation of SERIAL column ---- -CREATE TABLE serialTest (f1 text, f2 serial); -NOTICE: CREATE TABLE will create implicit sequence 'serialtest_f2_seq' for SERIAL column 'serialtest.f2' -INSERT INTO serialTest VALUES ('foo'); -INSERT INTO serialTest VALUES ('bar'); -INSERT INTO serialTest VALUES ('force', 100); -INSERT INTO serialTest VALUES ('wrong', NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute f2 -SELECT * FROM serialTest; - f1 | f2 --------+----- - foo | 1 - bar | 2 - force | 100 -(3 rows) - -CREATE SEQUENCE sequence_test; -BEGIN; -SELECT nextval('sequence_test'); - nextval ---------- - 1 -(1 row) - -DROP SEQUENCE sequence_test; -END; diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out new file mode 100644 index 0000000000..349c153e83 --- /dev/null +++ b/src/test/regress/expected/sequence.out @@ -0,0 +1,73 @@ +--- +--- test creation of SERIAL column +--- + +CREATE TABLE serialTest (f1 text, f2 serial); +NOTICE: CREATE TABLE will create implicit sequence 'serialtest_f2_seq' for SERIAL column 'serialtest.f2' + +INSERT INTO serialTest VALUES ('foo'); +INSERT INTO serialTest VALUES ('bar'); +INSERT INTO serialTest VALUES ('force', 100); +INSERT INTO serialTest VALUES ('wrong', NULL); +ERROR: ExecInsert: Fail to add null value in not null attribute f2 + +SELECT * FROM serialTest; + f1 | f2 +-------+----- + foo | 1 + bar | 2 + force | 100 +(3 rows) + + +CREATE SEQUENCE sequence_test; + +BEGIN; +SELECT nextval('sequence_test'); + nextval +--------- + 1 +(1 row) + +DROP SEQUENCE sequence_test; +END; +-- renaming sequences +CREATE SEQUENCE foo_seq; +ALTER TABLE foo_seq RENAME TO foo_seq_new; +SELECT * FROM foo_seq_new; + sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called +---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- + foo_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f +(1 row) + +DROP SEQUENCE foo_seq_new; +-- +-- Alter sequence +-- +CREATE SEQUENCE sequence_test2 START WITH 32; +SELECT nextval('sequence_test2'); + nextval +--------- + 32 +(1 row) + +ALTER SEQUENCE sequence_test2 RESTART WITH 16 + INCREMENT BY 4 MAXVALUE 22 MINVALUE 5 CYCLE; +SELECT nextval('sequence_test2'); + nextval +--------- + 16 +(1 row) + +SELECT nextval('sequence_test2'); + nextval +--------- + 20 +(1 row) + +SELECT nextval('sequence_test2'); + nextval +--------- + 5 +(1 row) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 509bb11457..e8e8d1e67b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -74,4 +74,4 @@ test: select_views portals_p2 rules foreign_key cluster # The sixth group of parallel test # ---------- # "plpgsql" cannot run concurrently with "rules" -test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table +test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 22a3013e83..ff2a0d1662 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -1,4 +1,4 @@ -# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.18 2002/09/02 05:55:43 momjian Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.19 2003/03/20 07:02:11 momjian Exp $ # This should probably be in an order similar to parallel_schedule. test: boolean test: char @@ -90,3 +90,4 @@ test: without_oid test: conversion test: truncate test: alter_table +test: sequence diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 8e98d077a4..9dec23499c 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -173,11 +173,6 @@ ALTER TABLE tmp_view RENAME TO tmp_view_new; -- 5 values, sorted SELECT unique1 FROM tenk1 WHERE unique1 < 5; DROP VIEW tmp_view_new; --- renaming sequences -CREATE SEQUENCE foo_seq; -ALTER TABLE foo_seq RENAME TO foo_seq_new; -SELECT * FROM foo_seq_new; -DROP SEQUENCE foo_seq_new; -- toast-like relation name alter table stud_emp rename to pg_toast_stud_emp; alter table pg_toast_stud_emp rename to stud_emp; diff --git a/src/test/regress/sql/create_misc.sql b/src/test/regress/sql/create_misc.sql index 2277d5c8b2..a4cb816fa0 100644 --- a/src/test/regress/sql/create_misc.sql +++ b/src/test/regress/sql/create_misc.sql @@ -203,24 +203,3 @@ INSERT INTO iportaltest (i, d, p) INSERT INTO iportaltest (i, d, p) VALUES (2, 89.05, '(4.0,2.0),(3.0,1.0)'::polygon); - - ---- ---- test creation of SERIAL column ---- - -CREATE TABLE serialTest (f1 text, f2 serial); - -INSERT INTO serialTest VALUES ('foo'); -INSERT INTO serialTest VALUES ('bar'); -INSERT INTO serialTest VALUES ('force', 100); -INSERT INTO serialTest VALUES ('wrong', NULL); - -SELECT * FROM serialTest; - -CREATE SEQUENCE sequence_test; - -BEGIN; -SELECT nextval('sequence_test'); -DROP SEQUENCE sequence_test; -END; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql new file mode 100644 index 0000000000..6f3c1f22dd --- /dev/null +++ b/src/test/regress/sql/sequence.sql @@ -0,0 +1,39 @@ +--- +--- test creation of SERIAL column +--- + +CREATE TABLE serialTest (f1 text, f2 serial); + +INSERT INTO serialTest VALUES ('foo'); +INSERT INTO serialTest VALUES ('bar'); +INSERT INTO serialTest VALUES ('force', 100); +INSERT INTO serialTest VALUES ('wrong', NULL); + +SELECT * FROM serialTest; + +CREATE SEQUENCE sequence_test; + +BEGIN; +SELECT nextval('sequence_test'); +DROP SEQUENCE sequence_test; +END; + +-- renaming sequences +CREATE SEQUENCE foo_seq; +ALTER TABLE foo_seq RENAME TO foo_seq_new; +SELECT * FROM foo_seq_new; +DROP SEQUENCE foo_seq_new; + +-- +-- Alter sequence +-- +CREATE SEQUENCE sequence_test2 START WITH 32; + +SELECT nextval('sequence_test2'); + +ALTER SEQUENCE sequence_test2 RESTART WITH 16 + INCREMENT BY 4 MAXVALUE 22 MINVALUE 5 CYCLE; +SELECT nextval('sequence_test2'); +SELECT nextval('sequence_test2'); +SELECT nextval('sequence_test2'); + -- 2.40.0