PostgreSQL异常无效果事务问题：转账模拟未写入失败交易表

问题场景 #

开发银行账户间交易模拟程序时，测试事务未生成预期的failed_transactions记录。当交易导致账户余额违反balance >= -500的CHECK约束时，本该写入失败日志，但表中无任何数据。

原始代码 #

DROP TABLE IF EXISTS clients, accounts, transactions, failed_transactions, successful_transactions CASCADE;


CREATE TABLE IF NOT EXISTS clients
(
    id int PRIMARY KEY,
    name varchar
);

CREATE TABLE IF NOT EXISTS accounts
(
    id int PRIMARY KEY,
    balance float,
    client int,
    FOREIGN KEY (client) REFERENCES clients(id),
    CHECK (balance >= -500)
);

CREATE TABLE IF NOT EXISTS transactions
(
    id serial PRIMARY KEY,
    amount float
);

CREATE TABLE failed_transactions
(
    id serial,
    account_id integer,
    attempted_amount float,
    timestamp timestamp DEFAULT NOW()
);

CREATE TABLE successful_transactions
(
    id serial,
    account_id integer,
    attempted_amount float,
    timestamp timestamp DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_failed_transaction()
RETURNS trigger AS $$
    BEGIN
        IF NEW.balance<-500 THEN
            INSERT INTO failed_transactions(account_id, attempted_amount)
                        VALUES (NEW.id, NEW.balance-OLD.balance);
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION log_successful_transaction()
RETURNS trigger AS $$
    BEGIN
        IF NEW.balance>=-500 THEN
            INSERT INTO successful_transactions(account_id, attempted_amount)
                        VALUES (NEW.id, NEW.balance-OLD.balance);
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER trigger_for_failed_transactions BEFORE UPDATE ON accounts
FOR EACH STATEMENT
EXECUTE FUNCTION log_failed_transaction();

CREATE OR REPLACE TRIGGER trigger_for_successful_transactions AFTER UPDATE ON accounts
FOR EACH STATEMENT
EXECUTE FUNCTION log_successful_transaction();


INSERT INTO clients VALUES (10, 'Client 1');
INSERT INTO clients VALUES (14, 'Client 2');
INSERT INTO clients VALUES (25, 'Client 3');

INSERT INTO accounts VALUES (8, 2000, 14);
INSERT INTO accounts VALUES (12, 650, 10);
INSERT INTO accounts VALUES (2, 300, 25);



BEGIN;
DO $$
DECLARE
    payer integer := 10; --payer referenced by bank1
    recipient integer := 25; --recipient referenced by bank2
    amount float := 100; --amount to transfer
BEGIN
    -- Attempt to execute a query that might fail
    UPDATE accounts
        SET balance = balance - amount
        WHERE client = payer;
    UPDATE accounts
        SET balance = balance + amount
        WHERE client = recipient;
EXCEPTION WHEN others THEN
    RAISE NOTICE 'An error occurred: %', SQLERRM;
END $$;
COMMIT;



SELECT * FROM successful_transactions;

问题根源 #

1. 触发器类型错误：两个触发器都使用了FOR EACH STATEMENT（语句级触发器），但触发器函数中引用了NEW和OLD变量——这两个变量仅在**行级触发器（FOR EACH ROW）**中有效。语句级触发器无法获取单条更新行的新旧值，导致函数中的条件判断永远不会触发，自然不会插入日志。

2. 事务回滚导致日志丢失：即使触发器类型正确，当UPDATE操作违反CHECK约束时，PostgreSQL会抛出错误并回滚整个事务，包括触发器中已经执行的INSERT操作，最终failed_transactions表依然无记录。

修复方案 #

步骤1：将触发器改为行级触发器 #

修改两个触发器的定义，把FOR EACH STATEMENT替换为FOR EACH ROW：

CREATE OR REPLACE TRIGGER trigger_for_failed_transactions BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_failed_transaction();

CREATE OR REPLACE TRIGGER trigger_for_successful_transactions AFTER UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_successful_transaction();

步骤2：实现不受事务回滚影响的失败日志写入 #

PostgreSQL没有Oracle的自治事务特性，可通过dblink扩展在独立事务中插入失败记录，避免回滚丢失日志：

1. 先安装dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 修改log_failed_transaction函数：

CREATE OR REPLACE FUNCTION log_failed_transaction()
RETURNS trigger AS $$
DECLARE
    v_conn text := 'dbname=' || current_database();
BEGIN
    IF NEW.balance < -500 THEN
        -- 用dblink在独立事务中写入失败日志
        PERFORM dblink_exec(v_conn,
            format('INSERT INTO failed_transactions(account_id, attempted_amount) VALUES (%L, %L)', NEW.id, NEW.balance - OLD.balance));
        -- 抛出错误阻止非法更新
        RAISE EXCEPTION '账户ID % 余额将低于-500，交易终止', NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤3：调整测试事务的金额以触发失败场景 #

将测试事务中的amount改为2000（让payer账户余额变为650-2000=-1350，违反CHECK约束）：

BEGIN;
DO $$
DECLARE
    payer integer := 10;
    recipient integer := 25;
    amount float := 2000; -- 修改为足够大的金额触发失败
BEGIN
    UPDATE accounts
        SET balance = balance - amount
        WHERE client = payer;
    UPDATE accounts
        SET balance = balance + amount
        WHERE client = recipient;
EXCEPTION WHEN others THEN
    RAISE NOTICE 'An error occurred: %', SQLERRM;
END $$;
COMMIT;

验证 #

执行修改后的代码后，查询failed_transactions表，会看到对应的失败交易记录；查询successful_transactions表，正常交易时会生成成功日志。

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