DBE_TASK

API Description

Table 1 lists all APIs supported by the DBE_TASK package.

DBE_TASK scheduling in distributed mode does not ensure high reliability of task execution status information. The execution status of task scheduling can be queried only on the node where the task is committed. The information queried on other nodes may not be synchronized.

GaussDB provides a highly available, reliable, and flexible scheduling system using the DBE_SCHEDULER API (recommended in distributed mode).

• DBE_TASK.SUBMIT

  The stored procedure SUBMIT commits a scheduled job provided by the system.

  The prototype of the DBE_TASK.SUBMIT function is as follows:

  1 2 3 4 5 6

  DBE_TASK.SUBMIT( what IN TEXT, next_time IN TIMESTAMP DEFAULT sysdate, interval_time IN TEXT DEFAULT 'null', id OUT INTEGER )RETURN INTEGER;

  

  When a scheduled task is created (using DBE_TASK), the system binds the current database and the username to the task by default. The function can be called using CALL or SELECT. If the function is called using CALL, you do not need to set the output parameter. If the function is called using SELECT, you need to set the output parameter. To call this function within a stored procedure, use perform. If the committed SQL statement task uses a non-public schema, specify the schema to a table schema or a function schema, or add set current_schema = xxx before the SQL statement.

  Table 2 Parameters for DBE_TASK.SUBMIT

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  what	Text	IN	No	SQL statement to be executed. One or multiple DDLs (excluding database-related operations), DMLs, anonymous blocks, and statements for calling stored procedures, or all four combined are supported.
  next_time	Timestamp	IN	No	Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is committed.
  interval_time	Text	IN	Yes	Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a numeric value, for example, sysdate+1.0/24. If this parameter is left empty or set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  id	Integer	OUT	No	Specifies the job ID. The value ranges from 1 to 32767. When SELECT is used for calling, this parameter cannot be added. When CALL is used for calling, this parameter must be added.

  

  When you create a user using the what parameter, the plaintext password of the user is recorded in the log. Therefore, you are advised not to do so. Tasks created using this API may not be highly available. You are advised to use PKG_SERVICE.SUBMIT_ON_N.... to create a task and specify the CCN as the job execution node.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

  gaussdb=# select DBE_TASK.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1'); submit -------- 31031 (1 row) gaussdb=# select DBE_TASK.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); submit -------- 512 (1 row) gaussdb=# DECLARE gaussdb-# jobid int; gaussdb-# BEGIN gaussdb$# PERFORM DBE_TASK.SUBMIT('call pro_xxx();', sysdate, 'interval ''5 minute''', jobid); gaussdb$# END; gaussdb$# / ANONYMOUS BLOCK EXECUTE

• DBE_TASK.JOB_SUBMIT

  The stored procedure SUBMIT commits a scheduled job provided by the system. In addition, it provides additional compatibility parameters.

  The DBE_TASK.JOB_SUBMIT function prototype is as follows:

  1 2 3 4 5 6 7 8 9

  DBE_TASK.JOB_SUBMIT( job OUT INTEGER, what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', no_parse IN BOOLEAN DEFAULT false, instance IN INTEGER DEFAULT 0, force IN BOOLEAN DEFAULT false )RETURN INTEGER;

  Table 3 Parameters for DBE_TASK.JOB_SUBMIT

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  job	Integer	OUT	No	Specifies the job ID. The value ranges from 1 to 32767. When dbe.job_submit is called using SELECT, this parameter can be omitted.
  what	Text	IN	No	SQL statement to be executed. One or multiple DDLs (excluding database-related operations), DMLs, anonymous blocks, and statements for calling stored procedures, or all four combined are supported.
  next_date	Timestamp	IN	Yes	Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is committed.
  job_interval	Text	IN	Yes	Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a numeric value, for example, sysdate+1.0/24. If this parameter is left empty or set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  no_parse	Boolean	IN	Yes	The default value is false, which is used only for syntax compatibility.
  instance	Integer	IN	Yes	The default value is 0, which is used only for syntax compatibility.
  force	Boolean	IN	Yes	The default value is false, which is used only for syntax compatibility.

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# DECLARE gaussdb-# id integer; gaussdb-# BEGIN gaussdb$# id = DBE_TASK.JOB_SUBMIT( gaussdb$# what => 'insert into t1 values (1, 2)', gaussdb$# job_interval => 'sysdate + 1' --daily gaussdb$# ); gaussdb$# END; gaussdb$# / ANONYMOUS BLOCK EXECUTE

• DBE_TASK.ID_SUBMIT

  ID_SUBMIT has the same syntax function as SUBMIT, but the first parameter of ID_SUBMIT is an input parameter, that is, a specified job ID. In contrast, that last parameter of ID_SUBMIT is an output parameter, indicating the job ID automatically generated by the system.

  1 2 3 4 5

  DBE_TASK.ID_SUBMIT( id IN BIGINT, what IN TEXT, next_time IN TIMESTAMP DEFAULT sysdate, interval_time IN TEXT DEFAULT 'null');

  Example:

  1 2 3 4 5

  gaussdb=# CALL dbe_task.id_submit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); id_submit ----------- (1 row)

• DBE_TASK.CANCEL

  The stored procedure CANCEL deletes a specified task.

  The function prototype of DBE_TASK.CANCEL is as follows:

  1	CANCEL(id IN INTEGER);

  Table 4 Parameters for DBE_TASK.CANCEL

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	Integer	IN	No	Specifies the job ID.

  Example:

  gaussdb=# CALL dbe_task.cancel(101);
   cancel
  --------
  
  (1 row)

• DBE_TASK.RUN

  The stored procedure runs a scheduled task.

  The prototype of the DBE_TASK.RUN function is as follows:

  1 2 3

  DBE_TASK.RUN( job IN BIGINT, force IN BOOLEAN DEFAULT FALSE);

  Table 5 Parameters for DBE_TASK.RUN

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  job	bigint	IN	No	Specifies the job ID.
  force	Boolean	IN	Yes	Used only for syntax compatibility.

  Example:

  1 2 3 4 5 6

  gaussdb=# BEGIN gaussdb$# DBE_TASK.ID_SUBMIT(12345, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); gaussdb$# DBE_TASK.RUN(12345); gaussdb$# END; gaussdb$# / ANONYMOUS BLOCK EXECUTE

• DBE_TASK.FINISH

  The stored procedure FINISH disables or enables a scheduled task.

  The function prototype of DBE_TASK.FINISH is as follows:

  1 2 3 4

  DBE_TASK.FINISH( id IN INTEGER, broken IN BOOLEAN, next_time IN TIMESTAMP DEFAULT sysdate);

  Table 6 Parameters for DBE_TASK.FINISH

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	Integer	IN	No	Specifies the job ID.
  broken	Boolean	IN	No	Specifies the status flag, true for broken and false for not broken. The current job is updated based on the parameter value true or false. If the parameter is left empty, the job status remains unchanged.
  next_time	Timestamp	IN	Yes	Specifies the next execution time. The default value is the current system time. If broken is set to true, next_time is updated to '4000-1-1'. If broken is set to false and next_time is not empty, next_time is updated for the job. If next_time is empty, it will not be updated. This parameter can be omitted, and its default value will be used in this case.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  gaussdb=# CALL dbe_task.id_submit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); id_submit ----------- (1 row) gaussdb=# CALL dbe_task.finish(101, true); finish -------- (1 row) gaussdb=# CALL dbe_task.finish(101, false, sysdate); finish -------- (1 row)

• DBE_TASK.UPDATE

  The stored procedure UPDATE modifies user-definable attributes of a task, including the task content, next-execution time, and execution interval.

  The DBE_TASK.UPDATE function prototype is as follows:

  1 2 3 4 5

  dbe_task.UPDATE( id IN INTEGER, content IN TEXT, next_time IN TIMESTAMP, interval_time IN TEXT);

  Table 7 Parameters for DBE_TASK.UPDATE

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	Integer	IN	No	Specifies the job ID.
  content	Text	IN	Yes	Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left empty, the system does not update the content parameter for the specified job. Otherwise, the system updates the content parameter for the specified job.
  next_time	Timestamp	IN	Yes	Specifies the next execution time. If this parameter is left empty, the system does not update the next_time parameter for the specified job. Otherwise, the system updates the next_time parameter for the specified job.
  interval_time	Text	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left empty, the system does not update the interval_time parameter for the specified job. Otherwise, the system updates the interval_time parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job state will change to 'd' afterward.

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# CALL dbe_task.update(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440'); update -------- (1 row) gaussdb=# CALL dbe_task.update(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440'); update -------- (1 row)

• DBE_TASK.CHANGE

  The stored procedure UPDATE modifies user-definable attributes of a task, including the task content, next-execution time, and execution interval.

  The prototype of the DBE_TASK.CHANGE function is as follows:

  1 2 3 4 5 6 7

  DBE_TASK.CHANGE( job IN INTEGER, what IN TEXT DEFAULT NULL, next_date IN TIMESTAMP DEFAULT NULL, job_interval IN TEXT DEFAULT NULL, instance IN INTEGER DEFAULT NULL, force IN BOOLEAN DEFAULT false);

  Table 8 Parameters for DBE_TASK.CHANGE

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  job	Integer	IN	No	Specifies the job ID.
  what	Text	IN	Yes	Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left empty, the system does not update the what parameter for the specified job. Otherwise, the system updates the what parameter for the specified job.
  next_date	Timestamp	IN	Yes	Specifies the next execution time. If this parameter is left empty, the system does not update the next_time parameter for the specified job. Otherwise, the system updates the next_date parameter for the specified job.
  job_interval	Text	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left empty, the system does not update the job_interval parameter for the specified job. Otherwise, the system updates the job_interval parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  instance	Integer	IN	Yes	Used only for syntax compatibility.
  force	Boolean	IN	No	Used only for syntax compatibility.

  Example:

  1 2 3 4 5 6 7 8

  gaussdb=# BEGIN gaussdb$# DBE_TASK.CHANGE( gaussdb$# job => 101, gaussdb$# what => 'insert into t2 values (2);' gaussdb$# ); gaussdb$# END; gaussdb$# / ANONYMOUS BLOCK EXECUTE

• DBE_TASK.CONTENT

  The stored procedure CONTENT modifies the procedures to be executed by a specified task.

  The function prototype of DBE_TASK.CONTENT is as follows:

  1 2 3

  DBE_TASK.CONTENT( id IN INTEGER, content IN TEXT);

  Table 9 Parameters for DBE_TASK.CONTENT

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	Integer	IN	No	Specifies the job ID.
  content	Text	IN	No	Specifies the name of the stored procedure, SQL statement block, or program block that is executed.

  

  • If the value specified by the content parameter is one or multiple executable SQL statements, program blocks, or stored procedures, this procedure can be executed successfully; otherwise, it will fail to be executed.
  • If the value specified by the content parameter is a simple statement such as INSERT and UPDATE, a schema name must be added in front of the table name.

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# CALL dbe_task.content(101, 'call userproc();'); content --------- (1 row) gaussdb=# CALL dbe_task.content(101, 'insert into tbl_a values(sysdate);'); content --------- (1 row)

• DBE_TASK.NEXT_TIME

  The stored procedure NEXT_TIME modifies the next-execution time attribute of a task.

  The function prototype of DBE_TASK.NEXT_TIME is as follows:

  1 2 3

  DBE_TASK.NEXT_TIME( id IN BIGINT, next_time IN TEXT);

  Table 10 Parameters for DBE_TASK.NEXT_TIME

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	bigint	IN	No	Specifies the job ID.
  next_time	Text	IN	No	Specifies the next execution time.

  

  If the specified next_time value is earlier than the current date, the job is executed once immediately.

  Example:

  1 2 3 4 5

  gaussdb=# CALL dbe_task.next_time(101, sysdate); next_time ----------- (1 row)

• DBE_TASK.INTERVAL

  The stored procedure INTERVAL modifies the execution interval attribute of a task.

  The function prototype of DBE_TASK.INTERVAL is as follows:

  1 2 3

  DBE_TASK.INTERVAL( id IN INTEGER, interval_time IN TEXT);

  Table 11 Parameters for DBE_TASK.INTERVAL

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	Integer	IN	No	Specifies the job ID.
  interval_time	Text	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left empty or set to null, the job will be executed only once, and the job status will change to 'd' afterward. The job interval must be of a valid time type or interval type.

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# CALL dbe_task.interval(101, 'sysdate + 1.0/1440'); interval ---------- (1 row) gaussdb=# CALL dbe_task.cancel(101); cancel -------- (1 row)

  

  For a job that is currently running (that is, job_status is 'r'), it is not allowed to use cancel, update, next_time, content, or interval to delete or modify job parameters.

Constraints

1. After a job is created by using SUMMIT/ID_SUBMIT, the job belongs to the current coordinator (that is, the job is scheduled and executed only on the current coordinator). Other coordinators do not schedule and execute the job. If the coordinator node is faulty, the job cannot be properly executed. You are advised to use the PKG_SERVICE.SUBMIT_ON_N.... API to specify the job execution node as CCN to ensure that the job is still available when a node is faulty. Not all coordinators can query, modify, and delete tasks created on other CNs.
2. You can create, update, and delete tasks only using the procedures provided by the dbe_task package. These procedures synchronize task information between different CNs and associate primary keys between the pg_job and pg_job_proc catalogs. If you use DML statements to add, delete, or modify records in the pg_job catalog, task information will become inconsistent between CNs and system catalogs may fail to be associated, compromising internal task management.
3. Each task created by a user is bound to a CN. If the CN fails while a task is being executed, the task status cannot be updated in real time and will stay at 'r'. The task status will be updated to 's' only after the CN recovers. When a CN fails, all tasks on this CN cannot be scheduled or executed until the CN is restored manually, or deleted and then replaced.
4. For each task, the bound CN updates the real-time task information (including the task status, last execution start time, last execution end time, next execution start time, the number of execution failures [if any]) to the pg_job catalog, and synchronizes the information to other CNs, ensuring consistent task information between different CNs. In the case of faults on other CNs, task information synchronization is reattempted by the bound CN, which increases job execution time. Although task information fails to be synchronized between CNs, task information can still be properly updated in pg_job on the bound CN and the task can be executed successfully. After the faulty CN recovers, task information such as task execution time and status in its pg_job catalog may be incorrect and will be updated only after the task is executed again on the bound CN.
5. For each job, a thread is established to execute it. If multiple jobs are triggered concurrently as scheduled, the system will need some time to start the required threads, resulting in a latency of 0.1 ms in job execution.