PolarDB:pg_cron

Background information

A scheduled task is automatically run at a specified time point or time interval. Scheduled tasks are usually managed by a scheduler that executes tasks based on specified time rules. You can set time rules for the scheduler to run tasks at specific time points every day, specific days and time points every week, or specific days and time points every month. You can also configure the scheduler to run a task for a specific number of times at specific time intervals based on your requirements.

Scheduled tasks can be used in the following scenarios:

• On the server side, scheduled tasks are often used to regularly run background tasks, generate reports, and clear junk data.

• On the client side, scheduled tasks can be used to update data, sent alerts to users, and perform automated operations.

You can use scheduled tasks on both the server and client sides to reduce manual operations and improve work efficiency.

The pg_cron extension has the following advantages:

• Easy to use: You can execute SQL statements to create, schedule, and manage tasks. No additional programming or configurations are required.

• Flexible scheduling options: You can schedule tasks by minute, hour, date, week, month, and any time combination.

• Database level task management: Scheduled tasks are stored in databases and can be shared and managed across multiple databases.

• Concurrent task execution: You can execute multiple tasks at the same time to improve task execution efficiency.

• Reliability and fault tolerance: Error handling and fault tolerance mechanisms are provided to ensure that tasks are correctly executed and corresponding logs are generated.

The pg_cron extension is a simple and powerful tool for database administrators and developers to automate repetitive tasks. For example, you can use pg_cron to create tasks to back up data, generate reports, and perform regular cleanups. This improves database management and development efficiency.

Usage notes

If you restart a PolarDB for PostgreSQL cluster whose minor engine version is 14.10.16.0 or earlier, the cluster port is changed and the tasks created by using the pg_cron extension may fail. This issue is fixed in minor engine version 14.10.16.1 or later.

How it works

Scheduled task information maintenance

All scheduled tasks are stored in the cron.job table. You can create or delete scheduled tasks by invoking SQL functions.

The pg_cron extension maintains the JOB LIST and TASK LIST lists for background scheduling and task execution. When the database is started, JOB LIST and TASK LIST are created based on the task list in the cron.job table. If the task list is later updated, the cron.job_cache_invalidate trigger refreshes the lists to keep JOB LIST and TASK LIST consistent with the cron.job table in real time. This ensures that the tasks can be correctly scheduled and executed.

Scheduled task scheduling and execution

• WAITING: the default state. If the conditions are not met, for example the task is not activated or the scheduled time is not reached, the scheduling of the task is skipped. If the conditions are met, the task enters the START state.

• START: establishes the connection information of the task and performs a connection test. If the connection is successful, the task enters the CONNECTING state. Otherwise, the task enters the ERROR state.

• CONNECTING: checks whether the task is activated and whether the connection is normal. If all conditions are met, the task enters the SENDING state. Otherwise, the task enters the ERROR state.

• SENDING: checks whether the task is activated and whether the connection is normal. If all the conditions are met, the scheduled task is sent to the PolarDB for PostgreSQL server and enters the RUNNING state. Otherwise, the task enters the ERROR state.

• RUNNING: checks whether the task is activated and whether the connection is normal. If all conditions are met, the system receives the returned task result and the task enters the DONE state. Otherwise, the system stops waiting and the task enters the ERROR state.

• ERROR: The task failed. The system resets the connection information. The task enters the DONE state.

• DONE: The task is complete. The system resets the task information. The task enters the WAITING state again.

Functions

Create a scheduled task

Syntax

cron.schedule (
    job_name TEXT,
    schedule TEXT,
    command TEXT
);

Parameters

Create a scheduled task in a specified database

Syntax

cron.schedule_in_database (
    job_name TEXT,
    schedule TEXT,
    command TEXT,
    db_name TEXT
);

Parameters

Modify a scheduled task

Syntax

cron.alter_job (
    job_id BIGINT,
    schedule TEXT DEFAULT NULL,
    command TEXT DEFAULT NULL,
    db_name TEXT DEFAULT NULL,
    active BOOLEAN DEFAULT NULL
);

Parameters

Delete a scheduled task

Syntax

cron.unschedule(job_id BIGINT);

cron.unschedule(job_name TEXT);

Parameters

Usage

Create the pg_cron extension

Connect to the database and run the following command to create a pg_cron extension:

CREATE EXTENSION pg_cron;

Create a scheduled task

Run the following command to create a task named task1 for the db01 database. The task ID is returned.

SELECT cron.schedule_in_database('task1', '* * * * *', 'SELECT 1', 'db01');

Sample result:

 schedule_in_database
----------------------
                    1
(1 row)

View scheduled tasks

Run the following command to view scheduled tasks:

SELECT * FROM cron.job;

Sample result:

jobid | schedule  | command  | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
     1 | * * * * * | SELECT 1 | /tmp     |    39361 | db01     | u1       | t      | task1
(1 row)

View the execution history of a scheduled task

Run the following command to view the execution history of a scheduled task:

SELECT * FROM cron.job_run_details;

Sample result:

 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 | 4152316 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:55:00.020442+00 | 2023-10-19 03:55:00.021512+00
     1 |     2 | 4152438 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:56:00.006468+00 | 2023-10-19 03:56:00.006822+00
(2 rows)

Delete a scheduled task

Run the following command to delete a scheduled task:

SELECT cron.unschedule('task1');

Sample result:

 unschedule
------------
 t
(1 row)

Delete the pg_cron extension

Run the following command to delete the pg_cron extension:

DROP EXTENSION pg_cron;

Time rule examples

The pg_cron extension uses the standard cron syntax. * indicates that the task is run at a specified time interval. The specified numbers indicate the time period between two consecutive executions.

┌───────────── Minute (0 to 59)
 │ ┌────────────── Hour (0 to 23)
 │ │ ┌─────────────── Date (1 to 31)
 │ │ │ ┌──────────────── Month (1 to 12)
 │ │ │ │ ┌───────────────── Day of the week (0 to 6). The value 0 indicates Sunday. The value 6 indicates next Saturday. 
 │ │ │ │ │                  The value 7 also indicates Sunday.
 * * * * *

• Delete expired data at GMT 03:30 every Saturday.

  SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

• Execute VACUUM at GMT 10:00 every day.

  SELECT cron.schedule('0 10 * * *', 'VACUUM;');

• Execute specified SQL statements every minute.

  SELECT cron.schedule('* * * * *', 'SELECT 1;');

• Execute specified SQL statements at the 23rd minute of each hour.

  SELECT cron.schedule('23 * * * *', 'SELECT 1;');

• Execute specified SQL statements on the fourth day of each month.

  SELECT cron.schedule('* * 4 * *', 'SELECT 1;');