Sequence Generator in MEMSQL

Help
1

We are migrating from Netezza to MEMSQL. However, The sequence generator is integral part of Netezza and it is extensively used in my project. I dont see the corresponding equivalent in MEMSQL. Can someone please suggest How we can go ahead and achieve this in MEMSQL.

We tried using AUTO INCREMENT but it does not solve the problem since the number that gets created is arbitrary and unique for a particular batch run only.

We need unique values getting incremented by 1 till a certain maximum permissible value.

2

You are correct that the AUTO_INCREMENT behavior on sharded tables does not generate sequential IDs. However, the AUTO_INCREMENT behavior on reference tables is much closer to what you want - the next generated value is 1 higher than the previous, except in the case that the MA node restarted (this is because of the way MemSQL reserves batches of values as a performance optimization). SingleStoreDB Cloud · SingleStore Documentation describes the difference between the two.

You can use this as a generator, here is an example using stored procedures:

create reference table seq(i bigint auto_increment primary key);

delimiter //
create procedure genseq()
  returns bigint
  as begin
    start transaction;
    insert into seq values ();
    rollback;
  end//

create function currentid()
  returns bigint
  as begin
    return last_insert_id();
  end//
1 Like
3

Also worth noting that Netezza sequences can have gaps too (IBM Documentation), so the reference table auto_increments should be a good fit for your use case.

4

You can use last_insert_id(expr)

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

E.g.

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);
UPDATE sequence SET id=LAST_INSERT_ID(id+1) where id < 100
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() | 
+------------------+
|                1 |
+------------------+

This example will help you get unique values all incremented by 1 till you reach 100.

Note: LAST_INSERT_ID() will stop changing after id reached 100. Also LAST_INSERT_ID() reset to zero when you connect to memsql (It is seperate value per connection)

last_insert_id(expr) was fixed in MemSQL 6.8