1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>Explicit Subtransactions</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.3beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="PL/Python - Python Procedural Language"
16
HREF="plpython.html"><LINK
18
TITLE="Database Access"
19
HREF="plpython-database.html"><LINK
21
TITLE="Utility Functions"
22
HREF="plpython-util.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2013-05-06T21:00:50"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.3beta1 Documentation</A
56
TITLE="Database Access"
57
HREF="plpython-database.html"
74
>Chapter 43. PL/Python - Python Procedural Language</TD
80
TITLE="Utility Functions"
81
HREF="plpython-util.html"
95
NAME="PLPYTHON-SUBTRANSACTION"
96
>43.8. Explicit Subtransactions</A
99
> Recovering from errors caused by database access as described in
101
HREF="plpython-database.html#PLPYTHON-TRAPPING"
103
> can lead to an undesirable
104
situation where some operations succeed before one of them fails,
105
and after recovering from that error the data is left in an
106
inconsistent state. PL/Python offers a solution to this problem in
107
the form of explicit subtransactions.
115
>43.8.1. Subtransaction Context Managers</A
118
> Consider a function that implements a transfer between two
121
CLASS="PROGRAMLISTING"
122
>CREATE FUNCTION transfer_funds() RETURNS void AS $$
124
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
125
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
126
except plpy.SPIError, e:
127
result = "error transferring funds: %s" % e.args
129
result = "funds transferred correctly"
130
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
131
plpy.execute(plan, [result])
132
$$ LANGUAGE plpythonu;</PRE
137
> statement results in an
138
exception being raised, this function will report the error, but
139
the result of the first <TT
143
nevertheless be committed. In other words, the funds will be
144
withdrawn from Joe's account, but will not be transferred to
148
> To avoid such issues, you can wrap your
152
> calls in an explicit
153
subtransaction. The <TT
157
helper object to manage explicit subtransactions that gets created
160
>plpy.subtransaction()</TT
162
Objects created by this function implement the
164
HREF="http://docs.python.org/library/stdtypes.html#context-manager-types"
166
> context manager interface</A
167
>. Using explicit subtransactions
168
we can rewrite our function as:
170
CLASS="PROGRAMLISTING"
171
>CREATE FUNCTION transfer_funds2() RETURNS void AS $$
173
with plpy.subtransaction():
174
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
175
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
176
except plpy.SPIError, e:
177
result = "error transferring funds: %s" % e.args
179
result = "funds transferred correctly"
180
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
181
plpy.execute(plan, [result])
182
$$ LANGUAGE plpythonu;</PRE
184
Note that the use of <TT
188
required. Otherwise the exception would propagate to the top of
189
the Python stack and would cause the whole function to abort with
197
> table would not have any row
198
inserted into it. The subtransaction context manager does not
199
trap errors, it only assures that all database operations executed
200
inside its scope will be atomically committed or rolled back. A
201
rollback of the subtransaction block occurs on any kind of
202
exception exit, not only ones caused by errors originating from
203
database access. A regular Python exception raised inside an
204
explicit subtransaction block would also cause the subtransaction
214
>43.8.2. Older Python Versions</A
217
> Context managers syntax using the <TT
221
is available by default in Python 2.6. If using PL/Python with an
222
older Python version, it is still possible to use explicit
223
subtransactions, although not as transparently. You can call the
224
subtransaction manager's <TT
231
> functions using the
239
aliases. The example function that transfers funds could be
242
CLASS="PROGRAMLISTING"
243
>CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
245
subxact = plpy.subtransaction()
248
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
249
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
252
subxact.exit(*sys.exc_info())
255
subxact.exit(None, None, None)
256
except plpy.SPIError, e:
257
result = "error transferring funds: %s" % e.args
259
result = "funds transferred correctly"
261
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
262
plpy.execute(plan, [result])
263
$$ LANGUAGE plpythonu;</PRE
273
> Although context managers were implemented in Python 2.5, to use
277
> syntax in that version you need to
279
HREF="http://docs.python.org/release/2.5/ref/future.html"
283
>. Because of implementation details, however,
284
you cannot use future statements in PL/Python functions.
295
SUMMARY="Footer navigation table"
306
HREF="plpython-database.html"
324
HREF="plpython-util.html"
348
>Utility Functions</TD
b'\\ No newline at end of file'