Sequence Generator in MEMSQL


#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). https://docs.memsql.com/sql-reference/v6.7/create-table/#auto-increment-in-reference-tables 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//

#3

Also worth noting that Netezza sequences can have gaps too (https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_create_sequence.html), so the reference table auto_increments should be a good fit for your use case.