From 22690719ea5a89ccbcd04deb58c83d8d5f138df8 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sun, 27 Feb 2011 17:09:56 +0200 Subject: PL/Python explicit subtransactions MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Adds a context manager, obtainable by plpy.subtransaction(), to run a group of statements in a subtransaction. Jan Urbański, reviewed by Steve Singer, additional scribbling by me --- doc/src/sgml/plpython.sgml | 132 ++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 131 insertions(+), 1 deletion(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index 94d42bfaaa1..73203e62512 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -955,7 +955,7 @@ $$ LANGUAGE plpythonu; - + Trapping Errors @@ -981,6 +981,136 @@ $$ LANGUAGE plpythonu; + + Explicit Subtransactions + + + 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/Python offers a solution to this problem in + the form of explicit subtransactions. + + + + Subtransaction Context Managers + + + 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.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, 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 occurrs 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. + + + + + Older Python Versions + + + 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: + +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.args +else: + result = "funds transferred correctly" + +plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"]) +plpy.execute(plan, [result]) +$$ LANGUAGE plpythonu; + + + + + + 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. + + + + + Utility Functions -- cgit v1.2.3