Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

CREATE EVENT

Updated on 2024-08-20 GMT+08:00

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;
NOTICE:
  • If a scheduled event fails to be executed after being created, you can view the failure cause in the SHOW EVENTS or PG_JOB table.
  • When operations related to user passwords (such as creating weak passwords) are performed in the statements to be executed for a scheduled event, system catalog records the password in plaintext. Therefore, you are advised not to perform operations related to user passwords in the statements to be executed for the scheduled event.

Helpful Links

ALTER EVENT, DROP EVENT, and SHOW EVENTS

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback