PolarDB:PG_CRON

Precautions

• The PG_CRON extension of an earlier version may risk memory leak. Therefore, before you use the PG_CRON extension, upgrade the kernel to V1.1.19 or later. If you avoid the PG_CRON extension, this risk is not incurred.
• For security considerations, you can execute scheduled tasks only by using system functions. The system allows you to add and delete scheduled tasks. Common users can only view scheduled tasks in the cron.job table. Select a method of using the functions.
• Scheduled tasks are stored in the default PostgreSQL database, but you can query the scheduled tasks in other databases.
• Scheduled tasks are run based on GMT.
• The PG_CRON extension is disabled by default. For security reasons, you cannot manually create the extension. To use this feature, go to the Quota Center. Click Apply in the Actions column corresponding to polardb_pg_pg_cron.

Disable the PG_CRON extension

You can disable the PG_CRON extension by executing the following statement:

-- Disable PG_CRON
DROP EXTENSION pg_cron;

Use the PG_CRON extension

Each scheduled task consists of a schedule and a task. The schedule specifies the time when the task is executed. For example, the task is executed once every minute. The task specifies the operation, such as select * from some_table. The PG_CRON extension provides the database parameter. This parameter is optional. If you do not specify this parameter, the system automatically uses the PostgreSQL database.

You can execute only the following functions. The execution of other functions is invalid.

-- Execute a task.
SELECT cron.schedule('schedule', 'task')

-- Select a database to execute the task. 
-- If you do not specify the third parameter, the database in the configuration file is used. By default, the PostgreSQL database is used.
SELECT cron.schedule('schedule', 'task', 'my_db')

-- Delete a task.
SELECT cron.unschedule(schedule_id)

-- View the task list.
SELECT * FROM cron.job;

Examples

• Add scheduled tasks.

  -- Delete stale data at 3:30 in the morning every Saturday. The time is in Greenwich Mean Time (GMT). 
  SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
   schedule
  ----------
         42
  
  -- Clear disks at 10:00 in the morning every day. The time is in GMT.
  SELECT cron.schedule('0 10 * * *', 'VACUUM');
   schedule
  ----------
         43
   
  -- Execute the specified script once every minute.
  SELECT cron.schedule('* * * * *', 'select 1;');
   schedule
  ----------
         44
  
  -- Execute the specified script at the twenty-third minute of every hour.
  SELECT cron.schedule('23 * * * *', 'select 1;');
   schedule
  ----------
         45
         
  -- Execute the specified script on the fourth day of every month.
  SELECT cron.schedule('* * 4 * *', 'select 1;');
   schedule
  ----------
         46

  The schedules use standard CRON expressions. In the expressions, wildcards * indicate that the tasks are periodically executed and the numbers indicate the specific points in time when the tasks are executed.

   ┌───────────── Minute (0 to 59)
   │ ┌────────────── Hour (0 to 23)
   │ │ ┌─────────────── Date (1 to 31)
   │ │ │ ┌──────────────── Month (1 to 12)
   │ │ │ │ ┌───────────────── The day of the week (0 to 6) (The value 0 indicates Sunday. 
   │ │ │ │ │                   The value 7 still indicates a weekend day.)
   * * * * *

• Delete a task.

  -- Stop and delete a task.
  SELECT cron.unschedule(42);
   unschedule
  ------------
            t

• View the running task.

  SELECT * FROM cron.job;
  
   jobid | schedule   |  command  | nodename  | nodeport | database | username | active 
  -------+------------+-----------+-----------+----------+----------+----------+--------
      43 | 0 10 * * * |   VACUUM; | localhost |     5433 | postgres | test     | t