PostgreSQL匿名块执行更新时出现事务不存在警告问题

问题场景 #

你尝试通过匿名块更新diagnostic_file表中controller_id为空的记录，执行的代码如下：

DO
$$BEGIN
    FOR diag_file IN 
        SELECT d_file.diagnostic_file_id AS diagnostic_file_id, 
            controller.controller_id AS controller_id
        FROM diagnostic_file d_file
        JOIN decoder_mapping d_mapping ON d_mapping.decoder_mapping_id = d_file.decoder_mapping_id
        JOIN device_model d_model ON d_model.device_model_id = d_mapping.device_model_id
        JOIN controller ON controller.device_model_id = d_model.device_model_id
        WHERE d_file.controller_id IS NULL
    LOOP
        UPDATE diagnostic_file SET controller_id = diag_file.controller_id WHERE diagnostic_file.diagnostic_file_id = diag_file.diagnostic_file_id;
        COMMIT;
    END LOOP;
END;$$;

执行后收到错误警告：

WARNING:  there is no transaction in progress

Query 1 OK: COMMIT

错误原因 #

PostgreSQL的DO匿名块默认会在单个事务中执行所有逻辑。你在循环内执行COMMIT，第一次执行就会结束当前事务，后续循环迭代时已经没有活跃事务，因此触发该警告。

解决方法 #

方法1：移除循环内的COMMIT #

把循环里的COMMIT语句删除，让整个匿名块在一个事务中完成所有更新操作：

DO
$$BEGIN
    FOR diag_file IN 
        SELECT d_file.diagnostic_file_id AS diagnostic_file_id, 
            controller.controller_id AS controller_id
        FROM diagnostic_file d_file
        JOIN decoder_mapping d_mapping ON d_mapping.decoder_mapping_id = d_file.decoder_mapping_id
        JOIN device_model d_model ON d_model.device_model_id = d_mapping.device_model_id
        JOIN controller ON controller.device_model_id = d_model.device_model_id
        WHERE d_file.controller_id IS NULL
    LOOP
        UPDATE diagnostic_file SET controller_id = diag_file.controller_id WHERE diagnostic_file.diagnostic_file_id = diag_file.diagnostic_file_id;
    END LOOP;
END;$$;

执行完成后，PostgreSQL会自动提交整个事务。

方法2：改用单条UPDATE语句（更高效） #

逐行循环更新的效率极低，尤其是当表中数据量较大时。推荐直接用一条UPDATE语句完成批量更新，逻辑更简洁且性能更好：

UPDATE diagnostic_file d_file
SET controller_id = controller.controller_id
FROM decoder_mapping d_mapping
JOIN device_model d_model ON d_model.device_model_id = d_mapping.device_model_id
JOIN controller ON controller.device_model_id = d_model.device_model_id
WHERE d_file.decoder_mapping_id = d_mapping.decoder_mapping_id
AND d_file.controller_id IS NULL;

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