PostgreSQL定时行更新与票务系统定时业务实现方案咨询

嗨，针对你在票务售卖数据库里遇到的两个定时/延时触发需求，我结合PostgreSQL的特性给你梳理下可行的解决方案：

一、实现24小时未交付自动取消交易 #

PostgreSQL确实没有原生的"日期触发器"，但我们可以通过两种方案来实现这个需求：

方案1：使用pg_cron定时检查（推荐，简单易维护） #

pg_cron是PostgreSQL官方维护的定时任务扩展，非常适合这类周期性检查的场景：

1. 首先安装并启用pg_cron：
   • 在postgresql.conf中添加shared_preload_libraries = 'pg_cron'（如果已经有其他扩展，用逗号分隔），然后重启PostgreSQL服务。
   • 登录数据库执行创建扩展的命令：

     CREATE EXTENSION pg_cron;
     

2. 创建定时任务，周期性检查并取消超时交易：

   -- 每5分钟执行一次，找出24小时以上未交付的交易并取消
   SELECT cron.schedule('cancel-unfulfilled-transactions', '*/5 * * * *', $$
       UPDATE transactions
       SET status = 'cancelled', updated_at = NOW()
       WHERE status = 'pending' 
         AND created_at <= NOW() - INTERVAL '24 hours';
   $$);
   

   这里的 cron 表达式*/5 * * * *表示每5分钟执行一次，你可以根据业务精度需求调整间隔（比如每分钟* * * * *）。因为需求是24小时后取消，几分钟的误差完全在可接受范围内。

方案2：pg_notify + 后台服务（适合高精度延时场景） #

如果需要精确到秒的延时触发，可以结合数据库通知和后台服务实现：

1. 创建触发器，在新增待交付交易时发送通知：

   CREATE OR REPLACE FUNCTION notify_pending_transaction()
   RETURNS TRIGGER AS $$
   BEGIN
       -- 发送交易ID和创建时间到专属频道
       PERFORM pg_notify('pending_transaction', NEW.id::text || ',' || NEW.created_at::text);
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_pending_transaction
   AFTER INSERT ON transactions
   FOR EACH ROW WHEN (NEW.status = 'pending')
   EXECUTE FUNCTION notify_pending_transaction();
   

2. 后台服务（比如Python/Go编写）监听pending_transaction频道，收到消息后计算需要延时的时间（24小时减去当前时间与交易创建时间的差值），到点后执行取消交易的SQL语句。这种方式能做到精准延时，但需要额外维护后台服务。

二、每月重置用户售卖限额 #

这个需求同样可以用pg_cron轻松实现，只需创建一个每月执行一次的定时任务：

-- 每月1号凌晨0点重置所有用户的售卖限额
SELECT cron.schedule('reset-monthly-sell-limits', '0 0 1 * *', $$
    UPDATE users
    SET monthly_sell_limit = DEFAULT, remaining_sell_limit = DEFAULT, updated_at = NOW();
$$);

• cron表达式0 0 1 * *表示每月1号0点0分执行，如果需要在每月最后一天执行，可以改成0 0 L * *（pg_cron支持L表示当月最后一天）。
• 如果你的限额不是默认值，而是固定数值，可以把DEFAULT替换成具体数字，比如monthly_sell_limit = 10。

注意事项 #

• 权限控制：默认只有超级用户能创建pg_cron任务，如果要给普通用户权限，执行：GRANT USAGE ON SCHEMA cron TO your_db_user;
• 日志排查：pg_cron的执行日志会输出到PostgreSQL的系统日志中，方便排查任务是否正常运行。
• 事务安全：定时任务中的UPDATE语句是原子操作，PostgreSQL会自动处理锁冲突，不用担心并发问题。

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