Additional examples, combining sequences with expressions / functions

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS')||MY_SEQ.Nextval INTO Bar FROM DUAL;

SELECT LPAD(MY_SEQ.NEXTVAL,10,'0') INTO Bar FROM DUAL;

The idea I have, from the interface point of view, is to implement

CREATE SEQUENCE foo;

As internally do:

CREATE TABLE foo (seq longlong) ROLLBACK=OFF;
INSERT INTO foo (seq) values (minvalue);

(The user will never see this syntax: The handler will get one new flag
ROLLBACK=OFF to take care of, it supports sequences).

And implement all value handling in the SQL level, including value caching.

What the engine needs to do is:

• No rollback
• It doesn't matter if a middle transaction is lost by being overwritten by a later one.
• When reading and writing, no row locks.
• Upper level will cache values and ensure that we don't do conflicting updates.
• When reading, return latest entry as if read uncommitted.

This is almost aligned with my thinking. I would prefer to omit the INSERT, and I would prefer to some other methods than handler::write_row() or handler::read*() to access the seqeuence object. I would like to see the proposed API.

I think that the minimum that we need is a table entry (both a .frm file and InnoDB data dictionary records, with a flag identifying the table as a sequence pseudo-table. On disk, the data would look like an empty InnoDB table consisting of an empty clustered index root page where PAGE_ROOT_AUTO_INC (introduced in MDEV-6076) stores the sequence value.

If we want to be Oracle compatible, note that nextval is incremented at most once per row processed. This is rather odd and this isn't something used much I guess but it is worth noting and should possibly go into the documentation. See this example:

CREATE SEQUENCE seq1;

CREATE TABLE ta(c1 NUMBER);

CREATE TABLE tb(c1 NUMBER, c2 NUMBER);

INSERT INTO ta VALUES(1);

INSERT INTO ta VALUES(2);

INSERT INTO tb SELECT seq1.nextval, seq1.nextval FROM ta;

SELECT * FROM tb;

This will result in:

        C1         C2

---------- ----------

         1          1

         2          2

And not:

        C1         C2

---------- ----------

         1          2

         3          4

As you might be expecting.

Regarding the comment from Mr Karlsson: Oracle's behaviour – incrementing once per row – is standard and I believe is what other DBMSs do – except PostgreSQL. PostgreSQL exposes a function so its behaviour is "once per invocation".

Peter is absolutely right that this is standard, and that is my mistake of not checking this first. SQL-2003 says this "If there are multiple instances of <next value expression>s specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement.", also this is the behavior exposed by Oracle and SQL Server, as far as I can see. That said, I still think it is somewhat odd but makes some sense. My initial reaction to this was that if you wanted this behavior you really should use currval (which is the Oracle way of asking for the current value from a sequence, in SQL-2003 there is no such construct), but actually that would actually introduce a few issues. For example it would assume a particular execution order inside a particular SQL statement, so there is a reason why the SQL standard actually omits currval completely. Thanks Peter for catching this!

alvinr, I do not know who will do the design review. I implemented the InnoDB part of this yesterday and slightly revised today. The InnoDB changes are almost trivial; the real work takes place above the storage engine layer. As far as I understood, the code by monty is still somewhat incomplete.

First version of sequences 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]

CREATE SEQUENCE is documented at:
https://mariadb.com/kb/en/mariadb/create-sequence/

Pushed into bb-10.2-ext.
Pushed to 10.3.

Hi,
Can I suggest two improvement :
first : support the command "grant select on sequence sequence_name to .."
second : support "alter sequence " on an attribute with the current value.

Example:
create sequence seq1 start with 1;
alter sequence seq1 cache 1;
alter sequence seq1 cache 1;

ERROR 1030 (HY000): Got error 169 "Record was not update. Original values was same as new values" from storage engine InnoDB

Regards.

Still some work to be done. Current state is:

• Everything coded in server, all 'base' tests are done. Now it's up to QA to test the code thoroughly.
• mysqldump will need a small change to dump sequence tables last. (This is worked upon)

Cool!!

I have a question about this, does auto_increment_increment and _offset change anything to sequence behaviour? I guess it could otherwise lead to conflicts in a multi-master cluster.

Yes, auto_increment_increment and _offset will work for sequences defined with INCREMENT=0.
This is documented at:
https://mariadb.com/kb/en/mariadb/sequence-overview/#replication

About GRANT.

Sequences uses normal table grants. To read from state of a sequence, one needs SELECT privilege,
to update the sequence one needs INSERT privilege. To use ALTER SEQUENCE one will need the ALTER privilege.

For migrations from Oracle: Access to SEQUENCE objects through migrated SYNONYM constructs should be checked, e.g. through FederatedX and VIEWs.

Pushed into 10.3 tree a long time ago