HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v3.0

Documentation:v3.0

Supported Versions:
Current(v5.0)/
v3.1/
v3.0/
v2.1

Other Versions:
v2.0/
v1.1

PKG_SERVICE API

MogDB 3.0 provides the following PKG_SERVICE API to manage scheduled jobs.

Table 1 API Descriptions

API Descriptions
PKG_SERVICE.JOB_CANCEL Delete the scheduled job by job ID.
PKG_SERVICE.JOB_FINISH Disable or enable scheduled job.
PKG_SERVICE.JOB_SUBMIT Submit a scheduled job. The job ID is automatically generated by the system or specified by the user.
PKG_SERVICE.JOB_UPDATE Update the properties of the scheduled job, including job content, next execution time, and execution interval.

API Definition and Usage Examples

  • PKG_SERVICE.JOB_CANCEL

    The CANCEL procedure deletes the specified scheduled job.

    PKG_SERVICE.JOB_CANCEL function prototype is:

    PKG_SERVICE.JOB_CANCEL( job IN INTEGER);

    Table 2 PKG_SERVICE.JOB_CANCEL API parameters description

    Parameter Type In/Out Whether it can be empty Definition
    id integer IN No Specified job ID.

    Example:

    CALL PKG_SERVICE.JOB_CANCEL(101);

  • PKG_SERVICE.JOB_FINISH

    The FINISH procedure disables or enables scheduled job.

    PKG_SERVICE.JOB_FINISH function prototype is:

    PKG_SERVICE.JOB_FINISH(
id IN BIGINT,
broken IN BOOLEAN, 
next_time IN TIMESTAMP DEFAULT sysdate);

    Table 3 PKG_SERVICE.JOB_FINISH API parameters description

    Parameter Type In/Out Whether it can be empty Definition
    id bigint IN No Specified job ID.
    broken Boolean IN No Status flag bit, true means disabled, false means enabled. The current job is updated according to the true or false value; if it is null, the status of the original job is not changed.
    next_time timestamp IN Yes The next run time, the default value is the current system time. If the parameter broken state is true, the parameter is updated to 4000-1-1; if the parameter broken state is false, and if the parameter next_time is not null, the next_time value of the specified job is updated, if next_time is null, the next_time value is not updated. This parameter can be omitted and is the default value.

  • PKG_SERVICE.JOB_SUBMIT

    The JOB_SUBMIT procedure submits a system-provided scheduled job.

    PKG_SERVICE.JOB_SUBMIT function prototype is:

    PKG_SERVICE.JOB_SUBMIT(
id IN BIGINT DEFAULT, 
content IN TEXT, 
next_date IN TIMESTAMP DEFAULT sysdate, 
interval_time IN TEXT DEFAULT 'null', 
job OUT INTEGER);

    Note: When a scheduled job is created, the system binds the current database and user name to the currently created timed task by default. This API function can be called by call or select statement, if called by select, you can leave out the parameters. If in a stored procedure, the API function needs to be called via perform statement. If the submitted SQL statement job uses a non-public schema, you should specify the schema of the table or function, or add the set current_schema = xxx; statement before the SQL statement.

    Table 4 PKG_SERVICE.JOB_SUBMIT API parameters description

    Parameter Type In/Out Whether it can be empty Definition
    id bigint IN No Job ID. If the passed id is NULL, the job ID will be generated internally.
    context text IN No The SQL statement to be executed. Support one or more scenarios of 'DML', 'anonymous block', 'statement calling a stored procedure' or a mix of all 3.
    next_time timestamp IN No The next job run time. The default value is the current system time (sysdate). If it is a past time, it indicates immediate execution when the job is submitted.
    interval_time text IN Yes The time expression used to calculate the next job run time, either as an interval expression or as sysdate plus a numeric value (e.g. sysdate+1.0/24). If the value is null or the string "null", it means that the job will be executed only once, and the job status will become 'd' after execution.
    job integer OUT No Job ID. The range is 1-32767. This parameter can be omitted when pkg_service.job_submit is called with select statement.

    Example:

    SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1');

SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24');

CALL PKG_SERVICE.JOB_SUBMIT(NULL, 'INSERT INTO T_JOB  VALUES(1);  call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid);

SELECT PKG_SERVICE.JOB_SUBMIT (101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24');

  • PKG_SERVICE.JOB_UPDATE

    The UPDATE procedure modifies the properties of a scheduled job, including the job content, next execution time, and execution interval.

    PKG_SERVICE.JOB_UPDATE function prototype is:

    PKG_SERVICE.JOB_UPDATE(
id IN BIGINT, 
next_time IN TIMESTAMP, 
interval_time IN TEXT, 
content IN TEXT);

    Table 5 PKG_SERVICE.JOB_UPDATE API parameters description

    Parameter Type In/Out Whether it can be empty Definition
    id integer IN No Specified job ID.
    next_time timestamp IN Yes The next job run time. If this parameter is null, the next_time value of the specified job is not updated, otherwise the next_time value of the specified job is updated.
    interval_time text IN Yes The time expression used to calculate the next job run time. If the parameter is null, the interval_time value of the specified job will not be updated; if the parameter is not null, it will check whether the interval_time is a valid time type or interval type, then the interval_time value of the specified job will be updated. If it is the string "null", it means it will be executed only once, and the job status will become 'd' after execution.
    content text IN Yes The name of the stored procedure or SQL statement block to execute. If this parameter is null, the content value of the specified job is not updated, otherwise the content value of the specified job is updated.

    Example:

    CALL PKG_SERVICE.JOB_UPDATE(id=>101, next_time=>sysdate, interval_time=>'sysdate + 1.0/1440',content=>'call userproc();');

CALL PKG_SERVICE.JOB_UPDATE(id=>101, next_time=>sysdate, interval_time=>'sysdate + 1.0/1440',content=>'insert into tbl_a values(sysdate);');

Examples of Using Scheduled Job

This example describes how to implement job management through the above APIs.

  • Create a test table

    MogDB=# create table t_job (value TIMESTAMP);
CREATE TABLE

MogDB=# insert into t_job values(sysdate);
INSERT 0 1

MogDB=# select * from t_job;
+---------------------+
| value               |
|---------------------|
| 2021-10-09 04:36:20 |
+---------------------+
SELECT 1

  • Create a job to insert a record every minute

    MogDB=# select pkg_service.job_submit(null, 'insert into t_job values(sysdate);',sysdate,'sysdate + 1/1440');
+--------------+
| job_submit   |
|--------------|
| 15566        |
+--------------+
SELECT 1

  • Check the results of the job runs

    MogDB=# select * from t_job;
+---------------------+
| value               |
|---------------------|
| 2021-10-09 04:36:20 |
| 2021-10-09 04:40:54 |
| 2021-10-09 04:41:54 |
| 2021-10-09 04:42:54 |
+---------------------+
SELECT 4

  • Check job operation from system view

    MogDB=# select job_id,dbname,start_date,next_run_date,interval,failure_count from pg_job;
+----------+----------+----------------------------+---------------------+------------------+-----------------+
| job_id   | dbname   | start_date                 | next_run_date       | interval         | failure_count   |
|----------+----------+----------------------------+---------------------+------------------+-----------------|
| 15566    | postgres | 2021-10-09 04:40:54.072363 | 2021-10-09 04:56:54 | sysdate + 1/1440 | 0               |
+----------+----------+----------------------------+---------------------+------------------+-----------------+
SELECT 1
Time: 0.089s
MogDB=# select * from pg_catalog.pg_job_proc pjp where job_id=15566;
+----------+------------------------------------+
| job_id   | what                               |
|----------+------------------------------------|
| 15566    | insert into t_job values(sysdate); |
+----------+------------------------------------+
SELECT 1
Time: 0.089s

  • Modified to execute once every 2 minutes

    MogDB=# select pkg_service.job_update(15566,null,'sysdate + 2/1440',null);
+--------------+
| job_update   |
|--------------|
|              |
+--------------+
SELECT 1

  • Check modifications and run results

    MogDB=# select job_id,interval from pg_job where job_id=15566;
+----------+------------------+
| job_id   | interval         |
|----------+------------------|
| 15566    | sysdate + 2/1440 |
+----------+------------------+
SELECT 1
MogDB=# select * from t_job;
+---------------------+
| value               |
|---------------------|
| 2021-10-09 04:36:20 |
| 2021-10-09 04:40:54 |
| 2021-10-09 04:41:54 |
| 2021-10-09 04:42:54 |
| 2021-10-09 04:43:54 |
| 2021-10-09 04:44:54 |
| 2021-10-09 04:45:54 |
| 2021-10-09 04:46:54 |
| 2021-10-09 04:47:54 |
| 2021-10-09 04:48:54 |
| 2021-10-09 04:49:54 |
| 2021-10-09 04:50:54 |
| 2021-10-09 04:51:54 |
| 2021-10-09 04:52:54 |
| 2021-10-09 04:53:54 |
| 2021-10-09 04:54:54 |
| 2021-10-09 04:55:54 |
| 2021-10-09 04:56:54 |
| 2021-10-09 04:57:54 |
| 2021-10-09 04:58:54 |
| 2021-10-09 04:59:54 |
| 2021-10-09 05:00:55 |
| 2021-10-09 05:01:56 | <---
| 2021-10-09 05:03:57 | <--- Start interval 2 minutes
+---------------------+
SELECT 24
Time: 0.088s
MogDB=# select job_id,interval,next_run_date from pg_job where job_id=15566;
+----------+------------------+---------------------+
| job_id   | interval         | next_run_date       |
|----------+------------------+---------------------|
| 15566    | sysdate + 2/1440 | 2021-10-09 05:05:57 |
+----------+------------------+---------------------+
SELECT 1
Time: 0.078s>

  • Finish and start jobs

    Both finish and start are the same function pkg_service.job_finish, and different parameters are passed in to indicate whether to finish or start.

    MogDB=# select pkg_service.job_finish(15566,true,null);
+--------------+
| job_finish   |
|--------------|
|              |
+--------------+
SELECT 1
Time: 0.089s
MogDB=# select job_id,next_run_date,job_status from pg_job where job_id=15566;
+----------+---------------------+--------------+
| job_id   | next_run_date       | job_status   |
|----------+---------------------+--------------|
| 15566    | 4000-01-01 00:00:00 | d            |
+----------+---------------------+--------------+
SELECT 1
Time: 0.075s
MogDB=# select pkg_service.job_finish(15566,false,null);
+--------------+
| job_finish   |
|--------------|
|              |
+--------------+
SELECT 1
Time: 0.091s
MogDB=# select job_id,next_run_date,job_status from pg_job where job_id=15566;
+----------+---------------------+--------------+
| job_id   | next_run_date       | job_status   |
|----------+---------------------+--------------|
| 15566    | 4000-01-01 00:00:00 | s            |
+----------+---------------------+--------------+
SELECT 1
Time: 0.080s

    It can be seen that if the next run time is not specified when the job is re-start, the next run time will always remain at 4000, which means that it will still not start, so if the job is finished and then restarted, you need to manually specify the next run time.

    MogDB=# select pkg_service.job_finish(15566,false,sysdate);
+--------------+
| job_finish   |
|--------------|
|              |
+--------------+
SELECT 1
Time: 0.088s
MogDB=# select job_id,next_run_date,job_status from pg_job where job_id=15566;
+----------+---------------------+--------------+
| job_id   | next_run_date       | job_status   |
|----------+---------------------+--------------|
| 15566    | 2021-10-09 05:16:22 | s            |
+----------+---------------------+--------------+
SELECT 1
Time: 0.086s

  • Cancel job

    MogDB=# select pkg_service.job_cancel(15566);
+--------------+
| job_cancel   |
|--------------|
|              |
+--------------+
SELECT 1
Time: 0.082s
MogDB=# select job_id,next_run_date,job_status from pg_job where job_id=15566;
+----------+-----------------+--------------+
| job_id   | next_run_date   | job_status   |
|----------+-----------------+--------------|
+----------+-----------------+--------------+
SELECT 0
Time: 0.086s
MogDB=# select * from pg_catalog.pg_job_proc pjp where job_id=15566;
+----------+--------+
| job_id   | what   |
|----------+--------|
+----------+--------+
SELECT 0
Time: 0.087s

PG_JOB, Scheduled Job

HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.