Triggers

• 1: Scheduled execution

1 - Scheduled execution

Stored procedures can be scheduled to execute automatically with the privileges of the trigger definer. You can use this to automate various tasks, like logging database activity, revoking privileges, creating roles, or loading data.

Enabling and disabling scheduling

Scheduling can be toggled at the database level with the EnableStoredProcedureScheduler configuration parameter:

-- Enable scheduler
=> SELECT SET_CONFIG_PARAMETER('EnableStoredProcedureScheduler', 1);

-- Disable scheduler
=> SELECT SET_CONFIG_PARAMETER('EnableStoredProcedureScheduler', 0);

You can toggle an individual schedule ENABLE_TRIGGER, or disable it by dropping the schedule's associated trigger.

Scheduling a stored procedure

The general workflow for implementing scheduled execution for a single stored procedure is as follows:

1. Create a stored procedure.

2. Create a schedule. A schedule can either use a list of timestamps for one-off triggers or a cron expression for recurring events.

3. Create a trigger, associating it with the stored procedure and trigger.

4. (Optional) Manually execute the trigger to test it.

One-off triggers

One-off triggers run a finite number of times.

The following example creates a trigger that revokes privileges on the customer_dimension table from the user Bob after 24 hours:

1. Create a stored procedure to revoke privileges from Bob:

   => CREATE OR REPLACE PROCEDURE revoke_all_on_table(table_name VARCHAR, user_name VARCHAR)
   LANGUAGE PLvSQL
   AS $$
   BEGIN
       EXECUTE 'REVOKE ALL ON ' || QUOTE_IDENT(table_name) || ' FROM ' || QUOTE_IDENT(user_name);
   END;
   $$;
   

   Copy

2. Create a schedule with a timestamp for 24 hours later:

   => CREATE SCHEDULE 24_hours_later USING DATETIMES('2022-12-16 12:00:00');
   

   Copy

3. Create a trigger with the stored procedure and schedule:

   => CREATE TRIGGER revoke_trigger ON SCHEDULE 24_hours_later EXECUTE PROCEDURE revoke_all_on_table('customer_dimension', 'Bob') AS DEFINER;
   

   Copy

Recurring triggers

Recurring triggers run at a recurring date or time specified by a cron expression:

=> CREATE SCHEDULE every_saturday_12am USING cron '0 0 * * 6';
=> CREATE SCHEDULE every_5_minutes USING CRON '*/5 * * * *';
=> CREATE SCHEDULE every_hour_from_12pm_to_6pm USING CRON '0 12-17 * * *';

The following example creates a weekly trigger that logs to the USER_COUNT table the number of users in the database:

=> SELECT * FROM USER_COUNT;
  total  |           timestamp
---------+-------------------------------
     293 | 2022-12-04 00:00:00.346664-00
     302 | 2022-12-11 00:00:00.782242-00
     301 | 2022-12-18 00:00:00.144633-00
     301 | 2022-12-25 00:00:00.548832-00
(4 rows)

1. Create the table to log the user counts:

   => CREATE TABLE USER_COUNT(total INT, timestamp TIMESTAMPTZ)
   

   Copy

2. Create the stored procedure to log to the table:

   => CREATE OR REPLACE PROCEDURE log_user_count()
   LANGUAGE PLvSQL
   AS $$
   DECLARE
       num_users int := SELECT count (user_id) FROM users;
       timestamp datetime := SELECT NOW();
   BEGIN
       PERFORM INSERT INTO USER_COUNT VALUES(num_users, timestamp);
   END;
   $$;
   

   Copy

3. Create the schedule for 12:00 AM on Sunday:

   => CREATE SCHEDULE weekly_sunday USING CRON '0 0 * * 0';
   

   Copy

4. Create the trigger with the stored procedure and schedule:

   => CREATE TRIGGER user_log_trigger ON SCHEDULE weekly_sunday EXECUTE PROCEDURE log_user_count() AS DEFINER;
   

   Copy

Viewing upcoming schedules

Schedules are managed and coordinated by the Active Scheduler Node (ASN). If the ASN goes down, a different node is automatically designated as the new ASN. To view scheduled tasks, query SCHEDULER_TIME_TABLE on the ASN.

1. Determine the ASN with ACTIVE_SCHEDULER_NODE:

   => SELECT active_scheduler_node();
   active_scheduler_node
   -----------------------
    initiator
   (1 row)
   

   Copy

2. On the ASN, query SCHEDULER_TIME_TABLE:

   => SELECT * FROM scheduler_time_table;
   
     schedule_name  | attached_trigger | scheduled_execution_time
   -----------------+------------------+--------------------------
    daily_1am_gmt   | log_user_actions | 2022-12-15 01:00:00-00
    24_hours_later  | revoke_trigger   | 2022-12-16 12:00:00-00
   

   Copy