Forum: Building VoltDB Applications

Post: how to use the results of GetPartitionKeys procedure?

how to use the results of GetPartitionKeys procedure?
Jul 17, 2015

below is a sample app.

CREATE TABLE tx_log_t (
detail VARCHAR(32),
operid integer,
tx_commit_date TIMESTAMP

partition procedure PurgeOldData on table tx_log_t column login;

VoltTable[] results = client.callProcedure("@GetPartitionKeys","STRING").getResults();
VoltTable keys = results[0];
for (int k=0;k<keys.getRowCount();k++) {
String key = keys.fetchRow(k).getString(1);

client.callProcedure("PurgeOldData", key);

1. Can I use the following SQL in procedure "PurgeOldData? will it delete the subset of records in all partitions ?
delete from tx_log_t where tx_commit_date < ?;

2. How to use parameter key in procedure "PurgeOldData" ?

Jul 17, 2015

That SQL could result in a lot of records being deleted at once if you didn't run the PurgeOldData procedure for a while. It would be better to limit the number of records that might be deleted at a time, and then to call the procedure with regularity.

This is explained in our blog post on aging out data from VoltDB.

The partition key for the procedure could be defined using a partitioned dummy table, or any arbitrary partitioned table. This is explained in the blog.

Since that blog was written, another option is to use the LIMIT PARTITION ROWS contraint with a DELETE statement, this way deletion is handled automatically whenever a partition gets to the limit row count, and you don't need to periodically call a stored procedure or even have a stored procedure. The difference is that it is triggered by count, not by date.

Best regards,