PostgreSQL存储过程异常块前置代码被跳过，请求修复并实现监控

问题根源分析 #

你的存储过程存在几个核心问题，导致执行逻辑异常或监控失效：

• 每个步骤独立执行COMMIT，拆分了事务边界，既破坏操作原子性，也会导致异常处理时的状态更新逻辑不准确
• 未处理全流程执行成功后的监控状态更新，导致监控表中始终存在end_date为null的记录
• 异常块中的COMMIT属于冗余操作，可能引发事务上下文冲突
• 监控表更新仅通过proc_name和end_date is null匹配，并发执行时会误更新其他实例的记录

修复后的代码 #

CREATE OR REPLACE PROCEDURE "y4a_fin_analyst"."hungcht_update_table_database_monitoring"()
AS $BODY$
DECLARE
    _sql_state TEXT;
    _message TEXT;
    _proc_monitor_id INT; -- 保存当前监控记录的主键，确保精准更新
BEGIN
    -- Step 1: 插入监控记录并获取主键，标记为运行中状态
    INSERT INTO "y4a_fin_analyst"."hungcht_proc_monitoring"
    (proc_name, run_date, end_date, status, err_message, err_sqlstate)
    VALUES
    ('hungcht_update_table_database_monitoring',
     current_timestamp AT TIME ZONE 'Asia/Bangkok',
     null, 'running', null, null)
    RETURNING id INTO _proc_monitor_id; -- 若表无id主键，可替换为proc_name+run_date的组合字段
    
    -- Step 2: 插入新创建的表记录
    INSERT INTO y4a_fin_analyst.hungcht_table_database_monitoring
    SELECT * FROM y4a_fin_analyst.hungcht_check_table A
    WHERE NOT EXISTS (
        SELECT 1 FROM y4a_fin_analyst.hungcht_table_database_monitoring b
        WHERE a.table_name = b.table_name
          AND a.schema_name = b.schema_name
          AND a.owner_name = b.owner_name
    );
    
    -- Step 3: 更新行计数和更新时间
    TRUNCATE y4a_fin_analyst.hungcht_table_database_monitoring_tmp;
    
    INSERT INTO y4a_fin_analyst.hungcht_table_database_monitoring_tmp 
    SELECT
        a.table_name,
        a.schema_name,
        a.owner_name,
        a.row_count,
        b."row_count" AS last_row_count,
        a."row_count" - b."row_count" AS row_change_cnt,
        COALESCE(b.table_type, a.table_type) AS table_type,
        b.create_date,
        a.update_date
    FROM y4a_fin_analyst.hungcht_check_table A
    JOIN y4a_fin_analyst.hungcht_table_database_monitoring b
        ON a.table_name = b.table_name
        AND a.schema_name = b.schema_name
        AND a.owner_name = b.owner_name
        AND a."row_count" <> b."row_count";
    
    DELETE FROM y4a_fin_analyst.hungcht_table_database_monitoring A
    WHERE EXISTS (
        SELECT 1 FROM y4a_fin_analyst.hungcht_table_database_monitoring_tmp b
        WHERE a."table_name" = b."table_name"
          AND a."schema_name" = b."schema_name"
          AND a."owner_name" = b."owner_name"
    );
    
    INSERT INTO y4a_fin_analyst.hungcht_table_database_monitoring
    SELECT * FROM y4a_fin_analyst.hungcht_table_database_monitoring_tmp;

    -- 所有步骤执行成功，更新监控状态为成功
    UPDATE "y4a_fin_analyst"."hungcht_proc_monitoring"
    SET
        end_date = current_timestamp AT TIME ZONE 'Asia/Bangkok',
        status = 'success'
    WHERE id = _proc_monitor_id;
    
    COMMIT; -- 统一提交所有操作，保证原子性

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _message := MESSAGE_TEXT,
        _sql_state := RETURNED_SQLSTATE;

    -- 更新监控记录为错误状态
    UPDATE "y4a_fin_analyst"."hungcht_proc_monitoring"
    SET
        end_date = current_timestamp AT TIME ZONE 'Asia/Bangkok',
        status = 'error',
        err_message = _message,
        err_sqlstate = _sql_state
    WHERE id = _proc_monitor_id;
    
    ROLLBACK; -- 回滚所有未提交操作，若业务允许保留部分步骤，可改为COMMIT
END;
$BODY$
LANGUAGE plpgsql;

关键修改说明 #

1. 精准跟踪监控记录：插入监控记录时通过RETURNING获取主键，避免并发场景下误更新其他实例的记录
2. 统一事务管理：移除每个步骤的独立COMMIT，改为全流程成功后统一提交，保证操作原子性；出错时回滚所有未提交修改
3. 补充成功状态更新：全流程执行完成后主动将监控状态设为success，完善监控链路
4. 优化初始状态：插入监控记录时标记为running，清晰反映作业执行阶段
5. 简化异常处理：移除异常块中的冗余COMMIT，确保事务逻辑一致

注意事项 #

• 若hungcht_proc_monitoring表无id主键，可替换为proc_name+run_date的组合条件作为更新依据
• 若业务要求部分步骤必须独立提交（如不能回滚的操作），可将对应步骤放在单独的事务块中，并调整监控逻辑适配多事务场景

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