Movatterモバイル変換

45.8.1. Subtransaction Context Managers

45.8.2. Older Python Versions

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, e:    result = "error transferring funds: %s" % e.argselse:    result = "funds transferred correctly"plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])plpy.execute(plan, [result])$$ LANGUAGE plpythonu;

If the secondUPDATE statement results in an exception being raised, this function will report the error, 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.

To avoid such issues, you can wrap yourplpy.execute calls in an explicit subtransaction. Theplpy module provides a helper object to manage explicit subtransactions that gets created with theplpy.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, e:    result = "error transferring funds: %s" % e.argselse:    result = "funds transferred correctly"plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])plpy.execute(plan, [result])$$ LANGUAGE plpythonu;

Note that the use oftry/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 aPostgreSQL error, so that theoperations 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 thewith 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 theenter andexit 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, e:    result = "error transferring funds: %s" % e.argselse:    result = "funds transferred correctly"plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])plpy.execute(plan, [result])$$ LANGUAGE plpythonu;