CREATE TASK

Documentation

VoltDB Home » Documentation » Using VoltDB

CREATE TASK

CREATE TASK — Schedules a procedure to run periodically.

Synopsis

CREATE TASK task-name
ON SCHEDULE {CRON cron-definition | DELAY time-interval | EVERY time-interval}
PROCEDURE procedure-name [WITH (argument [,...])]
[ON ERROR {LOG | IGNORE | STOP} ]
[RUN ON {DATABASE | PARTITIONS} ]
[AS USER user-name]
[ENABLE | DISABLE]

time-interval: integer {MILLISECONDS | SECONDS | MINUTES | HOURS | DAYS}

Description

The CREATE TASK statement schedules a stored procedure to run iteratively on a set schedule. The PROCEDURE clause specifies the stored procedure to run and any arguments it requires. The ON SCHEDULE clause specifies when the procedure will be run. You can schedule a procedure to run on three types of schedule:

  • CRON — Specifies a cron-style schedule to run the procedure as set times per day or week.

  • DELAY — Specifies a time interval between each run of the stored procedure, where the time interval starts at the end of each run.

  • EVERY — Specifies a time interval between the start of each run of the stored procedure.

The difference between DELAY and EVERY is how the interval is measured. For example, if you specify EVERY 5 SECONDS, the stored procedure runs every 5 seconds, no matter how long it takes to execute (assuming it does not take more than 5 seconds). If, on the other hand, you specify DELAY 5 SECONDS, each run starts 5 seconds after the previous run completes. In other words, EVERY results in invocations at a regular interval no matter how long they take, while DELAY results in a regular interval between when one run ends and the next begins.

For DELAY and EVERY you specify the interval as a positive integer and a time unit, where the supported time units are milliseconds, seconds, minutes, hours, and days. For EVERY, if the previous run takes longer than the interval to run, the schedule is reset at the end of the previous run. So, for example, if the schedule specifies EVERY 2 SECONDS but the procedure takes 2.5 seconds to run, the next scheduled interval will already be past when the previous run ends. In this case, the next invocation of the task is reset to 2 seconds after the previous run ends.

The CRON option requires a standard cron schedule, which consists of six values separated by spaces. Cron schedules set specific times of day, week, or month, rather than an interval. The six values of the cron string represent seconds, minutes, hours, day of the month, month, and day of the week. Asterisks indicate all possible values. For example, the cron specification ON SCHEDULE CRON '0 0 * * * *' schedules the task on the hour, every hour of every day. More information about scheduling tasks with cron can be found on the web.

You can also specify details about how the procedure is run:

  • ON ERROR specifies how errors are handled. The default is ON ERROR STOP.

    • ON ERROR LOG — The error is logged but the procedure continues to be scheduled and run.

    • ON ERROR IGNORE — The procedure continues to be scheduled and run and the error is ignored and not logged.

    • ON ERROR STOP — The error is logged and the scheduling process stops. No further invocations of the procedure will occur until the task is explicitly re-enabled (by using ALTER TASK to disable and then enable the task) or the database restarts.

  • RUN ON specifies where the procedure executes. The default is RUN ON DATABASE.

    • RUN ON DATABASE — For multi-partitioned procedures, each invocation of the procedure is run as a single transaction coordinated across all partitions.

    • RUN ON PARTITIONS — For directed procedures, the procedure is scheduled and run independently on all partitions in the database. Directed procedures are useful for performing distributed tasks that are transactional on each partition but do not need to be coordinated and therefore are less disruptive to the ongoing database workload.

  • AS USER specifies the user account under which the procedure is run. When security is enabled, you must specify a valid username and that user must have sufficient privileges to run the procedure.

When using passive database replication (DR), the replica cluster will automatically pause any scheduled tasks that might modify the database (that is, procedures that are not read-only). If the cluster is promoted, the tasks are resumed.

Finally, you can use the ENABLE and DISABLE keywords to specify whether the task is enabled or not. (The task is enabled by default.) If the task is disabled, the procedure is not invoked. If the task is enabled, the procedure is invoked according to the schedule until the database shuts down or the task is disabled by an ALTER TASK statement or an error while ON ERROR STOP is active.

Examples

The following example declares a procedure to reset the DailyStats view, and a task scheduled as a cron event at midnight every night to run the procedure.

CREATE PROCEDURE ResetDailyStats AS
   DELETE FROM DailyStats;
CREATE TASK nightly 
   ON SCHEDULE CRON '0 0 0 * * *'
   PROCEDURE ResetDailyStats
   RUN ON DATABASE;