Recovering from errors caused by database access as described in 45.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.
Consider a function that implements a transfer between two accounts:
CREATE FUNCTION transfer_funds() RETURNS void AS $$ try: plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'") plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'") except plpy.SPIError as e: result = "error transferring funds: %s" % e.args else: result = "funds transferred correctly" plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"]) plpy.execute(plan, [result]) $$ LANGUAGE plpythonu;
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:
CREATE FUNCTION transfer_funds2() RETURNS void AS $$ try: with plpy.subtransaction(): plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'") plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'") except plpy.SPIError as e: result = "error transferring funds: %s" % e.args else: result = "funds transferred correctly" plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"]) plpy.execute(plan, [result]) $$ LANGUAGE plpythonu;
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. For compatibility with
older Python versions, 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:
CREATE FUNCTION transfer_funds_old() RETURNS void AS $$ try: subxact = plpy.subtransaction() subxact.enter() try: plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'") plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'") except: import sys subxact.exit(*sys.exc_info()) raise else: subxact.exit(None, None, None) except plpy.SPIError as e: result = "error transferring funds: %s" % e.args else: result = "funds transferred correctly" plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"]) plpy.execute(plan, [result]) $$ LANGUAGE plpythonu;