Zoltan Boszormenyi írta:
Decibel! írta:
On Apr 3, 2008, at 12:52 AM, Zoltan Boszormenyi wrote:
Where is the info in the sequence to provide restarting with
the _original_ start value?


There isn't any. If you want the sequence to start at some magic value, adjust the minimum value.

There's the START WITH option for IDENTITY columns and this below
is paragraph 8 under General rules of 14.10 <truncate table statement>
in 6WD2_02_Foundation_2007-12.pdf (page 902):

8) If RESTART IDENTITY is specified and the table descriptor of T includes a column descriptor IDCD of
  an identity column, then:
a) Let CN be the column name included in IDCD and let SV be the start value included in IDCD. b) The following <alter table statement> is effectively executed without further Access Rule checking:
      ALTER TABLE TN ALTER COLUMN CN RESTART WITH SV

This says that the original start value is used, not the minimum value.
IDENTITY has the same options as CREATE SEQUENCE. In fact the
"identity column specification" links to "11.63 <sequence generator definition>"
when it comes to IDENTITY sequence options. And surprise, surprise,
"11.64 <alter sequence generator statement>" now defines
ALTER SEQUENCE sn RESTART [WITH newvalue]
where omitting the "WITH newval" part also uses the original start value.

Best regards,
Zoltán Böszörményi

Attached patch implements the extension found in the current SQL200n draft,
implementing stored start value and supporting ALTER SEQUENCE seq RESTART;
Some error check are also added to prohibit CREATE SEQUENCE ... RESTART ...
and ALTER SEQUENCE ... START ...

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

diff -dcrpN pgsql.orig/src/backend/commands/sequence.c pgsql/src/backend/commands/sequence.c
*** pgsql.orig/src/backend/commands/sequence.c	2008-01-01 20:45:49.000000000 +0100
--- pgsql/src/backend/commands/sequence.c	2008-04-08 10:51:27.000000000 +0200
*************** static Relation open_share_lock(SeqTable
*** 88,94 ****
  static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
  static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
  static void init_params(List *options, bool isInit,
! 			Form_pg_sequence new, List **owned_by);
  static void do_setval(Oid relid, int64 next, bool iscalled);
  static void process_owned_by(Relation seqrel, List *owned_by);
  
--- 88,94 ----
  static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
  static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
  static void init_params(List *options, bool isInit,
! 			Form_pg_sequence new, Form_pg_sequence old, List **owned_by);
  static void do_setval(Oid relid, int64 next, bool iscalled);
  static void process_owned_by(Relation seqrel, List *owned_by);
  
*************** DefineSequence(CreateSeqStmt *seq)
*** 116,122 ****
  	NameData	name;
  
  	/* Check and set all option values */
! 	init_params(seq->options, true, &new, &owned_by);
  
  	/*
  	 * Create relation (and fill *null & *value)
--- 116,122 ----
  	NameData	name;
  
  	/* Check and set all option values */
! 	init_params(seq->options, true, &new, NULL, &owned_by);
  
  	/*
  	 * Create relation (and fill *null & *value)
*************** DefineSequence(CreateSeqStmt *seq)
*** 143,148 ****
--- 143,153 ----
  				namestrcpy(&name, seq->sequence->relname);
  				value[i - 1] = NameGetDatum(&name);
  				break;
+ 			case SEQ_COL_STARTVAL:
+ 				coldef->typename = makeTypeNameFromOid(INT8OID, -1);
+ 				coldef->colname = "start_value";
+ 				value[i - 1] = Int64GetDatumFast(new.start_value);
+ 				break;
  			case SEQ_COL_LASTVAL:
  				coldef->typename = makeTypeNameFromOid(INT8OID, -1);
  				coldef->colname = "last_value";
*************** AlterSequence(AlterSeqStmt *stmt)
*** 336,342 ****
  	memcpy(&new, seq, sizeof(FormData_pg_sequence));
  
  	/* Check and set new values */
! 	init_params(stmt->options, false, &new, &owned_by);
  
  	/* Clear local cache so that we don't think we have cached numbers */
  	/* Note that we do not change the currval() state */
--- 341,347 ----
  	memcpy(&new, seq, sizeof(FormData_pg_sequence));
  
  	/* Check and set new values */
! 	init_params(stmt->options, false, &new, seq, &owned_by);
  
  	/* Clear local cache so that we don't think we have cached numbers */
  	/* Note that we do not change the currval() state */
*************** read_info(SeqTable elm, Relation rel, Bu
*** 967,973 ****
   */
  static void
  init_params(List *options, bool isInit,
! 			Form_pg_sequence new, List **owned_by)
  {
  	DefElem    *last_value = NULL;
  	DefElem    *increment_by = NULL;
--- 972,978 ----
   */
  static void
  init_params(List *options, bool isInit,
! 			Form_pg_sequence new, Form_pg_sequence old, List **owned_by)
  {
  	DefElem    *last_value = NULL;
  	DefElem    *increment_by = NULL;
*************** init_params(List *options, bool isInit,
*** 995,1003 ****
  		/*
  		 * start is for a new sequence restart is for alter
  		 */
! 		else if (strcmp(defel->defname, "start") == 0 ||
! 				 strcmp(defel->defname, "restart") == 0)
  		{
  			if (last_value)
  				ereport(ERROR,
  						(errcode(ERRCODE_SYNTAX_ERROR),
--- 1000,1023 ----
  		/*
  		 * start is for a new sequence restart is for alter
  		 */
! 		else if (strcmp(defel->defname, "start") == 0)
  		{
+ 			if (!isInit)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("use ALTER SEQUENCE ... RESTART ...")));
+ 			if (last_value)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("conflicting or redundant options")));
+ 			last_value = defel;
+ 		}
+ 		else if (strcmp(defel->defname, "restart") == 0)
+ 		{
+ 			if (isInit)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("use CREATE SEQUENCE ... START ...")));
  			if (last_value)
  				ereport(ERROR,
  						(errcode(ERRCODE_SYNTAX_ERROR),
*************** init_params(List *options, bool isInit,
*** 1109,1124 ****
  	/* START WITH */
  	if (last_value != NULL)
  	{
! 		new->last_value = defGetInt64(last_value);
  		new->is_called = false;
  		new->log_cnt = 1;
  	}
  	else if (isInit)
  	{
  		if (new->increment_by > 0)
! 			new->last_value = new->min_value;	/* ascending seq */
  		else
! 			new->last_value = new->max_value;	/* descending seq */
  		new->is_called = false;
  		new->log_cnt = 1;
  	}
--- 1129,1149 ----
  	/* START WITH */
  	if (last_value != NULL)
  	{
! 		if (last_value->arg != NULL)
! 			new->last_value = defGetInt64(last_value);
! 		else
! 			new->last_value = old->start_value;
! 		if (isInit)
! 			new->start_value = new->last_value;
  		new->is_called = false;
  		new->log_cnt = 1;
  	}
  	else if (isInit)
  	{
  		if (new->increment_by > 0)
! 			new->start_value = new->last_value = new->min_value;	/* ascending seq */
  		else
! 			new->start_value = new->last_value = new->max_value;	/* descending seq */
  		new->is_called = false;
  		new->log_cnt = 1;
  	}
diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
*** pgsql.orig/src/backend/parser/gram.y	2008-03-30 13:29:47.000000000 +0200
--- pgsql/src/backend/parser/gram.y	2008-04-08 10:32:25.000000000 +0200
*************** OptSeqElem: CACHE NumericOnly
*** 2486,2491 ****
--- 2486,2495 ----
  				{
  					$$ = makeDefElem("start", (Node *)$3);
  				}
+ 			| RESTART
+ 				{
+ 					$$ = makeDefElem("restart", NULL);
+ 				}
  			| RESTART opt_with NumericOnly
  				{
  					$$ = makeDefElem("restart", (Node *)$3);
diff -dcrpN pgsql.orig/src/include/commands/sequence.h pgsql/src/include/commands/sequence.h
*** pgsql.orig/src/include/commands/sequence.h	2008-03-30 13:29:50.000000000 +0200
--- pgsql/src/include/commands/sequence.h	2008-04-08 10:22:28.000000000 +0200
*************** typedef struct FormData_pg_sequence
*** 29,34 ****
--- 29,35 ----
  {
  	NameData	sequence_name;
  #ifndef INT64_IS_BUSTED
+ 	int64		start_value;
  	int64		last_value;
  	int64		increment_by;
  	int64		max_value;
*************** typedef struct FormData_pg_sequence
*** 36,53 ****
  	int64		cache_value;
  	int64		log_cnt;
  #else
! 	int32		last_value;
  	int32		pad1;
! 	int32		increment_by;
  	int32		pad2;
! 	int32		max_value;
  	int32		pad3;
! 	int32		min_value;
  	int32		pad4;
! 	int32		cache_value;
  	int32		pad5;
! 	int32		log_cnt;
  	int32		pad6;
  #endif
  	bool		is_cycled;
  	bool		is_called;
--- 37,56 ----
  	int64		cache_value;
  	int64		log_cnt;
  #else
! 	int32		start_value;
  	int32		pad1;
! 	int32		last_value;
  	int32		pad2;
! 	int32		increment_by;
  	int32		pad3;
! 	int32		max_value;
  	int32		pad4;
! 	int32		min_value;
  	int32		pad5;
! 	int32		cache_value;
  	int32		pad6;
+ 	int32		log_cnt;
+ 	int32		pad7;
  #endif
  	bool		is_cycled;
  	bool		is_called;
*************** typedef FormData_pg_sequence *Form_pg_se
*** 60,73 ****
   */
  
  #define SEQ_COL_NAME			1
! #define SEQ_COL_LASTVAL			2
! #define SEQ_COL_INCBY			3
! #define SEQ_COL_MAXVALUE		4
! #define SEQ_COL_MINVALUE		5
! #define SEQ_COL_CACHE			6
! #define SEQ_COL_LOG				7
! #define SEQ_COL_CYCLE			8
! #define SEQ_COL_CALLED			9
  
  #define SEQ_COL_FIRSTCOL		SEQ_COL_NAME
  #define SEQ_COL_LASTCOL			SEQ_COL_CALLED
--- 63,77 ----
   */
  
  #define SEQ_COL_NAME			1
! #define SEQ_COL_STARTVAL		2
! #define SEQ_COL_LASTVAL			3
! #define SEQ_COL_INCBY			4
! #define SEQ_COL_MAXVALUE		5
! #define SEQ_COL_MINVALUE		6
! #define SEQ_COL_CACHE			7
! #define SEQ_COL_LOG			8
! #define SEQ_COL_CYCLE			9
! #define SEQ_COL_CALLED			10
  
  #define SEQ_COL_FIRSTCOL		SEQ_COL_NAME
  #define SEQ_COL_LASTCOL			SEQ_COL_CALLED
diff -dcrpN pgsql.orig/src/test/regress/expected/sequence.out pgsql/src/test/regress/expected/sequence.out
*** pgsql.orig/src/test/regress/expected/sequence.out	2006-08-21 02:57:26.000000000 +0200
--- pgsql/src/test/regress/expected/sequence.out	2008-04-08 10:58:18.000000000 +0200
*************** DROP SEQUENCE sequence_test;
*** 99,107 ****
  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;
--- 99,107 ----
  CREATE SEQUENCE foo_seq;
  ALTER TABLE foo_seq RENAME TO foo_seq_new;
  SELECT * FROM foo_seq_new;
!  sequence_name | start_value | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
! ---------------+-------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
!  foo_seq       |           1 |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f
  (1 row)
  
  DROP SEQUENCE foo_seq_new;
*************** SELECT nextval('sequence_test2');
*** 175,180 ****
--- 175,212 ----
         5
  (1 row)
  
+ ALTER SEQUENCE sequence_test2 RESTART
+ 	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ SELECT nextval('sequence_test2');
+  nextval 
+ ---------
+       32
+ (1 row)
+ 
+ SELECT nextval('sequence_test2');
+  nextval 
+ ---------
+       36
+ (1 row)
+ 
+ SELECT nextval('sequence_test2');
+  nextval 
+ ---------
+        5
+ (1 row)
+ 
+ SELECT nextval('sequence_test2');
+  nextval 
+ ---------
+        9
+ (1 row)
+ 
+ SELECT nextval('sequence_test2');
+  nextval 
+ ---------
+       13
+ (1 row)
+ 
  -- Test comments
  COMMENT ON SEQUENCE asdf IS 'won''t work';
  ERROR:  relation "asdf" does not exist
diff -dcrpN pgsql.orig/src/test/regress/sql/sequence.sql pgsql/src/test/regress/sql/sequence.sql
*** pgsql.orig/src/test/regress/sql/sequence.sql	2005-10-03 01:50:16.000000000 +0200
--- pgsql/src/test/regress/sql/sequence.sql	2008-04-08 10:56:55.000000000 +0200
*************** SELECT nextval('sequence_test2');
*** 74,79 ****
--- 74,88 ----
  SELECT nextval('sequence_test2');
  SELECT nextval('sequence_test2');
  
+ ALTER SEQUENCE sequence_test2 RESTART
+ 	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ 
+ SELECT nextval('sequence_test2');
+ SELECT nextval('sequence_test2');
+ SELECT nextval('sequence_test2');
+ SELECT nextval('sequence_test2');
+ SELECT nextval('sequence_test2');
+ 
  -- Test comments
  COMMENT ON SEQUENCE asdf IS 'won''t work';
  COMMENT ON SEQUENCE sequence_test2 IS 'will work';
-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Reply via email to