Forum: VoltDB Architecture

Post: What happen when updating the partition key?

What happen when updating the partition key?
tony7889
Feb 23, 2012
Hi,


I tried to create a table like
CREATE TABLE table_a
(
ID INT
)
and making the table_a as a partitioned table with ID as the partition key


What will actually happen when I do a update SQL like this
UPDATE table_a
SET ID = ID + 1000
WHERE ID = ?


the design principle of Voltdb is single partition operation, but now the update sql may cause the record moving from one to another and touched 2 partitions.


I tried the SQL and it did not return exception, so does it be treated as multiple partition transaction or only 2 partition transaction?


Thanks a lot,


Tony7889
Modifying the partition key
rbetts
Feb 23, 2012
Tony,


I believe to make this work you would have to write a multi-partition procedure that performed a select of the row, a delete, and then an insert combining the new partition id with the previous row data. If you change the partition value in a table, the execution engine will create a constraint violation and rollback the procedure. That is, the storage system will not allow a mis-partitioned tuple to be created via an update.


Ryan.
Burllenz
May 10, 2013
The is also the correct way to write a multi-partition procedure, but this will be a long procedure, instead of this you can use PL/SQL variables and attributes, which will be easy to solve your problem.