Postgresql 中文操作指南

46.7. Explicit Subtransactions #

如 Section 46.6.2 所述，从数据库访问导致的错误中恢复可能会导致一些操作在其中一个操作失败之前成功的情况，并且在从该错误中恢复后，数据会处于不一致的状态。 PL/Python 通过显式子事务的形式为这个问题提供了解决方案。

46.7.1. Subtransaction Context Managers #

考虑一个在两个帐户之间实现转账的函数：

CREATE FUNCTION transfer_funds() RETURNS void AS $$
try:
    plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
    plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError as e:
    result = "error transferring funds: %s" % e.args
else:
    result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpython3u;

如果第二个 UPDATE 语句导致引发异常，此函数将报告错误，但第一个 UPDATE 结果仍然将提交。换句话说，资金将从 Joe 的帐户中取出，但不会转移到 Mary 的帐户中。

为了避免此类问题，您可以将您的 plpy.execute_调用包装在显式子事务中。 _plpy 模块提供了一个帮助对象来管理显式子事务，该子事务使用 _plpy.subtransaction()_函数创建。此函数创建的对象实现了 context manager interface。通过使用显式子事务，我们可以将函数重写为：

CREATE FUNCTION transfer_funds2() RETURNS void AS $$
try:
    with plpy.subtransaction():
        plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
        plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError as e:
    result = "error transferring funds: %s" % e.args
else:
    result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpython3u;

请注意，仍然需要使用 try/except。否则，异常将传播到 Python 堆栈的顶部，并导致整个函数终止并出现 PostgreSQL 错误，这样 operations 表中将没有插入任何行。子事务上下文管理器不会捕获错误，它只确保在其范围内执行的所有数据库操作都会原子地提交或回滚。子事务块的回滚发生在任何类型的异常退出时，而不仅仅是因数据库访问错误导致的异常退出。在显式子事务块中引发的常规 Python 异常也将导致子事务回滚。