Updated on 2024-05-07 GMT+08:00

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 scheduled event to be modified. By default, the system administrator has the permission to modify all scheduled events.
  • You can execute SHOW EVENTS or view the log_user column in PG_JOB 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]

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 the GS_JOB_ATTRIBUTE table.

  • DO

    Specifies the statement to be executed for a scheduled event.

Example

-- Create a scheduled event.
gaussdb=# CREATE TABLE t_ev(num int);

gaussdb=#  CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT sysdate + interval 5 second + interval 33 minute DISABLE DO insert into t_ev values(0);


-- Modify the scheduled event status and statements to be executed.
gaussdb=#  ALTER EVENT event_e1 ENABLE DO select 1;

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