Recovering from errors caused by database access as described in Section 43.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 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.
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;
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.
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.