PostgreSQL跨银行转账交易无效终止错误的排查与解决

我需要编写PostgreSQL代码模拟银行1客户向银行2客户转账的交易，但遇到了无法理解的无效交易终止错误。要求实现的SQL转账逻辑需要处理可能出现的错误并维护数据完整性，以下是我的原代码：

--The two following commands could be grouped but, for lisibility, are not
DROP TABLE IF EXISTS bank1_clients, bank1_accounts, bank1_transactions;
DROP TABLE IF EXISTS bank2_clients, bank2_accounts, bank2_transactions;


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

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

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


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

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

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


INSERT INTO bank1_clients VALUES (10, 'Client 1');
INSERT INTO bank1_clients VALUES (14, 'Client 2');

INSERT INTO bank1_accounts VALUES (8, 2000, 14);
INSERT INTO bank1_accounts VALUES (12, 650, 10);

INSERT INTO bank2_clients VALUES (25, 'Client 3');

INSERT INTO bank2_accounts VALUES (2, 300, 25);


BEGIN;
DO $$
DECLARE
    payer integer := 10; --payer referenced by bank1
    recipient integer := 25; --recipient referenced by bank2
    amount float := 10000; --amount to transfer
BEGIN
    -- Attempt to execute a query that might fail
    UPDATE bank1_accounts
        SET balance = balance - amount
        WHERE client = payer;
    UPDATE bank2_accounts
        SET balance = balance + amount
        WHERE client = recipient;
    INSERT INTO bank1_transactions (amount) VALUES (amount);
    INSERT INTO bank2_transactions (amount) VALUES (amount);
EXCEPTION WHEN others THEN
    RAISE NOTICE 'An error occurred: %', SQLERRM;
    ROLLBACK;
END;
$$ LANGUAGE plpgsql;
COMMIT;

问题分析 #

原代码出现“无效交易终止”错误的核心原因是事务嵌套与冲突：

1. 外部已开启BEGIN事务，PL/pgSQL块内EXCEPTION中的ROLLBACK会直接终止整个外部事务，后续的COMMIT调用时事务已不存在，因此触发错误。
2. 使用float存储金额会导致精度丢失，不符合金融场景的计算要求。
3. 未提前检查付款人余额，依赖CHECK约束触发错误，不利于业务逻辑的清晰性。

修正后的代码 #

-- 删除现有表
DROP TABLE IF EXISTS bank1_clients, bank1_accounts, bank1_transactions;
DROP TABLE IF EXISTS bank2_clients, bank2_accounts, bank2_transactions;

-- 创建银行1表结构
CREATE TABLE IF NOT EXISTS bank1_clients
(
    id int PRIMARY KEY,
    name varchar
);

CREATE TABLE IF NOT EXISTS bank1_accounts
(
    id int PRIMARY KEY,
    balance numeric(15,2), -- 改用numeric避免金额精度丢失
    client int,
    FOREIGN KEY (client) REFERENCES bank1_clients(id),
    CHECK (balance >= -500)
);

CREATE TABLE IF NOT EXISTS bank1_transactions
(
    id serial PRIMARY KEY,
    amount numeric(15,2),
    transaction_time timestamp DEFAULT CURRENT_TIMESTAMP -- 新增交易时间字段
);

-- 创建银行2表结构
CREATE TABLE IF NOT EXISTS bank2_clients
(
    id int PRIMARY KEY,
    name varchar
);

CREATE TABLE IF NOT EXISTS bank2_accounts
(
    id int PRIMARY KEY,
    balance numeric(15,2),
    client int,
    FOREIGN KEY (client) REFERENCES bank2_clients(id),
    CHECK (balance >= -500)
);

CREATE TABLE IF NOT EXISTS bank2_transactions
(
    id serial PRIMARY KEY,
    amount numeric(15,2),
    transaction_time timestamp DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO bank1_clients VALUES (10, '客户1');
INSERT INTO bank1_clients VALUES (14, '客户2');

INSERT INTO bank1_accounts VALUES (8, 2000.00, 14);
INSERT INTO bank1_accounts VALUES (12, 650.00, 10);

INSERT INTO bank2_clients VALUES (25, '客户3');

INSERT INTO bank2_accounts VALUES (2, 300.00, 25);

-- 转账逻辑处理
DO $$
DECLARE
    payer integer := 10; -- 银行1付款客户ID
    recipient integer := 25; -- 银行2收款客户ID
    amount numeric(15,2) := 10000.00; -- 转账金额
    payer_balance numeric(15,2);
BEGIN
    -- 提前检查付款人账户余额是否满足转账要求（含透支额度）
    SELECT balance INTO payer_balance
    FROM bank1_accounts
    WHERE client = payer;

    IF payer_balance - amount < -500 THEN
        RAISE EXCEPTION '账户余额不足，无法完成转账';
    END IF;

    -- 执行转账操作
    UPDATE bank1_accounts
        SET balance = balance - amount
        WHERE client = payer;

    UPDATE bank2_accounts
        SET balance = balance + amount
        WHERE client = recipient;

    -- 记录交易日志（付款方记负金额，更符合财务逻辑）
    INSERT INTO bank1_transactions (amount) VALUES (-amount);
    INSERT INTO bank2_transactions (amount) VALUES (amount);

    RAISE NOTICE '转账成功完成';
EXCEPTION WHEN others THEN
    RAISE NOTICE '转账失败: %', SQLERRM;
    -- 仅当当前存在有效事务时执行回滚
    IF CURRENT_TRANSACTION_ID() IS NOT NULL THEN
        ROLLBACK;
    END IF;
END;
$$ LANGUAGE plpgsql;

关键修正点 #

• 移除外部的BEGIN/COMMIT，避免事务嵌套冲突，由PL/pgSQL块统一处理事务生命周期。
• 将float替换为numeric(15,2)，确保金额计算的精度准确。
• 新增转账前的余额检查，提前拦截不符合条件的转账请求，业务逻辑更清晰。
• 调整交易日志的金额记录规则，付款方交易金额记为负数，符合财务记账习惯。
• 增加事务状态判断，避免在无有效事务时执行ROLLBACK操作。

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