DBE_TASK

API Description

Table 1 lists all APIs supported by the DBE_TASK package.

• DBE_TASK.SUBMIT

  The stored procedure SUBMIT commits a scheduled task 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 DBE_TASK.SUBMIT parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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.

  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 prototype of the DBE_TASK.JOB_SUBMIT function 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 DBE_TASK.JOB_SUBMIT parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	Description
  job	integer	OUT	No	Specifies the job ID. The value ranges from 1 to 32767. When dbe.job_submit is called by using the SELECT statement, 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 prototype of the DBE_TASK.CANCEL function is as follows:

  1	CANCEL(id IN INTEGER);

  Table 4 DBE_TASK.CANCEL parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 DBE_TASK.RUN parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 prototype of the DBE_TASK.FINISH function is as follows:

  1 2 3 4

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

  Table 6 DBE_TASK.FINISH parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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

  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)

• 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 prototype of the DBE_TASK.UPDATE function 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 DBE_TASK.UPDATE parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 DBE_TASK.CHANGE parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 state 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 prototype of the DBE_TASK.CONTENT function is as follows:

  1 2 3

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

  Table 9 DBE_TASK.CONTENT parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	Description
  id	integer	IN	No	Specifies the job ID.
  content	text	IN	No	Specifies the name of the stored procedure or SQL statement 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 prototype of the DBE_TASK.NEXT_TIME function is as follows:

  1 2 3

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

  Table 10 DBE_TASK.NEXT_TIME parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 prototype of the DBE_TASK.INTERVAL function is as follows:

  1 2 3

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

  Table 11 DBE_TASK.INTERVAL parameters

  Parameter	Type	Input/Output Parameter	Whether NULL Is Allowed	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 the cancel, update, next_time, content, or interval API to delete or modify job parameters.

Constraints

1. You can create, update, and delete jobs only using the procedures provided by the dbe_task package. These procedures synchronize job information between different primary database nodes 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, job information will become inconsistent between primary database nodes and system catalogs may fail to be associated, compromising internal job management.
2. Each task created by a user is bound to a primary database node. If the primary database node 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 primary database node recovers. When a primary database node fails, all tasks on this primary database node cannot be scheduled or executed until the primary database node is restored manually, or deleted and then replaced.
3. For each job, the bound primary database node updates the real-time job information (including the job status, last execution start time, last execution end time, next execution start time, the number of execution failures [if any]) to the pg_job system catalog, and synchronizes the information to other primary database nodes, ensuring consistent job information between different primary database nodes. In the case of primary database node failures, job information synchronization is reattempted by the hosting primary database nodes, which increases job execution time. Although job information fails to be synchronized between primary database nodes, job information can still be properly updated in the pg_job table on the hosting primary database nodes, and jobs can be executed successfully. After a primary database node recovers, job information such as job execution time and status in its pg_job table may be incorrect and will be updated only after the jobs are executed again on related primary database nodes.
4. 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.