快速上手openGauss数据库的定时任务

openGauss数据库的定时任务

• event事件
• PKG_SERVICE高级包
• crontab

event事件

CREATE EVENT 在MySQL兼容模式中使用的定时任务
注意事项：

• 仅在sql_compatibility = ‘B’，即MySQL兼容模式支持
• 定时任务时间间隔interval表达式目前兼容了浮点数语法，例如interval 0.5 minute，但是计算时会将浮点数取整，所以不建议interval时间间隔使用浮点数形式
• 定时任务待执行语句不支持的安全相关操作范围主要包括：
  使用加密函数
  创建、设置用户、group
  连接数据库
  函数加密等

案例：

-- 创建记录表
CREATE TABLE t_ev(num int,dtime timestamp);

-- 创建一个执行一次的定时任务
CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT current_timestamp + interval 5 second + interval 1 minute DISABLE DO insert into t_ev values(0,now());
-- 调整定时任务为可用
alter event event_e1 enable;

-- 创建一个每隔一分钟执行一次的定时任务
CREATE EVENT IF NOT EXISTS event_e2 ON SCHEDULE EVERY 1 minute DO insert into t_ev values(1,now());

-- 查看定时任务：执行失败报错、下次执行时间、定时任务信息
show events;		-- 仅MySQL兼容模式可用
select * from pg_job;

操作记录：

m_test=# show sql_compatibility ;
 sql_compatibility 
-------------------
 B
(1 row)
m_test=# CREATE TABLE t_ev(num int,dtime timestamp);
CREATE TABLE
m_test=# CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT current_timestamp + interval 5 second + interval 1 minute DISABLE DO insert into t_ev values(0,now());
CREATE EVENT
m_test=# alter event event_e1 enable;
ALTER EVENT
m_test=# CREATE EVENT IF NOT EXISTS event_e2 ON SCHEDULE EVERY 1 minute DO insert into t_ev values(1,now());
CREATE EVENT
m_test=# select * from t_ev ;
 num |         dtime          
-----+------------------------
   1 | 2024-08-18 21:02:44+08
(1 row)

m_test=# \x
Expanded display is on.
m_test=# show events;
-[ RECORD 1 ]---------------------------
job_name    | event_e1
schema_name | public
log_user    | omm
priv_user   | omm
job_status  | s
start_date  | 2024-08-18 21:03:35.506922
interval    | null
end_date    | 3999-12-31 16:00:00
enable      | t
failure_msg | 
-[ RECORD 2 ]---------------------------
job_name    | event_e2
schema_name | public
log_user    | omm
priv_user   | omm
job_status  | s
start_date  | 2024-08-18 21:02:44.391432
interval    | interval '1'  minute
end_date    | 3999-12-31 16:00:00
enable      | t
failure_msg | 

m_test=# select * from pg_job;
-[ RECORD 1 ]--------+---------------------------
job_id               | 25777
current_postgres_pid | -1
log_user             | omm
priv_user            | omm
dbname               | m_test
node_name            | dn_6001
job_status           | s
start_date           | 2024-08-18 21:03:35.506922
next_run_date        | 2024-08-18 21:03:35.506922
failure_count        | 0
interval             | null
last_start_date      | 
last_end_date        | 
last_suc_date        | 
this_run_date        | 
nspname              | public
job_name             | event_e1
end_date             | 3999-12-31 16:00:00
enable               | t
failure_msg          | 
-[ RECORD 2 ]--------+---------------------------
job_id               | 14333
current_postgres_pid | -1
log_user             | omm
priv_user            | omm
dbname               | m_test
node_name            | dn_6001
job_status           | s
start_date           | 2024-08-18 21:02:44.391432
next_run_date        | 2024-08-18 21:03:44.391432
failure_count        | 0
interval             | interval '1'  minute
last_start_date      | 2024-08-18 21:02:44.391432
last_end_date        | 2024-08-18 21:02:44.392579
last_suc_date        | 2024-08-18 21:02:44.391432
this_run_date        | 2024-08-18 21:02:44.391432
nspname              | public
job_name             | event_e2
end_date             | 3999-12-31 16:00:00
enable               | t
failure_msg          | 

m_test=# \x
Expanded display is off.
m_test=# select * from t_ev ;
 num |         dtime          
-----+------------------------
   1 | 2024-08-18 21:02:44+08
   0 | 2024-08-18 21:03:36+08
   1 | 2024-08-18 21:03:44+08
(3 rows)

m_test=# \x
Expanded display is on.
m_test=# select * from pg_job;
-[ RECORD 1 ]--------+---------------------------
job_id               | 14333
current_postgres_pid | -1
log_user             | omm
priv_user            | omm
dbname               | m_test
node_name            | dn_6001
job_status           | s
start_date           | 2024-08-18 21:02:44.391432
next_run_date        | 2024-08-18 21:05:44.538669
failure_count        | 0
interval             | interval '1'  minute
last_start_date      | 2024-08-18 21:04:44.538669
last_end_date        | 2024-08-18 21:04:44.539942
last_suc_date        | 2024-08-18 21:04:44.538669
this_run_date        | 2024-08-18 21:04:44.538669
nspname              | public
job_name             | event_e2
end_date             | 3999-12-31 16:00:00
enable               | t
failure_msg          | 

PKG_SERVICE高级包

openGauss之定时任务简单管理

定时任务相关参数

• job_queue_processes

• enable_prevent_job_task_startup

案例：

-- pkg_service高级包
create table t_ev2(num int,dtime timestamp);
create sequence seq_num start with 1 increment 1 nomaxvalue;
create or replace procedure job_test as
begin 
  insert into t_ev2 values(seq_num.nextval,now());
end;
/
-- 创建定时任务，每分钟执行一次
select pkg_service.job_submit(1,'select job_test()',sysdate,'sysdate+1/(24*60)');
-- 让定时任务失效,pg_job的job_status字段变成d
select pkg_service.job_finish(1,true);
-- 启动定时任务
select pkg_service.job_finish(1,flase);
-- 修改定时任务为一天执行一次
select pkg_service.job_update(1,sysdate,'sysdate+1',null);
-- 删除定时任务
select pkg_service.job_cancel(1);

操作记录：

openGauss=# create table t_ev2(num int,dtime timestamp);
CREATE TABLE
openGauss=# create sequence seq_num start with 1 increment 1 nomaxvalue;
CREATE SEQUENCE
openGauss=# create or replace procedure job_test as
openGauss$# begin 
openGauss$#   insert into t_ev2 values(seq_num.nextval,now());
openGauss$# end;
openGauss$# /
CREATE PROCEDURE
openGauss=# select pkg_service.job_submit(1,'select job_test()',sysdate,'sysdate+1/(24*60)');
 job_submit 
------------
          1
(1 row)

openGauss=# select * from t_ev2;
 num |           dtime            
-----+----------------------------
   1 | 2024-08-18 21:38:28.651561
(1 row)

openGauss=# \x
Expanded display is on.
openGauss=# select * from pg_job;
-[ RECORD 1 ]--------+---------------------------
job_id               | 1
current_postgres_pid | -1
log_user             | omm
priv_user            | omm
dbname               | postgres
node_name            | dn_6001
job_status           | s
start_date           | 2024-08-18 21:38:28.652765
next_run_date        | 2024-08-18 21:39:29
failure_count        | 0
interval             | sysdate+1/(24*60)
last_start_date      | 2024-08-18 21:38:28.652765
last_end_date        | 2024-08-18 21:38:28.65506
last_suc_date        | 2024-08-18 21:38:28.652765
this_run_date        | 2024-08-18 21:38:28.652765
nspname              | public
job_name             | 
end_date             | 
enable               | 
failure_msg          | 
-[ RECORD 2 ]--------+---------------------------
job_id               | 14333
current_postgres_pid | -1
log_user             | omm
priv_user            | omm
dbname               | m_test
node_name            | dn_6001
job_status           | s
start_date           | 2024-08-18 21:02:44.391432
next_run_date        | 2024-08-18 21:39:46.668673
failure_count        | 0
interval             | interval '1'  minute
last_start_date      | 2024-08-18 21:38:46.668673
last_end_date        | 2024-08-18 21:38:46.669858
last_suc_date        | 2024-08-18 21:38:46.668673
this_run_date        | 2024-08-18 21:38:46.668673
nspname              | public
job_name             | event_e2
end_date             | 3999-12-31 16:00:00
enable               | t
failure_msg          | 

openGauss=# \x
Expanded display is off.
openGauss=# select * from t_ev2;
 num |           dtime            
-----+----------------------------
   1 | 2024-08-18 21:38:28.651561
   2 | 2024-08-18 21:39:29.711587
(2 rows)

crontab

Linux操作系统的crontab配置定时任务脚步