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

举报
tyxxjtu 发表于 2021/07/01 15:58:10 2021/07/01
【摘要】 定时任务在数据库运维的很多场景下,是一个十分重要的工具,例如用户希望周期性地执行某个运维操作,或者为了不影响白天的正常业务,希望将某个运维操作放到深夜自动执行。针对这些场景,GaussDB(DWS) 内置了定时任务功能。用户创建的定时任务能够周期性地自动触发,从而减少了用户的工作量。 如何使用?GaussDB(DWS) 中对定时任务的各种操作,被封装成了函数,放在 DBMS_JOB 高级包中...

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

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

如何使用?

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

创建

函数定义

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

参数

参数名 类型 说明
job BIGINT 任务号,是定时任务的唯一标识。对定时任务的各种操作都需要指定任务号
what TEXT 任务内容,一般为一条或多条 SQL 语句,多条 SQL 语句之间用分号隔开
next_date TIMESTAME WITHOUT TIME ZONE 任务第一次触发的时间。如果该参数为一个过去的时间,则定时任务在创建后会立即触发。该参数的默认值为当前系统时间
interval_time TEXT 任务的触发周期,一般为一个表达式,通过计算该表达式的值,得到任务的下一次运行时间。如果该参数为 NULL,则定时任务只触发一次,而不会周期性地触发。该参数的默认值为 NULL

示例

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

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

补充说明

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

  1. INTERVAL 类型的表达式,例如:‘INTERVAL ‘‘3 minutes’’’ 表示定时任务每隔 3 分钟触发一次。
  2. 类似于 ‘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)

参数

参数名 类型 说明
job BIGINT 任务号

示例

SELECT DBMS_JOB.remove(1);

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

修改

函数定义

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

参数

参数名 类型 说明
job BIGINT 任务号
what TEXT 任务内容。该参数为 NULL 代表维持原本的任务内容
next_date TIMESTAME WITHOUT TIME ZONE 任务下次执行的时间。该参数为 NULL 代表维持原本的执行时间
interval_time TEXT 任务的触发周期,一般为一个表达式,通过计算该表达式的值,得到任务的下一次运行时间。该参数为 NULL 代表维持原本的周期

示例

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 函数,手动将定时任务设置为失效状态。

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

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

  1. 定时任务已经失效。可以按照上一个问题的方案,查询定时任务的状态,如果该任务确实已经失效,可以通过 DBMS_JOB.broken 函数恢复。
  2. 设定的执行间隔小于定时任务的执行时间。这种情况在使用建议中已经说过了。
  3. 当前正在执行的定时任务到达了上限,该上限由配置参数 job_queue_processes 控制。如果的确到达了上限,可以修改该配置参数。

定时任务执行失败,如何查看报错?

可以在日志中查看。

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

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

总结

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

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

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。