Auto incrementing fields are quite useful, particularly for allocating values to primary keys. MySQL has AUTO_INCREMENT and PostgreSQL has a SERIAL data type. VoltDB has neither, nor anything remotely close to them. This brief article will show you how to simulate auto-incrementing fields in VoltDB. It assumes some knowledge of VoltDB.

VoltDB implements a subset of ANSI-standard SQL. It supports the basic CRUD operations (INSERT, SELECT, UPDATE, DELETE) but it does not have support for automatically generating unique identifiers. It is possible, however, to simulate these in VoltDB, as per this entry in the FAQ. What we can do is create a table that stores the name of the table and the current value that can be used as the unique value for, say, a given column. The schema for the table is shown below:

CREATE TABLE IDENTIFIER (
   TABLE_NAME VARCHAR(100) NOT NULL,
   CURRENT_VALUE INTEGER DEFAULT 1 NOT NULL,
   PRIMARY KEY (TABLE_NAME)
);

The stored procedure looks like this:

import org.voltdb.*;

@ProcInfo (
  partitionInfo = "IDENTIFIER.TABLE_NAME: 0",
  singlePartition = true
)
public class GenerateUniqueIdentifier extends VoltProcedure {

  public final SQLStmt select = new SQLStmt(
    "SELECT CURRENT_VALUE FROM IDENTIFIER WHERE TABLE_NAME = ?"
  );

  public final SQLStmt update = new SQLStmt(
    "UPDATE IDENTIFIER SET CURRENT_VALUE = CURRENT_VALUE + 1 " +
    "WHERE TABLE_NAME = ?"
  );

  public VoltTable[] run(String tableName)
    throws VoltAbortException {

      voltQueueSQL(select, tableName);
      VoltTable[] idResult = voltExecuteSQL();

      voltQueueSQL(update, tableName);
      voltExecuteSQL(true);

      return idResult; // Return the current value for the table
  }
}

As this procedure demonstrates, it is possible to execute multiple SQL statements from inside a stored procedure. It also highlights the advantage of having single-threaded partitions. The table is partitioned on the table name therefore the current value for a given table (each row) is stored in a single partition. As each partition is single-threaded, and stored procedures are run sequentially, there is no risk of the current value for a given table being altered between the time the procedure reads the current value and updates it; this would not be the case in a multi-threaded environment where some kind of locking would have to be used.

With the stored procedure in place, it can be called at will. The returned values can then be used in subsequent calls to other stored procedures. And that is how you can simulate auto-increment in VoltDB. At some point I will upload a complete working solution. Checkout the autoincrement branch of my example VoltDB project to play around with some working code that implements the above. The End.