Postgresql 中文操作指南

44.9. Explicit Subtransactions in PL/Tcl #

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

Recovering from errors caused by database access as described in Section 44.8 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/Tcl offers a solution to this problem in the form of explicit subtransactions.

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

Consider a function that implements a transfer between two accounts:

CREATE FUNCTION transfer_funds() RETURNS void AS $$
    if [catch {
        spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
        spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
    } errormsg] {
        set result [format "error transferring funds: %s" $errormsg]
    } else {
        set result "funds transferred successfully"
    }
    spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;

如果第二个_UPDATE_ 语句导致异常被引发，此函数将记录失败，但第一个_UPDATE_ 的结果仍将被提交。换句话说，资金将从 Joe 的帐户中提取，但不会转入 Mary 的帐户。之所以发生这种情况，是因为每个_spi_exec_ 都是一个单独的子事务，且仅回滚了其中一个子事务。

If the second UPDATE statement results in an exception being raised, this function will log the failure, 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. This happens because each spi_exec is a separate subtransaction, and only one of those subtransactions got rolled back.

若要处理此类情况，您可以将多个数据库操作包装在一个显示子事务中，该子事务将整体成功或回滚。PL/Tcl 提供了_subtransaction_ 命令来管理此问题。我们可以这样重写我们的函数：

To handle such cases, you can wrap multiple database operations in an explicit subtransaction, which will succeed or roll back as a whole. PL/Tcl provides a subtransaction command to manage this. We can rewrite our function as:

CREATE FUNCTION transfer_funds2() RETURNS void AS $$
    if [catch {
        subtransaction {
            spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
            spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
        }
    } errormsg] {
        set result [format "error transferring funds: %s" $errormsg]
    } else {
        set result "funds transferred successfully"
    }
    spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;

请注意，此目的仍然要求使用_catch_。否则错误将传播到函数的顶层，从而阻止将数据插入到_operations_ 表中。subtransaction 命令不会捕获错误，它仅确保在报告错误时在它的作用域内执行的所有数据库操作都将被一同回滚。

Note that use of catch is still required for this purpose. Otherwise the error would propagate to the top level of the function, preventing the desired insertion into the operations table. The subtransaction command does not trap errors, it only assures that all database operations executed inside its scope will be rolled back together when an error is reported.

对显示子事务的回滚发生在所含 Tcl 代码报告的任何错误上，而不仅仅是源自数据库访问的错误。因此，在_subtransaction_ 命令内引发的常规 Tcl 异常也会导致子事务被回滚。但是，Tcl 代码中的非错误退出（例如，由于_return_）不会导致回滚。

A rollback of an explicit subtransaction occurs on any error reported by the contained Tcl code, not only errors originating from database access. Thus a regular Tcl exception raised inside a subtransaction command will also cause the subtransaction to be rolled back. However, non-error exits out of the contained Tcl code (for instance, due to return) do not cause a rollback.