QSched Task Scheduler

QSched — built-in task scheduler for QHB.

Description

User tasks are executed by a set of background processes that are unique for each database. The basis for the scheduler is a set of system tables with the prefix pg_catalog.qsched_..., which is available for viewing by the database administrator and the owner of the database.

To create and schedule tasks, the SCHED privilege is provided, which is assigned by the database administrator to users who have the permission to work with this database.

Usage

Assigning the Permission to Work with QSched

The administrator/database owner has the privilege to grant any user the permission to work with the scheduler:

-- GRANT CONNECT ON DATABASE ... TO ...;
GRANT SCHED ON DATABASE ... TO ...;

Starting Scheduler While the DBMS is Running

SELECT start_qsched_master();
-- The procedure will return the scheduler PID for the given database.

Starting Scheduler at the DBMS Starting

To start the scheduler when the DBMS starts, you need to add with_qsched parameter to the qhb.conf file and specify for which databases and as which users to run. It is important that both the database and the user exist, and also that the user has the necessary permissions to work with database, otherwise the DBMS start will be interrupted.

# Adding automatic Qsched start for databases dbname1 and db_name2
echo with_qsched = 'db_owner1:db_name1, db_owner2:db_name2' >> $PGDATA/qhb.conf;

# Adding automatic Qsched start for database dbname1 only
echo with_qsched = 'db_owner1:db_name1' >> $PGDATA/qhb.conf;

Checking Scheduler Status

SELECT status_qsched_master();
-- The procedure will return the scheduler boolean value, which indicates whether
-- the "master" is running (true) or not (false) for this database.

Stopping the Scheduler

SELECT stop_qsched_master();
-- The procedure will return completed process ID of the scheduler for this database.

Task Creating

Tasks are created and scheduled via the SQL interface provided in scheduler QSched:

-- General example form:
SELECT qsched_job_create_{proc,block,query}('job name', 'job payload');
-- The procedure will return JOB_ID:Oid.

-- Example of immediate execution (for debugging):
SELECT qsched_job_exec(/* task id */ 1);

-- Specific function to create a task with query as a content:
SELECT qsched_job_create_query('job name', 'SELECT 123;');
-- The procedure will return JOB_ID:Oid.

-- A specific function for creating a task with plpgsql block as a content
-- (in this example — an endless loop):
SELECT qsched_job_create_block('job name', 'BEGIN LOOP PERFORM pg_sleep(1); END LOOP; END;')
-- The procedure will return JOB_ID:Oid.

-- Specific function to create a task with stored procedure as a content
-- (the existence of the 'proc_name' procedure is checked during runtime):
SELECT qsched_job_create_proc('job name', 'proc_name');
-- The procedure will return JOB_ID:Oid.

Notes on the Running Task Contents

When creating a task, you can specify a procedure that does not currently exist, which name will be resolved upon execution (relative to the current search_path value), or you can specify the procedure with the schema name straightaway:

SELECT qsched_job_create_proc('job name','my_schema.my_proc');

To create a task with a variable set of arguments, but a common target function It is useful to use the syntax with plpgsql blocks:

-- An example of using an anonymous block to commit the argument values
-- (arguments can also be calculated inside an anonymous block):
SELECT qsched_job_create_block('job name', $$BEGIN PERFORM my_proc('my_arg1', 'my_arg2'); END;$$);

Creating a Schedule

A task is scheduled by one or more periodic schedules with cron-like syntax:

-- Forces schedule to run every 30 seconds:
SELECT qsched_schedule_create(/* id задачи */ 1, '1/30 * * * * *');

-- Includes all schedules for this task in the set of running QSched background tasks:
SELECT qsched_job_enable(/* id задачи */ 1);

Disabling Schedules/Tasks

-- Disables all schedules for a given task from the set of running background
-- QSched tasks:
SELECT qsched_job_disable(/* id задачи */ 1);

Specific Schedules Enabling and Disabling

Along with the functions of enabling and disabling entire tasks, qsched_job_{enable,disable}, there are functions that work with schedule granularity. So, you can enable or disable a specific schedule for a specific task by passing the schedule ID to the qsched_schedule_{enable,disable}.

You can find schedule IDs in qsched_my_jobs system view.

Viewing Scheduler Logs

Logs for tasks that were either created by the current user or were planned to be executed under current user are available for viewing.

-- Will show a view based on the pg_catalog.qsched_job_run_status system table:
SELECT * FROM qsched_my_log;

Viewing General Tasks and Schedules Table for a User

By accessing the qsched_my_jobs view, the user can see the tasks and schedules created by them, or those that are planned to be executed under their name.

-- Will display a complete summary of "your" tasks and schedules:
SELECT * FROM qsched_my_jobs;

Dynamic Calculation of Next Task Execution Time

Let's say we created a task and set a schedule for it in the crontab format:

SELECT qsched_job_create_query('job name', 'SELECT 123');
SELECT qsched_schedule_create(1, '* * * * * *');

SELECT qsched_job_enable(1);
COMMIT;

In this case, we have activated the task, and according to the schedule, it will be executed every second. But, for example, we want the next task execution time to be calculated dynamically, after the next successful execution. For this, the QSched SQL interface provides the function qsched_schedule_dyn_override, which for a given schedule allows to define a SQL command to calculate the next execution time:

-- General syntax:
SELECT qsched_schedule_dyn_override(/* schedule Oid */, /*dynamic SQL*/);

-- Example:
SELECT qsched_schedule_dyn_override(1, $$CURRENT_TIMESTAMP + '1 day'$$);

The result of executing this SQL command will always be interpreted as time, or more precisely TIMESTAMP WITHOUT TIME ZONE.

Now the task will be executed not every second, but only a day after the last successful execution.

Cancel Dynamic Calculation of Next Task Start Time

If you want the task to not calculate its start time dynamically, according to the expression in the next_fire_statement column, but simply work on the schedule, then you should execute the query using the function qsched_schedule_cancel_override, passing it the schedule identifier:


SELECT qsched_schedule_cancel_override(/* schedule id */ 1);

Here the ID is 1 for example, but it can be any value of the type OID. Then your task will follow the schedule to determine the start time.

You can verify that the changes are correct by accessing the qsched_my_jobs view, specifying the desired task ID:


SELECT next_fire_statement FROM qsched_my_jobs WHERE job_id = /* task id */ 1;

Execute Custom SQL on a Failed Task

Let's say we have created a task and its ID is 123. The task can fail at any execution, and we want to set up a SQL statement that will be executed by the QSched process when the task fails:

-- The second argument specifies Fallback SQL expression:
SELECT qsched_job_set_fallback_sql(123, 'UPDATE err_log_table SET failure = 1;');

Now, every time this task fails, the QSched process executor will try to update the entries in the err_log_table table and set the failure field to 1. (This operation is given as an example, you can use any SQL expression.)

If both the task failed and the Fallback expression was executed with an error, this fact is noted in the QSched system tables and is available for viewing in the qsched_my_log view:

-- The double_fault column indicates a double fault — both in the task content
-- and in the Fallback expression.
SELECT double_fault FROM qsched_my_log;

Cancel SQL Fallback Statement for a Specific Task

If you want the Fallback expression to no longer be executed when a task fails, you should execute a query using qsched_job_unset_fallback_sql function:


SELECT qsched_job_unset_fallback_sql(/* task id */ 1);

Deleting and Disabling Unnecessary Tasks

This version supports complete task deletion (excluding execution history) using qsched_job_delete function:

-- Delete a task, its schedule, logs and execution history:
SELECT qsched_job_delete(1 /* task oid */);

Since a complete deletion may not be desirable, a simple way to disable all schedules for a particular task is also provided:

-- Make all schedules for this job inactive, including immediate runs scheduled
-- using qsched_job_exec:
SELECT qsched_job_disable(1 /* task oid */);

You can reactivate all schedules for this task using qsched_job_enable(/* task oid */) function.

Configuring Scheduler Log Automatic Rotation

Scheduler log automatic rotation is configured using the following parameters:

NameDescriptionMandatory
qsched_log_rotation_max_lifetimeMaximum entry lifetimeYes, in the qsched_log_rotation_max_entries absence
qsched_log_rotation_max_entriesMaximum number of entries to saveYes, in the qsched_log_rotation_max_lifetime absence
qsched_log_rotation_periodEntry rotation start periodYes
qsched_log_rotation_strategyRotation strategyNo
qsched_log_rotation_adjustment_periodEntry count period for automatic strategyNo

The rotation parameters are automatically reloaded when the SIGHUP signal is sent or when the pg_reload_conf() function is called. This way, you can change the log rotation parameters or enable/disable it while the DBMS is running.

Rotation can be done using the following strategies:

  • Delete (0) removes old entries using DELETE (it is default).

  • Truncate (1) keeps the actual entries, clears the table using TRUNCATE, loads the saved records back. Potentially faster than Delete when more records are deleted than saved.

  • Automatic (2) periodically counts the number of entries to save/delete, and if more than half of the entries need to be saved, Delete is used, otherwise Truncate. Delete/Truncate are used until the next entry count. The frequency of entry counting is determined by qsched_log_rotation_adjustment_period. For example, if you set this parameter to two, the entries will be recalculated (and the rotation strategy will potentially be changed) every two rotation runs. If you set it to zero, the recalculation will be performed at every run.

Note
Since TRUNCATE for system tables requires permission to modify system tables, the additional allow_system_table_mods = true configuration parameter is required for strategies 1 and 2 to work.

To start automatic scheduler log rotation, you need to add the parameters qsched_log_rotation_max_lifetime and/or qsched_log_rotation_max_entries, as well as qsched_log_rotation_period to the qhb.conf file:

# Maximum entry lifetime is 30 minutes
echo qsched_log_rotation_max_lifetime = '30m' >> $PGDATA/qhb.conf;

# Maximum number of saved entries is 500
echo qsched_log_rotation_max_entries = '500' >> $PGDATA/qhb.conf;

# Run rotation every 5 minutes
echo qsched_log_rotation_period = '5m' >> $PGDATA/qhb.conf;

Checking Automatic Rotation Status

Find out if automatic log rotation is enabled:

SELECT qsched_log_rotation_status();

Returns true when log rotation is enabled and false when disabled.

Find out the current log rotation strategy:

SELECT qsched_log_rotation_static_strategy();

Displays the current strategy: 0 for Delete, 1 for Truncate, 2 for Auto. The value -1 is displayed when rotation is disabled.

To find out the current log rotation strategy when using the automatic strategy:

SELECT qsched_log_rotation_auto_strategy();

Displays the current strategy: 0 for Delete, 1 for Truncate. The value -1 is displayed in cases where rotation is disabled, when the automatic strategy is not used, and also before the first start of rotation, since at that time the strategy has not been generated yet.

Number of Allowed Parallel Processes

This database administrator can set a limit on the number of parallel QSched processes allocated to executing user tasks using the qsched_parallel_slaves configuration parameter.

-- Increase the allowed number of parallel processes to 4:
ALTER DATABASE SET qsched_parallel_slaves TO 4;

-- After this, you should restart the process coordinator:
SELECT start_qsched_master();

-- But before that, the current coordinator should be terminated.
-- You can find out the PID by accessing the process tree or the pg_stat_activity view.
\! kill -TERM <pid_of_qsched_master>

SHOW qsched_parallel_slaves;

This limitation applies to a specific database and does not apply to QSched Master coordinator processes.

QSched Limitations

QSched introduces a standard limitation for many Enterprise schedulers: although many tasks can be executed in parallel, this particular task (that has an OID and a name) will not be executed in parallel with an instance of the same task. This is done to prevent unintentional data corruption, if, for example, the contents of the task modifies DBMS tables or does some other modifying actions.

Copyright © KVANTOM LLC, 2019-2026