PostgreSQL转账事务回滚语法错误及相关问题求助

我在编写处理资金转账的事务脚本时，执行回滚操作出现语法错误，即便仅使用ROLLBACK语句也存在非语法类错误。以下是我的PostgreSQL表结构、触发器函数及测试代码：

DROP TABLE clients, accounts, transactions;

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 int PRIMARY KEY,
    payer int,
    recipient int,
    amount float,
    FOREIGN KEY (payer) REFERENCES clients(id),
    FOREIGN KEY (recipient) REFERENCES clients(id)
);


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 (2, 300, 10);
INSERT INTO accounts VALUES (8, 2000, 14);
INSERT INTO accounts VALUES (12, 650, 25);

CREATE OR REPLACE FUNCTION update_accounts()
RETURNS TRIGGER AS $$
BEGIN
    SAVEPOINT before_transaction;
    
    -- 从付款人余额扣除金额
    UPDATE accounts
    SET balance = balance - NEW.amount
    WHERE client = NEW.payer;

    -- 给收款人余额增加金额
    UPDATE accounts
    SET balance = balance + NEW.amount
    WHERE client = NEW.recipient;
    RETURN NEW;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '交易过程中发生错误：每个账户余额不得低于-500。';
            ROLLBACK TO before_transaction;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER new_transaction
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_accounts();


INSERT INTO transactions VALUES (1, 10, 14, 1000);
SELECT * FROM accounts;

问题分析及解决方案 #

核心问题 #

1. 触发器类型错误：使用AFTER INSERT触发器时，交易记录已经插入到transactions表中，即便后续转账操作失败，这条无效的交易记录仍会保留，且无法通过触发器内的回滚撤销。
2. 回滚语句使用不当：在PL/pgSQL触发器中，直接使用ROLLBACK TO SAVEPOINT会破坏事务上下文，触发器运行在调用它的事务环境中，手动回滚会导致非语法类错误。
3. 不必要的SAVEPOINT：当UPDATE操作违反CHECK约束时，PostgreSQL会自动回滚该操作，无需手动创建SAVEPOINT。

修正后的代码 #

调整触发器为BEFORE INSERT

将触发器改为BEFORE INSERT，这样在转账操作失败时，可以直接阻止无效交易记录插入：

CREATE OR REPLACE FUNCTION update_accounts()
RETURNS TRIGGER AS $$
BEGIN
    -- 扣除付款人账户余额
    UPDATE accounts
    SET balance = balance - NEW.amount
    WHERE client = NEW.payer;

    -- 增加收款人账户余额
    UPDATE accounts
    SET balance = balance + NEW.amount
    WHERE client = NEW.recipient;
    
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION '交易失败：账户余额不能低于-500';
        RETURN NULL; -- 取消当前交易记录的插入
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_transaction
BEFORE INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_accounts();

测试验证 #

执行原测试语句INSERT INTO transactions VALUES (1, 10, 14, 1000);时，付款人账户余额300扣除1000后为-700，违反balance >= -500的约束，触发器会抛出异常并取消交易记录插入，同时UPDATE操作自动回滚，账户余额保持不变。

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