7.4. Scheduling Stored Procedures as Tasks

Documentation

VoltDB Home » Documentation » Using VoltDB

7.4. Scheduling Stored Procedures as Tasks

There are often repetitive tasks you want to perform on the database that can be scheduled at regular intervals. These tasks may include general cleanup, pruning, or periodic data validation. Rather than write a separate application and scheduler to do this, VoltDB lets you automate tasks at intervals ranging from milliseconds to days.

A task is a stored procedure that you schedule using the CREATE TASK statement. The statement specifies what procedure to run and when to run it and what arguments to use. In the simplest case, you can schedule a multi-partition procedure at specific times of day (using cron notation), at a regular interval (using EVERY), or with a regular pause between iterations (using DELAY). For example, The following statements define a procedure called OrphanedRecords that deletes reservations from a specific airline with no associated flight number and a task called RemoveOrphans that uses that procedure to delete orphaned records for FlyByNight airlines every two hours.

CREATE PROCEDURE OrphanedRecords
  AS DELETE FROM reservations 
     WHERE aireline=? AND flight_id IS NULL;
CREATE TASK RemoveOrphans
  ON SCHEDULE EVERY 2 HOURS
  PROCEDURE OrphanedRecords WITH ('FlyByNight');  

Since the task definition is part of the schema, VoltDB automates starting and stopping the tasks with the database. Other clauses to the CREATE TASK statement let you further refine how the task is run including what user account runs it and what to do in case of errors. There are also corresponding ALTER TASK and DROP TASK statements for managing your task definitions. See the description of the CREATE TASK statement for details.