嵌套事务实战案例

今天我们的任务是写一个函数，它要：

1. 检查客户余额。在扣款之前，必须先看看钱够不够。
2. 从余额里扣钱。如果钱够，就扣款。
3. 记录成功和失败的操作。所有操作都要写进日志表，方便以后分析。

这可不是那种无聊的减法函数。我们会用嵌套事务，如果出错（比如钱不够或者写日志时出错），就能回滚。你会发现SAVEPOINT的好处，还能学会让过程更抗错。

创建初始表

在写函数之前，先准备下数据库。我们需要三张表：

1. clients — 存客户和他们余额的数据。
2. payments — 记录成功的交易。
3. logs — 存所有支付尝试的信息（不管成功还是失败）。

-- 客户表
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- 成功支付表
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- 日志表
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

给clients表插点测试数据

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

现在我们有三个客户：Otto有100，Maria有50，Anna有0。

实现业务逻辑：PROCEDURE vs FUNCTION

简单说：

• 如果业务操作是“要么全成要么全不成”，用函数就够了。
• 如果要分阶段控制事务、部分提交、回滚、记录错误日志——用过程（CREATE PROCEDURE）。

为啥不用函数？因为在PostgreSQL 17里，函数里你不能用COMMIT、SAVEPOINT、ROLLBACK。所有改动都是在外部事务里一次性完成的。

只有过程（CREATE PROCEDURE ... LANGUAGE plpgsql）才能用SAVEPOINT、COMMIT、ROLLBACK——不过有些限制：

• 过程里允许SAVEPOINT、COMMIT、RELEASE SAVEPOINT。
• ROLLBACK TO SAVEPOINT在PL/pgSQL里禁用（会报错），要用BEGIN ... EXCEPTION ... END块来实现“虚拟savepoint”。

部分回滚的主要技巧：

BEGIN
    -- 你的代码
EXCEPTION
    WHEN OTHERS THEN
        -- 这个块出错时会回滚里面的所有改动！
        -- 可以在日志里记一下：
        INSERT INTO logs (...) VALUES (...);
END;

写一个带部分回滚和日志的支付过程

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- 查客户余额
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '客户没找到，操作被拒绝');
        RAISE EXCEPTION 'ID为%的客户没找到', in_client_id;
    END IF;

    -- 检查钱够不够
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '余额不足，扣款 ' || in_payment_amount || ' 元失败');
        -- 结束过程
        RETURN;
    END IF;

    -- 原子操作块，出错就回滚（虚拟savepoint）
    BEGIN
        -- 扣钱
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- 记录成功支付
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- 日志记录成功
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '成功扣款 ' || in_payment_amount || ' 元');

    EXCEPTION
        WHEN OTHERS THEN
            -- 这个块里的所有改动都会被撤销
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, '支付时出错: ' || SQLERRM);
            -- （不用显式ROLLBACK TO SAVEPOINT——禁用也不需要）
    END;
END;
$$;

简单说下流程：

• 钱不够/客户不存在——写日志然后退出。
• 所有关键代码都放在BEGIN ... EXCEPTION ... END块里。
• 只要这个块里出错，所有改动都会自动回滚；错误写进日志。
• 没有直接用SAVEPOINT和ROLLBACK TO SAVEPOINT——这就是PL/pgSQL的风格，只能靠EXCEPTION块。

调用过程

注意：要用CALL ...命令调用过程，而且外部数据库连接要开autocommit或者别在大事务里！

CALL process_payment(1, 30.00);   -- 成功支付
CALL process_payment(2, 100.00);  -- 余额不足
CALL process_payment(999, 50.00); -- 没有这个客户

检查结果

• 客户余额只在支付成功时才变。
• payments表——只有成功扣款才有记录。
• logs——所有尝试（包括错误）都有记录。

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

实际应用

处理交易的过程是fintech、电商甚至游戏平台系统的核心之一。想象一下网店要管理礼品卡余额并在购物时扣款，或者银行系统每秒成千上万的操作。

这些知识在实际开发里很有用，能帮你保护客户数据，避免支付处理时出大乱子。