Forum: Building VoltDB Applications

Post: how to use the results of GetPartitionKeys procedure?

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

below is a sample app.


CREATE TABLE tx_log_t (
login VARCHAR(32) NOT NULL,
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" ?


thanks.
bballard
Jul 17, 2015
Hi,

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. http://voltdb.com/blog/aging-out-data-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,
Ben