Re: TRUNCATE TABLE with IDENTITY - Mailing list pgsql-hackers
| From | Zoltan Boszormenyi |
|---|---|
| Subject | Re: TRUNCATE TABLE with IDENTITY |
| Date | |
| Msg-id | 47FB3653.1080307@cybertec.at Whole thread Raw |
| In response to | Re: TRUNCATE TABLE with IDENTITY (Zoltan Boszormenyi <zb@cybertec.at>) |
| Responses |
Re: TRUNCATE TABLE with IDENTITY
(Zoltan Boszormenyi <zb@cybertec.at>)
|
| List | pgsql-hackers |
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';
pgsql-hackers by date: