WL#827: SEQUENCE object as in Oracle, PostgreSQL, and/or SQL:2003

In Oracle, PostgreSQL, and in SQL:2003, there are SEQUENCE objects which have
various options (to specify what the first number is, what the increment value
is, whether a range of numbers is cached, etc.). Sequences are separate objects 
rather than part of tables. Sequence values are dissociated from table 
storage, therefore deletes do not affect them and rollbacks do not affect 
them. 

Oracle implementation:
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6016.htm#SQLRF01314

PostgreSQL implementation:
http://www.postgresql.org/docs/9.3/static/sql-createsequence.html


An article "Sequences and Identity Columns" 
(http://web.archive.org/web/20021201224701/www.dbazine.com/gulutzan4.html)
describes the matter more fully. 
 
I added this item at Sinisa's request. 
 
In the Bordeaux group leaders' meeting (November 2003), 
"Oracle-type sequences" was specified as an item for 
version 5.1. 

CREATE SEQUENCE 
--------------- 

Syntax: 

CREATE SEQUENCE sequence_name [option [,option ...]]; 
 
The sequence_name is like other identifiers: maximum 
length 64, stored in UTF-8, qualifiable with catalog.schema, 
case insensitive, and not the same as any existing 
sequence in the same schema (i.e. in the same database).
Related to dev-private discussion "Identifier equivalence and events":
"Identifiers are equivalent if their upper-case forms are
equal according to a binary collation with space padding."

An option may be any one of these, in any order: 
AS data_type 
INCREMENT BY increment 
MAXVALUE maximum_value | NO MAXVALUE 
MINVALUE minimum_value | NO MINVALUE 
START WITH start_value 
CYCLE | NO CYCLE 
CACHE cache_value | NO CACHE 
NO ORDER 
 
No option may be specified more than once. 

The "NO ORDER" clause exists for compatibility reasons. 
Ignore it. 

Initially, accept any signed big integer for increment, 
maximum_value, minimum_value, start_value, and cache_value. 
 
For "AS data_type": allowable values are any one of 
TINYINT, SMALLINT, MEDIUMINT, INT | INTEGER, BIGINT, 
TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, 
INT | INTEGER UNSIGNED, BIGINT UNSIGNED, DECIMAL(n), 
or NUMERIC(n). DEFAULT is BIGINT. (The default
is implementation-defined, if somebody thinks INTEGER 
or SMALLINT is better, okay.) In reality 
we should store everything as BIGINT; however, the data 
type specified here will affect the calculation of default 
min/max values (see next paragraph), and the data type of 
the function that retrieves the next sequence value (see 
later section titled "NEXT VALUE FOR"). 

After all options are parsed, set default values and check 
for errors with these steps, in order: 
1. Given the data type, calculate Default_min_value and 
   Default_max_value according to the range limits specified 
   in the MySQL Reference Manual 
   http://www.mysql.com/doc/en/Column_types.html 
   For example if the specification was "AS SMALLINT" then 
   the data type is SMALLINT, the Default min_value = -32768, 
   and the Default max_value = 32767. 
2. If "NO MAXVALUE" was specified, or if no MAXVALUE clause 
   was specified, then maximum_value = Default_maximum_value. 
3. If "NO MINVALUE" was specified, or if no MINVALUE clause 
   was specified, then minimum_value = Default_minimum_value. 
4. If no INCREMENT clause was specified, then increment = 1. 
5. If no START WITH clause was specified, then if increment < 0 
   start_with = maximum_value, else (if increment > 0) 
   start_with = minimum_value. 
6. If no CYCLE clause was specified, then it's 'NO CYCLE'. 
7. If "NO CACHE" was specified, or if no CACHE clause 
   was specified, then cache_value = 20. 
8. The following are syntax errors (return sqlstate = '42000'): 
   - if maximum_value > Default_maximum_value 
   - if minimum_value < Default_minimum_value 
   - if minimum_value >= maximum_value 
   - if start_value < minimum_value or start_value > maximum_value 
   - if increment = 0 or abs(increment) > (max_value - min_value) 
   - if cache_value <= 0 
 
Privileges required: Not yet known. We have CREATE VIEW 
and CREATE ROUTINE privileges, so doubtless we should have a
CREATE SEQUENCE privilege. 
 
Oracle alternate syntax: Oracle has NOMAXVALUE for NO MAXVALUE, 
NOMINVALUE for NO MINVALUE, NOCYCLE for NO CYCLE. Let's not do that. 
 
Correctly speaking, the thing that CREATE SEQUENCE creates 
is not a "sequence", but a "sequence generator". Peter Gulutzan
finds it hard to remember to say "sequence generator" but we should 
all try. 

This is an example, using all options but with default values: 
 
CREATE SEQUENCE sequence1_seq 
AS BIGINT 
INCREMENT BY 1 
MAXVALUE    9223372036854775807 
MINVALUE   -9223372036854775808 
START WITH -9223372036854775808 
NO CYCLE 
CACHE 20 
NO ORDER 
 
mysql.sequences 
--------------- 
 
A sequence generator is a different object from the ones we already have 
(users, tables, routines). And sequence generators have their own namespace. 
And they are within a schema. Peter proposes this structure because most of
the column names are like what we'll need in information_schema.sequences: 

sequence_catalog        varchar(64)      i.e. catalog (depends on WL#942) 
sequence_schema         varchar(64)      i.e. schema. don't call it `db` 
sequence_name           varchar(64) 
data_type               varchar(64)      e.g. 'UNSIGNED SMALLINT'. or a number. 
numeric_precision       smallint         e.g. 8 for DECIMAL(8) 
maximum_value           bigint 
minimum_value           bigint 
increment               bigint 
cycle_option            varchar(3)       'YES' or 'NO', 'YES' means 'CYCLE' 
start_value             bigint 
cache_value             integer 
current_base_value      bigint           this should initially be NULL 

There will be additional columns for internal sequence generators, 
associated with columns. 
 
CREATE SEQUENCE should insert a row in this table. 
DROP SEQUENCE should delete a row in this table. 
ALTER SEQUENCE should update a row in this table. 
 
For precedents, see what we already do for create/drop/alter procedure 
and mysql.proc. 
 
DROP SEQUENCE 
------------- 
 
Syntax: 
 
DROP SEQUENCE sequence_name; 
 
The standard actually requires "DROP SEQUENCE sequence_name CASCADE|RESTRICT". 
However, since Peter expects we won't actually support cascade|restrict, 
and it's a sin to lie, we cannot use the cascade|restrict clause. 
 
Privileges required: DROP. If somebody thinks it should be a special 
DROP SEQUENCE privilege, that's okay too. 
 
ALTER SEQUENCE 
-------------- 
 
Syntax: 
 
ALTER SEQUENCE sequence_name [option [,option ...]]; 
 
The possible options are the same as for CREATE SEQUENCE, 
and the restrictions are the same as for CREATE SEQUENCE. 
Except for one clause: Instead of START WITH start_value, 
one must say RESTART WITH start_value. 

SQL:2011 non-core feature T177 Sequence generator support: simple restart option
allows ALTER SEQUENCE ... RESTART ...
 
NEXT VALUE FOR: Syntax 
---------------------- 
 
Syntax: NEXT VALUE FOR schema_name 
 
You may use NEXT VALUE for in a SELECT list, for example 
SELECT NEXT VALUE FOR catalog1 . schema1 . special_seq; 
The SELECT must not contain DISTINCT (this restriction seems 
unnecessary, lift it if you wish). 
 
You may use NEXT VALUE FOR as a source in an UPDATE 
statement, for example 
UPDATE t SET column1 = NEXT VALUE FOR schema1.special_seq; 
 
You may use NEXT VALUE FOR for an INSERT statement, for example 
INSERT INTO t VALUES (NEXT VALUE FOR special_seq); 
 
And that's all. 
 
You may not use NEXT VALUE FOR anywhere else. 
For example, these statements are illegal: 
SELECT * FROM t WHERE column1 = NEXT VALUE FOR special_seq; 
SET @a = NEXT VALUE FOR special_seq; 
 
You must have USAGE privilege on the sequence (see "Privilege" below). 
 
MySQL should evaluate a NEXT VALUE FOR expression once per 
selected row. 
For example: 
CREATE TABLE t1 (s1 INT); 
INSERT INTO t1 VALUES (1),(2); 
CREATE SEQUENCE seq ... 
SELECT s1, NEXT VALUE FOR seq, NEXT VALUE FOR seq FROM t1; 
The result will be: 
{1, 1, 1} 
{2, 2, 2} 
 
NEXT VALUE FOR: Simple Rules 
---------------------------- 
 
For any given sequence ... (words in CAPITALS are column names
in mysql.sequence)

if CURRENT_BASE_VALUE is null { 
  /* current_base_value is null only after "create/alter sequence", 
     so we get here only if we have never called "next value" before */ 
  set CURRENT_BASE_VALUE = START_VALUE 
  return (CURRENT_BASE_VALUE) 
  } 

set x = CURRENT_BASE_VALUE + INCREMENT (beware of overflow) 
if (INCREMENT > 0 and x > MAX_VALUE) 
or (INCREMENT < 0 AND x < MIN_VALUE) { 
  if (CYCLE_OPTION = 'no') { 
    return error: 
    sqlstate = '22003' 
    message =  "data exception: sequence generator limit exceeded" 
    } 
  if (CYCLE_OPTION = 'yes') { 
    if (INCREMENT > 0) set CURRENT_BASE_VALUE = MIN_VALUE 
    if (INCREMENT < 0) set CURRENT_BASE_VALUE = MAX_VALUE 
    set x = CURRENT_BASE_VALUE + INCREMENT 
    } 
  } 
return (x) 
 
PROBLEM: Whenever you make a change to CURRENT_BASE_VALUE, 
you are changing a row in the mysql.sequences table, so a 
lock|read|write|unlock process may be necessary. Of course 
fsync would be good too. 
 
NEXT VALUE FOR: Reducing The Problem 
------------------------------------ 
 
This section is not what ANSI/ISO requires. It is what others 
do, with modifications that (Peter thinks) could suit MySQL. 
 
The problem described in the last section ("NEXT VALUE FOR: 
Simple Rules") can be reduced in one of two ways: 
 
1. Do not store CURRENT_BASE_VALUE in mysql.sequences. Store 
   it in the log. Although this is feasible for InnoDB 
   there is no guarantee that we can do it at all times, so 
   let's look at this as a (very good) enhancement option. 
 
2. Do not lock|read|write|unlock the mysql.sequences row 
   every time that NEXT VALUE FOR happens. Instead, get a 
   batch of numbers. 
 
Let's concentrate solely on the second solution. 
 
For every session, there is a "local copy" of every sequence 
that the session has used (i.e. has called NEXT VALUE FOR for). 
The idea is that we will keep a "cache" of N numbers available 
in memory. There is also, for each local copy, a bigint named 
used_number_count. 
 
When NEXT VALUE FOR happens: 
 
1. If there is no local copy of the sequence, make a local copy 
   and set used_number_count = CACHE_SIZE. 
 
2. If used_number_count = CACHE_SIZE, do "The Grab". That is, 
   Perform "NEXT VALUE FOR: Simple Rules" as described above. 
   But instead of using INCREMENT, use INCREMENT * CACHE_VALUE. 
 
So the revision of the "Simple Rules" looks like this: 
 
if {used_number_count == 0) { 
 
  if CURRENT_BASE_VALUE is null { 
    /* current_base_value is NULL only after "create/alter sequence", 
       so we get here only if we have never called "next value" before */ 
    set x = CURRENT_BASE_VALUE 
    set CURRENT_BASE_VALUE = START_VALUE 
        + (CACHE_VALUE * (INCREMENT - 1)) 
    return (x) 
    } 
 
  set x = CURRENT_BASE_VALUE + INCREMENT * CACHE_VALUE (beware of overflow) 
  if (INCREMENT > 0 and x > MAX_VALUE) 
  or (INCREMENT < 0 AND x < MIN_VALUE) { 
    if (CYCLE_OPTION = 'no') { 
      return error: 
      sqlstate = '22003' 
      message =  "data exception: sequence generator limit exceeded" 
      } 
    if (CYCLE_OPTION = 'yes') { 
      if (INCREMENT > 0) set CURRENT_BASE_VALUE = MIN_VALUE 
      if (INCREMENT < 0) set CURRENT_BASE_VALUE = MAX_VALUE 
      set x = CURRENT_BASE_VALUE + INCREMENT 
      set CURRENT_BASE_VALUE = CURRENT_BASE_VALUE + INCREMENT * CACHE_SIZE 
      } 
    } 
  return (x) 
  } 
 
Now we have x, which is the first useful "NEXT VALUE FOR" value. 
Put this in the local copy, as (local)CURRENT_BASE_VALUE. 
Also, set used_number_count = 0. 
 
And now, to return. 
 
set return_value = (local)CURRENT_BASE_VALUE + (INCREMENT * used_number_count); 
set used_number_count = used_number_count + 1; 
return (return_value); 
 
To sum it up: if CACHE_SIZE is 20, then there will be an access of 
the mysql.sequences row only once for every twenty calls to NEXT 
VALUE FOR. The other nineteen times, the access will be of a local 
unlocked in-memory copy of the mysql.sequences row. 
 
When the session ends, there will almost certainly be some numbers 
still "in the cache". They are lost. They will never be used. So 
you probably won't have a dense series of numbers unless you specify 
"CREATE SEQUENCE ... CACHE 1". But generally you don't need a 
dense series, and generally it's acceptable to lose 20 numbers 
when you have 18,446,744,073,709,551,615 available. 
 
For another, possibly simpler, description of the whole technique, 
see the article "Sequences and Identify Columns" in DBAZINE.com: 
http://web.archive.org/web/20021201224701/www.dbazine.com/gulutzan4.html.
 
CURRVAL and PREVVAL 
------------------- 

This is a non-standard extension. Oracle uses CURRVAL, DB2 uses 
PREVVAL. They mean the same thing. 
 
SELECT PREVVAL FOR sequence_name;           /* DB2 style */ 
SELECT sequence_name.CURRVAL;               /* Oracle style */ 
 
This function will retrieve the local copy. So if there is no 
local copy (because the session never called NEXT VALUE FOR), 
the result is meaningless, return NULL. 
 
This is similar to LAST_INSERT_ID(), so perhaps some people 
would prefer to see LAST_INSERT_ID (sequence_name). Peter doesn't. 
 
Privilege 
--------- 
 
In order to use a sequence generator, you need the USAGE privilege 
on it. To grant a usage privilege, say: 
 
GRANT USAGE ON SEQUENCE name TO user-list [WITH GRANT OPTION]; 
 
To revoke a usage privilege, say: 
 
REVOKE USAGE ON SEQUENCE name FROM user-list; 
 
The MySQL extensions like "GRANT|REVOKE USAGE ON database_name.*" 
as described in the MySQL Reference Manual, 
http://www.mysql.com/doc/en/GRANT.html 
are okay. 
 
New reserved words 
------------------ 
 
New reserved words are: CYCLE, START. 

WITH is already a reserved word.

INCREMENT, MAXVALUE, MINVALUE, SEQUENCE are not new reserved words. 
 
SHOW 
---- 

For other new object types that we've introduced into MySQL,
we've added support for corresponding SHOW CREATE statements.
For example SHOW CREATE TRIGGER, SHOW CREATE EVENT. So support:
SHOW CREATE SEQUENCE sequence_name;
This will show a CREATE SEQUENCE statement that would reproduce
the current object identified by sequence_name.
See also WL#3574 "Online backup: SHOW commands for various metadata"
and WL#3582 "Online Backup: Add SHOW commands for needed metadata".

There will not be a SHOW SEQUENCES statement. To get metadata
about sequences, there should be an information_schema table.
The description of information_schema.sequences may be a separate task.
Meanwhile one can use mysql.sequences.

You need a privilege on sequence_name in order to access its
metadata. The privilege could be either USAGE or CREATE SEQUENCE.

Replication 
----------- 
 
There could be some complications with replication if we 
used a log to store CURRENT_BASE_VALUE, but that's not the 
initial plan. 
 
There certainly is a problem with NEXT VALUE FOR if one 
uses the binary log. Once again, we have a function value 
that must be passed for every statement. 
 
Compatibility 
------------- 
 
Once we have all the details in WL#827, we can claim support 
for SQL:2003 feature T176 "Sequence generator support". We 
won't guarantee that no numbers will be lost, and it's possible 
that they won't be in temporal order. 
 
The CACHE clause and the NEXTVAL/CURRVAL function are 
extensions used by Oracle and DB2. DB2 would also allow 
an INCREMENT size of 0, we don't. There are slight 
differences in the CREATE SEQUENCE syntax, e.g. placement of 
commas or use of NOMINVALUE instead of NO MINVALUE, but 
the required change should be obvious to any user. The 
same is true for the function NEXT VALUE FOR, which has 
the spelling NEXTVAL FOR in DB2. 
 
auto_increment 
-------------- 

We won't depend on MySQL's auto_increment feature. 
Peter's hope is that eventually we can throw the auto_increment feature away. 

References
----------

WL#1295 "Implement AUTO_DECREMENT" -- will be unnecessary if we do WL#827 

WL#1327 "DB2-type IDENTITY columns" -- could be made to depend on sequences 

BUG#1625 Built in sequence support (Feature Request)

People have asked about CREATE SEQUENCE several times in MySQL Forums:
http://forums.mysql.com/read.php?10,147719,147719
http://lists.mysql.com/mysql/178439
http://forums.mysql.com/read.php?61,113934,113934
http://forums.mysql.com/read.php?10,127559,127559