ALTER EVENT

Description

Modifies the parameters in the created scheduled event.

Precautions

• Operations related to scheduled events are supported only when sql_compatibility is set to 'B'.
• Only the owner has the permission to modify the corresponding scheduled event. By default, system administrators have the permission to modify all scheduled events.
• You can execute SHOW EVENTS or view the log_user column in the PG_JOB table to obtain the event owner information.
• Each time a scheduled event is modified, the owner of the modified event is changed to the current user. If a definer is specified during modification, the owner is changed to the specified definer.
• The restrictions for the definer are the same as those described in CREATE EVENT.
  

  If a system administrator modifies a scheduled event created by another user, the owner of the modified event is changed to the system administrator. The statements to be executed are executed by the system administrator.

Syntax

ALTER
    [DEFINER = user]    
EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [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 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. The SCHEDULE clause is the same as that in CREATE EVENT.

• RENAME TO

  Specifies the updated scheduled event name.

• 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 a scheduled event. The comments can be viewed in GS_JOB_ATTRIBUTE.

• 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 task in the DISABLE state, indicating that the scheduled task is disabled.
test_event=# CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT sysdate + interval 5 second DISABLE DO insert into t_ev values(0);

-- To query a scheduled task, set ENABLE to f.
test_event=# SHOW EVENTS;
 job_name | schema_name | log_user | priv_user | job_status |     start_date      | interval |      end_date       | enable | failure_msg 
----------+-------------+----------+-----------+------------+---------------------+----------+---------------------+--------+-------------
 event_e1 | public      | omm      | omm       | s          | 2023-11-28 09:10:58 | null     | 3999-12-31 16:00:00 | f      | 
(1 row)

-- Change the status of the scheduled task to ENABLE and query the table five seconds later.
test_event=# ALTER EVENT event_e1 ENABLE;
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)

-- Modify the statements to be executed in the scheduled task and query the table.
test_event=# ALTER EVENT event_e2 DO insert into t_ev values(3);

test_event=# SELECT * FROM t_ev;
 num 
-----
   0
   1
   1
   3
(4 rows)

-- Change the name of a scheduled event.
test_event=# ALTER EVENT event_e2 RENAME TO event_ee;

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

-- 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

CREATE EVENT, DROP EVENT, and SHOW EVENTS