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 <rbt@rbt.ca>

7 files changed, 208 insertions, 46 deletions

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 74068181e40..460c616dd42 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 9ea51d589b5..c043b8b2498 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 3dd552bdd52..3f55d67cffe 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 $
  *
  *-------------------------------------------------------------------------
  */
@@ -1067,6 +1067,15 @@ _equalCreateSeqStmt(CreateSeqStmt *a, CreateSeqStmt *b)
 }
 
 static bool
+_equalAlterSeqStmt(AlterSeqStmt *a, AlterSeqStmt *b)
+{
+	COMPARE_NODE_FIELD(sequence);
+	COMPARE_NODE_FIELD(options);
+
+	return true;
+}
+
+static bool
 _equalVariableSetStmt(VariableSetStmt *a, VariableSetStmt *b)
 {
 	COMPARE_STRING_FIELD(name);
@@ -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 7b28bdc9150..1fa5d957983 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 <node>	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 49432cb957a..83608d95edb 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 ec717d8c122..dd2ed6e500c 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 baf74aa3cc5..4d533633d62 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;