CREATE EVENT

Description

Creates a scheduled event. The scheduled event is a special object defined in the database. It is associated with tables or other database objects. When specified event conditions are met, the scheduled event automatically triggers the execution of pre-defined operations or statements.

Precautions

• Operations related to scheduled events are supported only when sql_compatibility is set to 'B'.
• A user without the sysadmin permission must obtain the permission from the user who has the sysadmin permission to create, modify or delete the scheduled event. The operation permissions of the scheduled event are the same as those of creating scheduled events for the advanced package DBE_SCHEDULER.
• Currently, the interval expression of a scheduled event is compatible with the syntax of floating-point number, for example, interval 0.5 minute. However, the floating-point number is rounded up during calculation. Therefore, you are advised not to use the floating-point number for the interval.
• Scheduled events with the same name are not supported in the same database.
• The statements to be executed in a scheduled event are any SQL statements except security-related operations. However, some statements with restrictions fail to be executed. For example, a database cannot be created by using composite statements.
• The security-related operations are as follows.
  • Use the encryption function.
  • Create and set users and groups.
  • Connect to a database.
  • Encrypt the function.
• The definer fails to be specified for a scheduled event in the following scenarios:
  • The user who operates the scheduled event does not have the sysadmin permission.
  • The current user is inconsistent with the specified definer.
    • An initial user is specified as the definer.
    • An O&M administrator or monitor administrator is specified as the definer.
    • The parameter enableSeparationOfDuty is set to on to enable the separation of duties.

Syntax

CREATE
    [DEFINER = user]
EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

• schedule

  1 2 3 4 5 6

  { AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...] }

• interval

  quantity {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
            YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
            DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | 
            MINUTE_SECOND}

Parameters

• DEFINER

  Specifies the permission for the scheduled event statement to be executed during execution. By default, the permission of the user who creates the scheduled event is used. When a definer is specified, the permission of the specified user is used.

  Only users with the sysadmin permission can specify the definer.

• ON SCHEDULE
  Specifies the time when a scheduled event is executed. A scheduled event can be set to be executed once or multiple times through SCHEDULE.

  • AT timestamp [+ INTERVAL interval] indicates that the scheduled event is executed only once at timestamp [+ INTERVAL interval].
  • EVERY interval indicates that the scheduled event is executed at the interval specified by interval.
    • STARTS timestamp [+ INTERVAL interval] indicates that user can specify the start time for a scheduled event that can be executed repeatedly. That is, the scheduled event is executed since timestamp [+ INTERVAL interval]. If this parameter is left blank, the task is executed from the current time by default.
    • ENDS timestamp [+ INTERVAL interval] indicates that user can specify the end time for a scheduled event that can be executed repeatedly. That is, the scheduled event ends at timestamp [+ INTERVAL interval]. If this parameter is left empty, the default value 3999-12-31 16:00:00 is used.
• INTERVAL

  Specifies the interval. The value of INTERVAL consists of a number and a time unit specified by quantity, for example, 1 YEAR.

• ON COMPLETION [NOT] PRESERVE

  Once a transaction is complete, the scheduled event is deleted from the system catalog immediately by default. You can overwrite the default behavior by setting ON COMPLETION PRESERVE.

• ENABLE | DISABLE | DISABLE ON SLAVE

  The scheduled event is in the ENABLE state by default after it is created. That is, the statement to be executed is executed immediately at the specified time. You can use the keyword DISABLE to change the ENABLE state. The performance of DISABLE ON SLAVE is the same as that of DISABLE.

• COMMENT

  You can add comments to the scheduled event. The comments can be viewed in the GS_JOB_ATTRIBUTE table.

• DO

  Specifies the statement to be executed for a scheduled event.

Examples

-- Create and switch to the test database.
gaussdb=# CREATE DATABASE test_event WITH DBCOMPATIBILITY = 'b';
gaussdb=# \c test_event

-- Create a table.
test_event=# CREATE TABLE t_ev(num int);

-- Create a scheduled event that is executed once every five seconds. After the event is executed, the scheduled event is automatically deleted.
test_event=# CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT sysdate + interval 5 second DO INSERT INTO t_ev VALUES(0);

-- Query the table five seconds later.
test_event=# SELECT * FROM t_ev;
 num 
-----
   0
(1 row)

-- Create a scheduled event that is executed every minute.
test_event=# CREATE EVENT IF NOT EXISTS event_e2 ON SCHEDULE EVERY 1 minute DO INSERT INTO t_ev VALUES(1);

-- Query the table every one minute. A new record is displayed.
test_event=# SELECT * FROM t_ev;
 num 
-----
   0
   1
   1
(3 rows)

-- Delete the scheduled event.
test_event=# DROP EVENT event_e2;

-- Drop the table.
test_event=# DROP TABLE t_ev;

-- Switch back to the initial database and delete the test database. Replace postgres with the actual database name.
test_event=#\c postgres
gaussdb=# DROP DATABASE test_event;

Helpful Links

ALTER EVENT, DROP EVENT, and SHOW EVENTS