Postgresql 中文操作指南

46.7. Explicit Subtransactions #

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

Recovering from errors caused by database access as described in Section 46.6.2 can lead to an undesirable situation where some operations succeed before one of them fails, and after recovering from that error the data is left in an inconsistent state. PL/Python offers a solution to this problem in the form of explicit subtransactions.

46.7.1. Subtransaction Context Managers #

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

Consider a function that implements a transfer between two accounts:

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 的帐户中。

If the second UPDATE statement results in an exception being raised, this function will report the error, but the result of the first UPDATE will nevertheless be committed. In other words, the funds will be withdrawn from Joe’s account, but will not be transferred to Mary’s account.

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

To avoid such issues, you can wrap your plpy.execute calls in an explicit subtransaction. The plpy module provides a helper object to manage explicit subtransactions that gets created with the plpy.subtransaction() function. Objects created by this function implement the context manager interface. Using explicit subtransactions we can rewrite our function as:

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 异常也将导致子事务回滚。

Note that the use of try/except is still required. Otherwise the exception would propagate to the top of the Python stack and would cause the whole function to abort with a PostgreSQL error, so that the operations table would not have any row inserted into it. The subtransaction context manager does not trap errors, it only assures that all database operations executed inside its scope will be atomically committed or rolled back. A rollback of the subtransaction block occurs on any kind of exception exit, not only ones caused by errors originating from database access. A regular Python exception raised inside an explicit subtransaction block would also cause the subtransaction to be rolled back.