PG12触发器函数能否执行COMMIT？如何实现禁删数据同时留存操作日志

• 触发器函数运行在触发其执行的外层事务上下文中，本身没有独立事务控制权，直接在函数内编写COMMIT/ROLLBACK语句必然触发invalid transaction termination错误。
• 当触发器抛出EXCEPTION级别异常时，整个外层事务会被全部回滚。无论拆分多少个触发器，只要日志写入操作和异常抛出在同一个事务内，写入的日志记录都会随事务回滚被清除，无法持久化到表中。

PostgreSQL 12没有内置自治事务能力，使用官方自带的dblink扩展实现独立事务写入日志是兼容性、稳定性最高的方案：独立事务的提交不受外层删除事务回滚影响，既能拦截删除操作，也能完整留存删除尝试的日志。

1. 初始化扩展与权限 #

使用超级用户执行以下语句：

-- 安装dblink扩展（属于官方contrib组件，默认随PG发行版提供）
CREATE EXTENSION IF NOT EXISTS dblink;

-- 授予业务用户dblink相关函数的执行权限
GRANT EXECUTE ON FUNCTION dblink_connect(text, text) TO PUBLIC;
GRANT EXECUTE ON FUNCTION dblink_exec(text, text) TO PUBLIC;
GRANT EXECUTE ON FUNCTION dblink_disconnect(text) TO PUBLIC;

-- 收回普通用户对日志表的直接写入权限，避免篡改日志
REVOKE ALL ON test_del_log FROM PUBLIC;

2. 编写拦截触发器函数 #

核心逻辑是通过dblink建立独立数据库连接写入日志，日志写入后在独立连接中自动提交，不受外层事务回滚影响：

CREATE OR REPLACE FUNCTION prevent_del_trg()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
DECLARE
    -- 连接串可配置为专用的日志写入账号，安全性更高
    v_conn text := format('dbname=%s user=log_writer', current_database());
    v_log_sql text;
BEGIN
    -- 仅对指定用户做删除拦截
    IF current_user = 'X' THEN
        -- 构造日志插入语句，format转义避免SQL注入问题
        v_log_sql := format(
            'INSERT INTO test_del_log(id, value, del_date) VALUES (%L, %L, now())',
            OLD.id, OLD.value
        );
        -- 独立连接执行日志写入，操作自动提交
        PERFORM dblink_connect('del_log_conn', v_conn);
        PERFORM dblink_exec('del_log_conn', v_log_sql);
        PERFORM dblink_disconnect('del_log_conn');
        -- 抛出异常终止删除操作，外层事务回滚不影响已提交的日志
        RAISE EXCEPTION 'Delete guard. Access denied for user X';
    END IF;
    -- 非拦截用户正常执行删除
    RETURN OLD;
END;
$body$;

3. 绑定行级删除触发器 #

CREATE TRIGGER test_prevent_del_trg
    BEFORE DELETE ON public.test
    FOR EACH ROW
    EXECUTE PROCEDURE prevent_del_trg();

• 建议为日志写入创建专用的数据库账号，仅给该账号授予日志表的INSERT权限，避免普通用户篡改、删除审计日志。
• 若不想使用dblink，第三方扩展pg_background也可实现类似自治事务效果，但dblink为官方维护组件，生产环境使用风险更低。
• 不要使用AFTER类型触发器实现拦截，BEFORE触发器在删除操作实际执行前就会触发拦截，无额外IO开销。

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