Forum: Building VoltDB Applications

Post: Auto Increment

Auto Increment
henning
Feb 28, 2010
What is the recommended way to create a unique primary key, and/or sequential primary keys?
Since AUTO_INCREMENT is not supported.
Auto increment
tcallaghan
Mar 1, 2010
Henning,
You currently need to create unique and/or sequential PK values in your client application. If you cannot create a surrogate PK scheme in your client application you can create a partitioned table in VoltDB to hold current PK values, similar to the following:
create table t_pks
(table_name varchar(50) not null,
current_pk bigint not null default 1);
Partition this table on table_name and create a stored procedure to retrieve the current value, increment by 1, and update the result back to the row (sending the incremented PK value back to the client application). If you only want uniqueness (across all tables) your PK table could be even simpler (always pass 1 as PK to the stored procedure, rather than passing a table name):
create table t_pks
(pk integer not null,
current_pk bigint not null default 1);
In the callback from the stored procedure (getting the next PK value) call the stored procedure to do the insert logic on the table where you need a generated PK.
-Tim
Standard Practice
henning
Mar 2, 2010
Henning,
You currently need to create unique and/or sequential PK values in your client application. If you cannot create a surrogate PK scheme in your client application you can create a partitioned table in VoltDB to hold current PK values, similar to the following...


Thanks for detailing a possible solution!
Could you enlighten me about a standard practice that I might be missing about how to generate sequential ids on the client side? Surely they'd have the same, even worse problem of synching increments and holding a copy of the highest current value?
I even once ran across a post suggesting to take a database whenever you needed a really reliable sequential count. That it can't be achieved without proper locking mechanisms, just using metaphores and file locking.
Is sequentiality overrated in your eyes?
Or could I anticipate that VoltDB will at some point make this easier? Especially putting your real pay load into the call back of your sequential incrementor call seems inelegant to the point that it adds a potential source of errors?
Thanks,
Henning
Unique vs. Sequential
tcallaghan
Mar 2, 2010
Thanks for detailing a possible solution!
Could you enlighten me about a standard practice that I might be missing about how to generate sequential ids on the client side? Surely they'd have the same, even worse problem of synching increments and holding a copy of the highest current value?
I even once ran across a post suggesting to take a database whenever you needed a really reliable sequential count. That it can't be achieved without proper locking mechanisms, just using metaphores and file locking.
Is sequentiality overrated in your eyes?
Or could I anticipate that VoltDB will at some point make this easier? Especially putting your real pay load into the call back of your sequential incrementor call seems inelegant to the point that it adds a potential source of errors?
Thanks,
Henning


More often than not, I've found that generating unique values is much more valuable than the values themselves being sequential. To accomplish unique/sequential in Oracle you'd use a sequence, and if you grab a value from the sequence and rollback your transaction you've lost those values forever.
The difficult part about client side uniqueness is coordinating across multiple clients. You can still achieve uniqueness by implementing a strategy whereby each client gets a large range of values to work with and no two clients have the same range.
I just coded a PK generating table/stored procedure, let me know if you want a copy.
Long term I suspect we'll add something internal to VoltDB to assist with generating unique values.
-Tim
I would like to see your
yairogen
Jan 3, 2011
More often than not, I've found that generating unique values is much more valuable than the values themselves being sequential. To accomplish unique/sequential in Oracle you'd use a sequence, and if you grab a value from the sequence and rollback your transaction you've lost those values forever.
The difficult part about client side uniqueness is coordinating across multiple clients. You can still achieve uniqueness by implementing a strategy whereby each client gets a large range of values to work with and no two clients have the same range.
I just coded a PK generating table/stored procedure, let me know if you want a copy.
Long term I suspect we'll add something internal to VoltDB to assist with generating unique values.
-Tim


I would like to see your solution as mentioned above.
Any Idea when a sequence like approach will be supported by VoltDB?
Yair
re: Auto Increment
tcallaghan
Mar 1, 2011
Yair,
There are two approaches to generate unique/incrementing values in VoltDB, one may be useful for your needs:
1. Use the internal Transaction ID.
- If you need a unique value from within a stored procedure you can use "getTransactionId()".
- This also allows you a second unique value (the negative representation of the Transaction ID) if you need it.
- The down side to this approach is that it is only useful if this value isn't used as the partitioning column for the table (you need to know the partitioned column value before you call the stored procedure).
2. Create a "sequence" table.
- Create a partitioned table in your schema named sequence_table, columns sequence_name (partition key, VARCHAR(20), NOT NULL, PRIMARY KEY) and sequence_value (LONG, DEFAULT 0, NOT NULL).
- Create a single-partition stored procedure get_next() that checks to see if the sequence exists, inserts a row and returns 1 if not, updates and returns the updated value if so.
- This allows you to call this stored procedure first to get the "sequence" value so you can pass it to other stored procedures.
-Tim