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

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.

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:

Note that the use of try/catch 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.

Context managers syntax using the with keyword is available by default in Python
2.6. If using PL/Python with an older Python version, it is
still possible to use explicit subtransactions, although not as
transparently. You can call the subtransaction manager's
__enter__ and __exit__ functions using the enter and exit
convenience aliases. The example function that transfers funds
could be written as:

Note: Although context managers were implemented
in Python 2.5, to use the with
syntax in that version you need to use a future statement. Because of
implementation details, however, you cannot use future
statements in PL/Python functions.