GaussDB(DWS) SQL进阶之高级包（一）DBMS_JOB

定时任务在数据库运维的很多场景下，是一个十分重要的工具，例如用户希望周期性地执行某个运维操作，或者为了不影响白天的正常业务，希望将某个运维操作放到深夜自动执行。

针对这些场景，GaussDB(DWS) 内置了定时任务功能。用户创建的定时任务能够周期性地自动触发，从而减少了用户的工作量。

如何使用？

GaussDB(DWS) 中对定时任务的各种操作，被封装成了函数，放在 DBMS_JOB 高级包中，下面介绍这些函数的使用方法。

创建

函数定义

DBMS_job.isubmit(job BIGINT, what TEXT, next_date TIMESTAME WITHOUT TIME ZONE, interval_time TEXT)

参数

示例

SELECT DBMS_JOB.isubmit(1, 'CALL usr_procedure_1();', SYSDATE, 'INTERVAL ''1 hour''');

执行该语句，会创建一个任务号为 1 的定时任务，并在创建之后立即触发，之后该任务每隔 1 小时触发一次，每次触发该任务会调用 usr_procedure_1 这个存储过程。

补充说明

相信读者一定对 interval_time 这个参数一头雾水，应该传入什么表达式，才能计算得到下一次运行时间呢？事实上，interval_time 的值主要有两种形式：

INTERVAL 类型的表达式，例如：‘INTERVAL ‘‘3 minutes’’’ 表示定时任务每隔 3 分钟触发一次。

类似于 ‘SYSDATE + 1’ 的表达式。每次触发定时任务时，会计算该表达式的值，得到的时间就是下次触发定时任务的时间，因此 interval_time 为 ‘SYSDATE + 1’ 表示定时任务每隔一天触发一次。

用户设置 interval_time 时，最好使用上述的两种形式之一，其它表达式一般无法被解析。此外，需要注意 interval_time 参数的类型为 TEXT 类型，因此无论使用哪种表达式，都应该给表达式外层加上单引号，构成一个字符串。

除了 DBMS_JOB.isubmit 函数，还有一个 DBMS_JOB.submit 函数用于创建定时任务，DBMS_JOB.submit 函数的定义如下：

DBMS_JOB.submit(what TEXT, next_date TIMESTAME WITHOUT TIME ZONE, interval_time TEXT, OUT job INTEGER)

DBMS_job.submit 函数的 what、next_date 和 interval_time 参数含义都和 DBMS_JOB.isubmit 函数一致，差异在于 DBMS_job.submit 函数不需要传入 job 这个参数，即不需要指定任务号，改为由系统分配一个任务号，并作为返回值返回。

删除

函数定义

DBMS_JOB.remove(job BIGINT)

参数

示例

SELECT DBMS_JOB.remove(1);

执行该语句，会删除任务号为 1 的定时任务。

修改

函数定义

DBMS_JOB.change(job BIGINT, what TEXT, next_date TIMESTAME WITHOUT TIME ZONE, interval_time TEXT)

参数

示例

SELECT DBMS_JOB.change(1, 'CALL usr_procedure_2();', NULL, 'INTERVAL ''2 hours''');

执行该语句，会将任务号为 1 的定时任务的任务内容修改为 ‘CALL usr_procedure_2();’，并将任务的触发周期修改为 2 小时。需要注意的是，触发周期的修改会从下一次任务触发开始生效，例如将触发周期从 1 小时修改为 2 小时后，下一次任务触发的时间和上一次任务触发的时间仍会间隔 1 小时，之后每次任务触发间隔 2 小时。

补充说明

需要注意的是，DBMS_JOB.change 函数中，interval_time 参数为 NULL 表示维持原本的周期，这和 DBMS_JOB.isubmit 不一样。对于 DBMS_JOB.isubmit 函数，interval_time 为 NULL 表示定时任务只触发一次，而不是周期性地触发。

那么如果用户想要修改某个定时任务，将其修改为非周期性触发，应该在 DBMS_JOB.change 函数中将 interval_time 设置为什么值呢？查阅产品文档得知，这种情况可以将 interval_time 设置为字符串 ‘null’。例如：

SELECT DBMS_JOB.change(1, NULL, NULL, 'null');

执行该语句，会将任务号为 1 的定时任务修改为非周期性触发，该定时任务只会根据原本的执行周期再执行 1 次，之后便不再触发。

查询

所有定时任务被保存在 pg_jobs 系统表中，用户可以通过该系统表，查询定时任务的信息。pg_jobs 系统表各列的含义可以参考产品文档，这里就不再赘述了。

使用建议

这里以我个人的经验，提出几点使用定时任务功能的建议。受个人水平的限制，这些建议不一定适用于所有人。

定时任务的任务内容中，尽量不要放 SELECT 语句

一般情况下，用户使用 SELECT 语句是为了获取查询结果，但是定时任务只会在后台执行，没有机会将查询结果返回给用户。如果确实需要在定时任务中进行查询，推荐使用 INSERT INTO … SELECT 语句将查询结果保存到某张表中，用户只需要在定时任务执行完毕后查询这张表，就能看见 SELECT 语句的结果。

定时任务的任务内容中，尽量显式指定各对象的 schema

众所周知，表、视图、函数、存储过程等对象都是有 schema 的，不同 schema 的对象可以同名。如果某个对象未显式指定 schema，SQL 解释器会在 current_schema 中试图寻找该对象，如果 current_schema 中没有该对象则会报错。

创建定时任务时，定时任务会将此时的 current_schema 记录下来，记作 job_nsp，之后每次执行任务语句之前，会先将 current_schema 切到 job_nsp。

因此，最好在任务内容中，显式指定各对象的 schema，或者在任务内容中的第一条语句中设置 current_schema，否则，需要确保未显式指定 schema 的对象在 job_nsp 中，这样的设计显然不是很可靠。

定时任务的执行间隔，不要小于定时任务的执行时间

设置定时任务的执行间隔前，最好先预估任务的执行时间，保证执行间隔不小于任务的执行时间，因为任务触发时，会先检查本任务上一次执行是否结束，若没有结束，会先等待上一次执行结束，再进行本次执行。如果定时任务的执行间隔小于执行时间的话，定时任务可能不会按照设置的周期触发。

常见问题

如何查看定时任务的状态？

可以查询 pg_job 视图（注意，不是 pg_jobs 系统表）的 job_status 列，该列记录了定时任务的当前状态。定时任务的状态共有以下 4 种：

r（running）状态：定时任务正在执行。

s（successful）状态：定时任务最近一次执行成功。

f（failed）状态：定时任务最近一次执行失败。定时任务刚创建，还未触发时，默认也是该状态。

d（disable）状态：定时任务失效。处于失效状态的定时任务不会被触发。定时任务进入失效状态的原因可能有：(1) 定时任务被创建为只触发一次的任务，且已被触发；(2) 定时任务连续执行失败 16 次；(3) 通过 DBMS_JOB.broken 函数，手动将定时任务设置为失效状态。

为什么定时任务没有按照设定的周期触发？

定时任务未按照设定的周期触发，可能有多种原因。

定时任务已经失效。可以按照上一个问题的方案，查询定时任务的状态，如果该任务确实已经失效，可以通过 DBMS_JOB.broken 函数恢复。

设定的执行间隔小于定时任务的执行时间。这种情况在使用建议中已经说过了。

当前正在执行的定时任务到达了上限，该上限由配置参数 job_queue_processes 控制。如果的确到达了上限，可以修改该配置参数。

定时任务执行失败，如何查看报错？

可以在日志中查看。

为什么我的定时任务不见了？

定时任务有自动的级联删除机制，如果创建定时任务的用户被删除，或者创建定时任务的数据库被删除，那么定时任务也会被级联删除。

总结

定时任务是数据库的一个重要功能，本文介绍了 GaussDB(DWS) 中定时任务功能的用法、使用建议和一些常见问题。

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。