如何使用pg_cron设置每90秒执行的数据库定时任务

核心思路 #

pg_cron本身仅支持分钟级及以上的定时粒度，无法直接设置秒级任务。我们可以通过「pg_cron启动持久化后台循环函数」的方式实现需求：

1. 封装实际的更新逻辑到独立函数中
2. 创建带循环和休眠的后台函数，用排他锁避免多实例并行
3. 用pg_cron设置每分钟执行一次的启动任务，确保后台函数意外终止后能自动重启

具体步骤 #

1. 创建更新逻辑函数 #

先把你的业务更新逻辑封装成独立函数，方便复用和维护：

CREATE OR REPLACE FUNCTION update_target_value()
RETURNS void AS $$
BEGIN
    -- 替换成你的实际更新语句
    UPDATE your_target_table 
    SET target_column = target_column + 1 -- 示例更新逻辑，按需修改
    WHERE your_filter_condition; -- 按需添加过滤条件
END;
$$ LANGUAGE plpgsql;

2. 创建后台循环执行函数 #

这个函数会循环调用更新逻辑，每次执行后休眠90秒，同时用数据库 advisory lock 防止多个实例同时运行：

CREATE OR REPLACE FUNCTION run_every_90_seconds()
RETURNS void AS $$
DECLARE
    lock_id integer := 12345; -- 自定义唯一锁ID，避免和其他业务锁冲突
BEGIN
    -- 尝试获取排他锁，已存在锁则直接退出，防止重复启动
    IF NOT pg_try_advisory_lock(lock_id) THEN
        RETURN;
    END IF;

    -- 循环执行更新任务
    LOOP
        PERFORM update_target_value();
        PERFORM pg_sleep(90); -- 休眠90秒
    END LOOP;

    -- 释放锁（理论上循环不会终止，除非手动停止）
    PERFORM pg_advisory_unlock(lock_id);
END;
$$ LANGUAGE plpgsql;

3. 配置pg_cron启动任务 #

设置pg_cron每分钟执行一次，确保后台函数在意外终止后能自动恢复运行：

SELECT cron.schedule(
    'start-90s-update-job', -- 自定义任务名称
    '* * * * *', -- 每分钟执行一次的cron表达式
    'SELECT run_every_90_seconds();'
);

注意事项 #

• 停止任务：需要终止循环时，按以下步骤操作：

  -- 停止pg_cron的启动任务
  SELECT cron.unschedule('start-90s-update-job');
  -- 释放排他锁
  SELECT pg_advisory_unlock(12345);
  -- 查找并终止后台进程（替换pid为实际进程ID）
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%run_every_90_seconds%';
  

• 锁ID唯一性：确保lock_id是数据库内唯一值，避免和其他使用 advisory lock 的业务冲突
• 时间校准：如果更新操作本身耗时较长，需要调整休眠时间，保证两次任务的总间隔符合90秒要求（比如更新耗时10秒，休眠时间设为80秒）
• 日志监控：可以在更新函数中添加日志，方便排查问题：

  RAISE NOTICE 'Updated target value at %', now();
  

内容的提问来源于stack exchange，提问作者Dharmendra Soni