pg_cron执行事务块失败：EndTransactionBlock异常排查

问题详情 #

• 有5个逻辑一致、仅操作目标表不同的事务任务，以裸字符串形式传入pg_cron后台Worker执行，事务结构为BEGIN;(若干查询)END;
• 手动执行所有任务均成功，但通过pg_cron并行执行时，80%概率触发报错FATAL: EndTransactionBlock: unexpected state BEGIN，仅极少数任务能成功完成
• 该问题仅在Azure/Robin K8s集群搭配Stolon数据库的环境中复现，本地单节点PostgreSQL环境无此问题

提供的示例脚本用于删除public.dummy表中未被public.connecteddummy表引用的实体（即id和originalid均未出现在connecteddummy表中的记录），脚本内容如下：

BEGIN;
        LOCK TABLE public.dummy IN ACCESS EXCLUSIVE MODE;
        ALTER TABLE public.dummy DISABLE TRIGGER ALL;
        CREATE INDEX IF NOT EXISTS idx_connecteddummy_id ON public.connecteddummy(id);
        CREATE INDEX IF NOT EXISTS idx_connecteddummy_originalid ON public.connecteddummy(originalid);
        CREATE TEMP TABLE connecteddummy_ids ON COMMIT DROP AS (
            SELECT d.Id 
            FROM public.dummy d 
            LEFT JOIN public.connecteddummy cd 
            ON d.Id = cd.Id 
            LEFT JOIN public.connecteddummy cdd 
            ON d.Id = cdd.OriginalId 
            WHERE cd.Id IS null 
            AND cdd.OriginalId IS null 
            ORDER BY d.Id 
            LIMIT 50000
        );
        CREATE INDEX IF NOT EXISTS idx_ids_id ON connecteddummy_ids(Id);
        DELETE FROM public.dummy d 
        USING connecteddummy_ids v 
        WHERE d.Id = v.Id;
        DROP INDEX IF EXISTS idx_connecteddummy_Id;
        DROP INDEX IF EXISTS idx_connecteddummy_OriginalId;
        ALTER TABLE public.dummy ENABLE TRIGGER ALL;
    END;

问题分析 #

报错FATAL: EndTransactionBlock: unexpected state BEGIN的核心原因是事务状态冲突：

1. pg_cron默认会将每个任务包装在一个隐式事务中执行，手动添加的BEGIN/END会导致事务嵌套，而PostgreSQL本身不支持嵌套事务，在Stolon集群的事务上下文管理下，这种嵌套会触发状态异常
2. Stolon作为分布式PostgreSQL集群管理工具，在连接池复用、事务状态同步上和单节点环境存在差异，并行执行时更容易暴露这种隐式+显式事务的冲突问题

解决方案 #

1. 移除显式事务包裹：删除脚本中的BEGIN;和END;，让pg_cron自动处理事务生命周期。pg_cron的任务默认会在一个独立事务中运行，执行完成后自动提交，失败则自动回滚
   修改后的示例脚本：

   LOCK TABLE public.dummy IN ACCESS EXCLUSIVE MODE;
   ALTER TABLE public.dummy DISABLE TRIGGER ALL;
   CREATE INDEX IF NOT EXISTS idx_connecteddummy_id ON public.connecteddummy(id);
   CREATE INDEX IF NOT EXISTS idx_connecteddummy_originalid ON public.connecteddummy(originalid);
   CREATE TEMP TABLE connecteddummy_ids ON COMMIT DROP AS (
       SELECT d.Id 
       FROM public.dummy d 
       LEFT JOIN public.connecteddummy cd 
       ON d.Id = cd.Id 
       LEFT JOIN public.connecteddummy cdd 
       ON d.Id = cdd.OriginalId 
       WHERE cd.Id IS null 
       AND cdd.OriginalId IS null 
       ORDER BY d.Id 
       LIMIT 50000
   );
   CREATE INDEX IF NOT EXISTS idx_ids_id ON connecteddummy_ids(Id);
   DELETE FROM public.dummy d 
   USING connecteddummy_ids v 
   WHERE d.Id = v.Id;
   DROP INDEX IF EXISTS idx_connecteddummy_Id;
   DROP INDEX IF EXISTS idx_connecteddummy_OriginalId;
   ALTER TABLE public.dummy ENABLE TRIGGER ALL;
   

2. 错开并行任务调度：如果必须保持并行执行，可将5个任务的调度时间设置为错开（比如间隔1分钟），减少Stolon集群中同时触发的事务冲突概率
3. 确认锁级别必要性：由于脚本中需要禁用触发器，ACCESS EXCLUSIVE锁是必须的，此锁会阻塞所有其他操作，无需调整

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