From b58fd4a9cab21e9d937a4e369bab31b3a5d24710 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 11 Mar 2017 14:37:05 -0500 Subject: Add a "subtransaction" command to PL/Tcl. This allows rolling back the effects of some SPI commands without having to fail the entire PL/Tcl function. Victor Wagner, reviewed by Pavel Stehule Discussion: https://postgr.es/m/20170108205750.2dab04a1@wagner.wagner.home --- doc/src/sgml/pltcl.sgml | 112 +++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 106 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index ad216dd5b75..ed745a74810 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -476,6 +476,20 @@ $$ LANGUAGE pltcl; + + subtransaction command + + + The Tcl script contained in command is + executed within a SQL subtransaction. If the script returns an + error, that entire subtransaction is rolled back before returning the + error out to the surrounding Tcl code. + See for more details and an + example. + + + + quote string @@ -844,18 +858,22 @@ CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE PROCEDURE tclsnit either by executing some invalid operation or by generating an error using the Tcl error command or PL/Tcl's elog command. Such errors can be caught - within Tcl using the Tcl catch command. If they - are not caught but are allowed to propagate out to the top level of - execution of the PL/Tcl function, they turn into database errors. + within Tcl using the Tcl catch command. If an + error is not caught but is allowed to propagate out to the top level of + execution of the PL/Tcl function, it is reported as a SQL error in the + function's calling query. - Conversely, database errors that occur within PL/Tcl's + Conversely, SQL errors that occur within PL/Tcl's spi_exec, spi_prepare, and spi_execp commands are reported as Tcl errors, so they are catchable by Tcl's catch command. - Again, if they propagate out to the top level without being caught, - they turn back into database errors. + (Each of these PL/Tcl commands runs its SQL operation in a + subtransaction, which is rolled back on error, so that any + partially-completed operation is automatically cleaned up.) + Again, if an error propagates out to the top level without being caught, + it turns back into a SQL error. @@ -902,6 +920,88 @@ if {[catch { spi_exec $sql_command }]} { + + Explicit Subtransactions in PL/Tcl + + + subtransactions + in PL/Tcl + + + + Recovering from errors caused by database access as described in + 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. + + + PL/Tcl Configuration -- cgit v1.2.3