maria-developers team mailing list archive

[Michael Widenius <michael.widenius@xxxxxxxxx>]

Hi!

For those that are trying to port applications to MariaDB from other
databases or need more features than the current AUTO_INCREMENT
provides, the following may be of interest:

First version of sequence is pushed to bb-10.2-sequence. It should be
merged to bb-10.2-compatilbity and then to 10.3 shortly (1-2 weeks).

What is working for the moment:

CREATE OR REPLACE [TEMPORARY] SEQUENCE [IF NOT EXISTS] name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE ]
[ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ]
ENGINE=xxx COMMENT=".."
SELECT NEXT VALUE FOR sequence_name;
SELECT NEXTVAL(sequence_name);
SELECT PREVIOUS VALUE FOR sequence_name;
SELECT LASTVAL(sequence_name);

SHOW CREATE SEQUENCE sequence_name;
SHOW CREATE TABLE sequence_name;
CREATE TABLE sequence-structure ... SEQUENCE=1
ALTER TABLE sequence RENAME TO sequence2;
RENAME TABLE sequence RENAME TO sequence2;
DROP [TEMPORARY] SEQUENCE sequence_name [IF EXISTS]

See https://jira.mariadb.org/browse/MDEV-10139 for progress.
See commit messages in bb-10.2-sequence for what is still to be done.

Some documentation can be found at:
https://mariadb.com/kb/en/mariadb/create-sequence/
This will be improved as the work progress.

One of the goals with the SEQUENCE implementation is that all old
tools, like mysqldump, should work unchanged, while still keeping
normal usage of sequence standard compatibly.

The make this possible, the sequence is currently implemented as a
table with a few exclusive properties.

The main disadvantage of having sequence as a table is that it uses
the same name space as tables. In other words, you can't have a table
and a sequence of the same name. The benefit is that sequences shows
up in 'show tables', one can create a sequence also with 'create
table' and drop it with 'drop table'.  One can select from it as from
any other table. This ensures that all old tools that works with
tables should work with sequences.

The special properties for sequence tables are:
- A sequence table has always one row.
- When one creates a sequence, either with CREATE TABLE or CREATE
SEQUENCE, one row will be inserted.
- Normal table options works for CREATE SEQUENCE. One can use
ENGINE=xxx, COMMENT=xxx etc.
- If one tries to insert into a sequence table, the single row will be
updated.  This allows mysqldump to work but also gives the additional
benefit that one can change all properties of a sequence with a single
insert. New applications can of course also use ALTER SEQUENCE.
- Updates to the sequence table will change the single row.
- Doing a select on the sequence shows the current state of the
sequence, except the values that are reserved in the cache.  The
column 'next_value' shows the next value not reserved by the cache.
- Truncate on a sequence table will give an error.
- Alter table and rename works on sequences.
- If one creates a sequence with INCREMENT 0, then the sequence will
use auto_increment_increment and auto_increment_offset for the
sequence, just like AUTO_INCREMENT.  This allows sequences to work
reliable in a  master-master environment and with Galera.

Internally sequence tables are created as a normal table without
rollback (InnoDB, Aria and MySAM supports this) with is wrapped by a
sequence engine.  This allowed me to create sequences with almost no
performance impact for normal tables. (The cost is one 'if' per insert
if binary log is enabled).

MariaDB 10.3 will support both the ANSI SQL and Oracle syntax for
creating and accessing sequences. As ANSI SQL doesn't have an easy
access to the last generated value, 103 also supports 'PREVIOUS VALUE
FOR sequence_name', like IBM DB2, and LASTVAL(sequence_name) as
PostgreSQL.

I want to thank Jianwe Zhao from Aliyun for his work on SEQUENCE in
AliSQL which gave me ideas and inspiration for this work.
I also want to thank Marko Mäkelä for his help in the InnoDB part of the code.

There is still a lot of work to fix edge cases, but in the current
implementation most major things seams to work...

Comments, suggestions or questions?

Regards,
Monty