Movatterモバイル変換

Recovering from errors caused by database access as described inSection 42.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;

If the secondUPDATE statement results in an exception being raised, this function will log the failure, but the result of the firstUPDATE 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 eachspi_exec is a separate subtransaction, and only one of those subtransactions got rolled back.

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 asubtransaction 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;

Note that use ofcatch is still required for this purpose. Otherwise the error would propagate to the top level of the function, preventing the desired insertion into theoperations table. Thesubtransaction 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.

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 asubtransaction command will also cause the subtransaction to be rolled back. However, non-error exits out of the contained Tcl code (for instance, due toreturn) do not cause a rollback.