This is a MIME-formatted message. If you see this text it means that your
E-mail software does not sup****t MIME-formatted messages.
--=_mailrelay-23453-1207646098-0001-2
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Zoltan Boszormenyi =EDrta:
> Decibel! =EDrta:
>> 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=20
>> 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=20
> 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=20
> start value included in IDCD.
> b) The following <alter table statement> is effectively executed=20
> 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=20
> 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=E1n B=F6sz=F6rm=E9nyi
Attached patch implements the extension found in the current SQL200n
draft,
implementing stored start value and sup****ting ALTER SEQUENCE seq RESTART;
Some error check are also added to prohibit CREATE SEQUENCE ... RESTART
...
and ALTER SEQUENCE ... START ...
Best regards,
Zolt=E1n B=F6sz=F6rm=E9nyi
--=20
----------------------------------
Zolt=E1n B=F6sz=F6rm=E9nyi
Cybertec Sch=F6nig & Sch=F6nig GmbH
http://www.postgresql.at/
--=_mailrelay-23453-1207646098-0001-2
Content-Type: text/x-patch; name="sql2008-compliant-seq.patch";
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="sql2008-compliant-seq.patch"
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)
ere****t(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)
+ ere****t(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("use ALTER SEQUENCE ... RESTART ...")));
+ if (last_value)
+ ere****t(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ last_value = defel;
+ }
+ else if (strcmp(defel->defname, "restart") == 0)
+ {
+ if (isInit)
+ ere****t(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("use CREATE SEQUENCE ... START ...")));
if (last_value)
ere****t(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';
--=_mailrelay-23453-1207646098-0001-2
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
--
Sent via pgsql-patches mailing list (pgsql-patches@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
--=_mailrelay-23453-1207646098-0001-2--
|
