Job类似Unix中的crontab,有定时执行的功能,可以在指定的时间点或每天的某个时间点等自行执行任务。在各类系统使用运行过程中,经常会遇到需要定时完成的任务,比如定时更新数据,定时统计数据生成报表等等,这些工作都可以使用Job来完成。在openGauss 2.1.0中,提供了以下接口来实现管理Job。
存储过程CANCEL删除指定的定时任务。 PKG_SERVICE.JOB_CANCEL函数原型为: PKG_SERVICE.JOB_CANCEL( job IN INTEGER);
示例: CALL PKG_SERVICE.JOB_CANCEL(101);
存储过程FINISH禁用或者启用定时任务。 PKG_SERVICE.JOB_FINISH函数原型为: PKG_SERVICE.JOB_FINISH( id IN INTEGER, broken IN BOOLEAN, next_time IN TIMESTAMP DEFAULT sysdate);
存储过程JOB_SUBMIT提交一个系统提供的定时任务。 PKG_SERVICE.JOB_SUBMIT函数原型为: 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); 当创建一个定时任务(JOB)时,系统默认将当前数据库和用户名与当前创建的定时任务绑定起来。该接口函数可以通过call或select调用,如果通过select调用,可以不填写出参。如果在存储过程中,则需要通过perform调用该接口函数。如果提交的sql语句任务使用到非public的schema,应该指定表或者函数的schema,或者在sql语句前添加set current_schema = xxx;语句。
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');
存储过程UPDATE修改定时任务的属性,包括任务内容、下次执行时间、执行间隔。 PKG_SERVICE.JOB_UPDATE函数原型为: PKG_SERVICE.JOB_UPDATE( id IN BIGINT, next_time IN TIMESTAMP, interval_time IN TEXT, content IN TEXT);
示例: CALL PKG_SERVICE.JOB_UPDATE(101,'call userproc();', sysdate, 'sysdate + 1.0/1440'); CALL PKG_SERVICE.JOB_UPDATE(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440');
存储过程SUBMIT_ON_NODES创建一个所有CN/DN上的定时任务,仅sysadmin有此权限。 PKG_SERVICE.SUBMIT_ON_NODES函数原型为: PKG_SERVICE.SUBMIT_ON_NODES( node_name IN TEXT, database IN TEXT what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', job OUT INTEGER)
示例: select pkg_service.submit_on_nodes('ALL_NODE', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second'''); select pkg_service.submit_on_nodes('CCN', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second''');
ISUBMIT_ON_NODES与SUBMIT_ON_NODES语法功能相同,但其第一个参数是入参,即指定的作业号,SUBMIT最后一个参数是出参,表示系统自动生成的作业号。仅sysadmin有此权限。
gaussdb@postgres> create table t_job (value TIMESTAMP); CREATE TABLE
gaussdb@postgres> insert into t_job values(sysdate); INSERT 0 1
gaussdb@postgres> select * from t_job; +---------------------+ | value | |---------------------| | 2021-10-09 04:36:20 | +---------------------+ SELECT 1
gaussdb@postgres> select pkg_service.job_submit(null, 'insert into t_job values(sysdate);',sysdate,'sysdate + 1/1440'); +--------------+ | job_submit | |--------------| | 15566 | +--------------+ SELECT 1
gaussdb@postgres> 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
gaussdb@postgres> 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 gaussdb@postgres> 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
gaussdb@postgres> select pkg_service.job_update(15566,null,'sysdate + 2/1440',null); +--------------+ | job_update | |--------------| | | +--------------+ SELECT 1
[gaussdb@postgres> select job_id,interval from pg_job where job_id=15566; +----------+------------------+ | job_id | interval | |----------+------------------| | 15566 | sysdate + 2/1440 | +----------+------------------+ SELECT 1](<gaussdb@postgres%3E 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 | <--- 开始间隔2分钟 +---------------------+ SELECT 24 Time: 0.088s gaussdb@postgres> 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>
禁用和启用都是同样的函数pkg_service.job_finish,传入不同的参数表示是禁用还是启用。 gaussdb@postgres> select pkg_service.job_finish(15566,true,null); +--------------+ | job_finish | |--------------| | | +--------------+ SELECT 1 Time: 0.089s gaussdb@postgres> 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 gaussdb@postgres> select pkg_service.job_finish(15566,false,null); +--------------+ | job_finish | |--------------| | | +--------------+ SELECT 1 Time: 0.091s gaussdb@postgres> 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 可以看到如果重新启用任务的时候,没有指定下次运行时间,那么下次运行时间会始终保持在4000年,意味着仍然不会启动,所以如果禁用任务之后再重新启动,需要手动显式指定下次运行时间。 gaussdb@postgres> select pkg_service.job_finish(15566,false,sysdate); +--------------+ | job_finish | |--------------| | | +--------------+ SELECT 1 Time: 0.088s gaussdb@postgres> 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
gaussdb@postgres> select pkg_service.job_cancel(15566); +--------------+ | job_cancel | |--------------| | | +--------------+ SELECT 1 Time: 0.082s gaussdb@postgres> 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 gaussdb@postgres> select * from pg_catalog.pg_job_proc pjp where job_id=15566; +----------+--------+ | job_id | what | |----------+--------| +----------+--------+ SELECT 0 Time: 0.087s 墨天轮原文链接:https://www./db/137362?sjhy(复制到浏览器或者点击“阅读原文”立即查看) |
|