On 02/02/11 14:16, Steve Singer wrote:
> On 11-01-27 05:11 PM, Jan Urbański wrote:
>> On 23/12/10 15:32, Jan Urbański wrote:
>>> Here's a patch implementing explicitly starting subtransactions
>>> mentioned in
>>> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
>>> an incremental patch on top of the spi-in-subxacts patch sent eariler.
>>
>> Updated to the spi-in-subxacts version sent earlier.
>>
>
> Submission Review
> -----------------
>
> The patch applies against master.
> Test updates are included.
>
> The patch doesn't included any documentation updates. The author did
> mention that he'll do these if it looks like the patch is going to be
> accepted.
PFA an updated patch with documentation.
> The plpython_subxact regression test you addded is failing on both
> python3 and 2.4 for me. It seems to be creating functions with the same
> name twice and the second time is failing with "ERROR: function ....."
> already exists. I think this is just an issue with your expect files.
The expect files for older Pythons were broken by the change to include
HINT and DETAIL messages when errors are reported from Python. I fixed
them and now the regression tests are passing for me on Python 2.4, 2.6
and 3.1.
> Code Review
> ------------
>
>
> PLy_abort_open_subtransactions(...) line 1215:
>
> ereport(WARNING,
> (errmsg("Forcibly aborting a subtransaction "
> "that has not been exited")));
>
> "Forcibly" should be "forcibly" (lower case)
>
> Similarly in PLy_subxact_enter and PLy_subxact_exit a few
> PLy_exception_set calls start with an upper case character when I think
> we want it to be lower case.
Yeah, changed them.
Thanks,
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..9cd4879 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*************** $$ LANGUAGE plpythonu;
*** 943,949 ****
</sect2>
! <sect2>
<title>Trapping Errors</title>
<para>
--- 943,949 ----
</sect2>
! <sect2 id="plpython-trapping">
<title>Trapping Errors</title>
<para>
*************** CREATE FUNCTION try_adding_joe() RETURNS
*** 966,971 ****
--- 966,1084 ----
$$ LANGUAGE plpythonu;
</programlisting>
</para>
+ </sect2>
+ </sect1>
+
+ <sect1 id="plpython-subtransaction">
+ <title>Explicit subtransactions</title>
+ <para>
+ Recovering from errors caused by database access as described
+ in <xref linkend="plpython-trapping"> 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.
+ </para>
+
+ <sect2>
+ <title>Subtransaction context managers</title>
+ <para>
+ Consider a function that implements a transfer between two accounts:
+ <programlisting>
+ 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"
+ plpy.execute("INSERT INTO operations(result) VALUES ('%s')" % result)
+ $$ LANGUAGE plpythonu;
+ </programlisting>
+ If the second <literal>UPDATE</literal> statement results in an exception
+ being raised, this function will report the error, but the result of the
+ first <literal>UPDATE</literal> 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.
+ </para>
+ <para>
+ To avoid such issues, you can wrap your <literal>plpy.execute</literal>
+ calls in an explicit subtransaction. The <literal>plpy</literal> module
+ provides a helper object to manage explicit subtransactions that gets
+ created with <literal>plpy.subtransaction()</literal> function. Objects created
+ by this function implements the context manager interface as defined by
+ <ulink url="http://www.python.org/dev/peps/pep-0343/";>PEP 343</ulink>.
+ Using explicit subtransacrions we can rewrite our function as:
+ <programlisting>
+ 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"
+ plpy.execute("INSERT INTO operations(result) VALUES ('%s')" % result)
+ $$ LANGUAGE plpythonu;
+ </programlisting>
+ Note that the use of <literal>try/catch</literal> 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 <productname>PostgreSQL</productname> error.
+ The <literal>operations</literal> 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.
+ </para>
+ <para>
+ Another reason to use explicit subtransactions is that each
+ time <literal>plpy.execute</literal> or <literal>plpy.prepare</literal> is
+ used, it has to create its own internal subtransaction in order to be able
+ to recover from errors using the <literal>try/catch</literal> construct. If
+ you explicitly put your database access code in the scope of a
+ <literal>plpy.subtransaction()</literal> context manager, no additional
+ subtransactions will be created, which could have a positive effect on
+ performance.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Older Python versions</title>
+
+ <para>
+ Context managers syntax using the <literal>with</literal> keyword was
+ implemented 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. The example function that transfers funds would be
+ written as:
+ <programlisting>
+ 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"
+
+ plpy.execute("INSERT INTO operations(result) VALUES ('%s')" % result)
+ $$ LANGUAGE plpythonu;
+ </programlisting>
+ </para>
</sect2>
</sect1>
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 16d78ae..33dddc6 100644
*** a/src/pl/plpython/Makefile
--- b/src/pl/plpython/Makefile
*************** REGRESS = \
*** 79,84 ****
--- 79,85 ----
plpython_types \
plpython_error \
plpython_unicode \
+ plpython_subxact \
plpython_drop
# where to find psql for running the tests
PSQLDIR = $(bindir)
diff --git a/src/pl/plpython/expected/README b/src/pl/plpython/expected/README
index 27c995d..a16edce 100644
*** a/src/pl/plpython/expected/README
--- b/src/pl/plpython/expected/README
*************** plpython_unicode_2.out Python 2.2
*** 8,10 ****
--- 8,13 ----
plpython_unicode_3.out Python 2.3 through 3.1
plpython_types_3.out Python 3.1
+
+ plpython_subxact.out Python 2.6 through 3.1
+ plpython_subxact_0.out older Pythons that don't have the with statement
diff --git a/src/pl/plpython/expected/plpython_subxact.out b/src/pl/plpython/expected/plpython_subxact.out
index ...7508883 .
*** a/src/pl/plpython/expected/plpython_subxact.out
--- b/src/pl/plpython/expected/plpython_subxact.out
***************
*** 0 ****
--- 1,330 ----
+ -- test explicit subtransaction starting
+ /* Test table to see if transactions get properly rolled back
+ */
+ CREATE TABLE subxact_tbl (
+ i integer
+ );
+ /* Explicit case for Python <2.6
+ */
+ CREATE FUNCTION subxact_test(what_error text = NULL) RETURNS text
+ AS $$
+ import sys
+ subxact = plpy.subtransaction()
+ subxact.__enter__()
+ exc = True
+ try:
+ try:
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ except:
+ exc = False
+ subxact.__exit__(*sys.exc_info())
+ raise
+ finally:
+ if exc:
+ subxact.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_test();
+ subxact_test
+ --------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ (2 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('SPI');
+ ERROR: plpy.SPIError: invalid input syntax for integer: "oops"
+ LINE 1: insert into subxact_tbl values('oops')
+ ^
+ QUERY: insert into subxact_tbl values('oops')
+ CONTEXT: PL/Python function "subxact_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('Python');
+ ERROR: AttributeError: 'module' object has no attribute 'attribute_error'
+ CONTEXT: PL/Python function "subxact_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Context manager case for Python >=2.6
+ */
+ CREATE FUNCTION subxact_ctx_test(what_error text = NULL) RETURNS text
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_ctx_test();
+ subxact_ctx_test
+ ------------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ (2 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('SPI');
+ ERROR: plpy.SPIError: invalid input syntax for integer: "oops"
+ LINE 1: insert into subxact_tbl values('oops')
+ ^
+ QUERY: insert into subxact_tbl values('oops')
+ CONTEXT: PL/Python function "subxact_ctx_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('Python');
+ ERROR: AttributeError: 'module' object has no attribute 'attribute_error'
+ CONTEXT: PL/Python function "subxact_ctx_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Nested subtransactions
+ */
+ CREATE FUNCTION subxact_nested_test(swallow boolean = 'f') RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(3)")
+ plpy.execute("error")
+ except plpy.SPIError, e:
+ if not swallow:
+ raise
+ plpy.notice("Swallowed %r" % e)
+ return "ok"
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_nested_test();
+ ERROR: plpy.SPIError: syntax error at or near "error"
+ LINE 1: error
+ ^
+ QUERY: error
+ CONTEXT: PL/Python function "subxact_nested_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_nested_test('t');
+ NOTICE: Swallowed SPIError('syntax error at or near "error"',)
+ CONTEXT: PL/Python function "subxact_nested_test"
+ subxact_nested_test
+ ---------------------
+ ok
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ (2 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Nested subtransactions that recursively call code dealing with
+ subtransactions */
+ CREATE FUNCTION subxact_deeply_nested_test() RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ plpy.execute("select subxact_nested_test('t')")
+ return "ok"
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_deeply_nested_test();
+ NOTICE: Swallowed SPIError('syntax error at or near "error"',)
+ CONTEXT: PL/Python function "subxact_nested_test"
+ SQL statement "select subxact_nested_test('t')"
+ PL/Python function "subxact_nested_test"
+ subxact_deeply_nested_test
+ ----------------------------
+ ok
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ 1
+ 2
+ (4 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Error conditions from not opening/closing subtransactions */
+ CREATE FUNCTION subxact_exit_without_enter() RETURNS void
+ AS $$
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__exit__(None, None, None)
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ /* No warnings here, as the subxact gets indeed closed */
+ CREATE FUNCTION subxact_enter_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_exit_without_enter();
+ ERROR: ValueError: this subtransaction has not been entered
+ CONTEXT: PL/Python function "subxact_exit_without_enter"
+ SELECT subxact_enter_without_exit();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_without_exit"
+ subxact_enter_without_exit
+ ----------------------------
+
+ (1 row)
+
+ SELECT subxact_exit_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_exit_twice"
+ ERROR: ValueError: this subtransaction has not been entered
+ CONTEXT: PL/Python function "subxact_exit_twice"
+ SELECT subxact_enter_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_twice"
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_twice"
+ subxact_enter_twice
+ ---------------------
+
+ (1 row)
+
+ SELECT subxact_exit_same_subxact_twice();
+ ERROR: ValueError: this subtransaction has already been exited
+ CONTEXT: PL/Python function "subxact_exit_same_subxact_twice"
+ SELECT subxact_enter_same_subxact_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_same_subxact_twice"
+ ERROR: ValueError: this subtransaction has already been entered
+ CONTEXT: PL/Python function "subxact_enter_same_subxact_twice"
+ SELECT subxact_enter_subxact_in_with();
+ ERROR: ValueError: this subtransaction has already been entered
+ CONTEXT: PL/Python function "subxact_enter_subxact_in_with"
+ SELECT subxact_exit_subxact_in_with();
+ ERROR: ValueError: this subtransaction has already been exited
+ CONTEXT: PL/Python function "subxact_exit_subxact_in_with"
+ /* Make sure we don't get a "current transaction is aborted" error */
+ SELECT 1 as test;
+ test
+ ------
+ 1
+ (1 row)
+
+ /* Mix explicit subtransactions and normal SPI calls */
+ CREATE FUNCTION subxact_mix_explicit_and_implicit() RETURNS void
+ AS $$
+ p = plpy.prepare("insert into subxact_tbl values ($1)", ["integer"])
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error from an explicit subtransaction")
+
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error")
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_mix_explicit_and_implicit();
+ WARNING: Caught a SPI error from an explicit subtransaction
+ CONTEXT: PL/Python function "subxact_mix_explicit_and_implicit"
+ WARNING: Caught a SPI error
+ CONTEXT: PL/Python function "subxact_mix_explicit_and_implicit"
+ subxact_mix_explicit_and_implicit
+ -----------------------------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ (2 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Alternative method names for Python <2.6 */
+ CREATE FUNCTION subxact_alternate_names() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.enter()
+ s.exit(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_alternate_names();
+ subxact_alternate_names
+ -------------------------
+
+ (1 row)
+
+ DROP TABLE subxact_tbl;
diff --git a/src/pl/plpython/expected/plpython_subxact_0.out b/src/pl/plpython/expected/plpython_subxact_0.out
index ...73a9d90 .
*** a/src/pl/plpython/expected/plpython_subxact_0.out
--- b/src/pl/plpython/expected/plpython_subxact_0.out
***************
*** 0 ****
--- 1,322 ----
+ -- test explicit subtransaction starting
+ /* Test table to see if transactions get properly rolled back
+ */
+ CREATE TABLE subxact_tbl (
+ i integer
+ );
+ /* Explicit case for Python <2.6
+ */
+ CREATE FUNCTION subxact_test(what_error text = NULL) RETURNS text
+ AS $$
+ import sys
+ subxact = plpy.subtransaction()
+ subxact.__enter__()
+ exc = True
+ try:
+ try:
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ except:
+ exc = False
+ subxact.__exit__(*sys.exc_info())
+ raise
+ finally:
+ if exc:
+ subxact.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_test();
+ subxact_test
+ --------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ 2
+ (2 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('SPI');
+ ERROR: plpy.SPIError: invalid input syntax for integer: "oops"
+ LINE 1: insert into subxact_tbl values('oops')
+ ^
+ QUERY: insert into subxact_tbl values('oops')
+ CONTEXT: PL/Python function "subxact_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('Python');
+ ERROR: AttributeError: 'module' object has no attribute 'attribute_error'
+ CONTEXT: PL/Python function "subxact_test"
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Context manager case for Python >=2.6
+ */
+ CREATE FUNCTION subxact_ctx_test(what_error text = NULL) RETURNS text
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_ctx_test"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT subxact_ctx_test();
+ ERROR: function subxact_ctx_test() does not exist
+ LINE 1: SELECT subxact_ctx_test();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('SPI');
+ ERROR: function subxact_ctx_test(unknown) does not exist
+ LINE 1: SELECT subxact_ctx_test('SPI');
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('Python');
+ ERROR: function subxact_ctx_test(unknown) does not exist
+ LINE 1: SELECT subxact_ctx_test('Python');
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Nested subtransactions
+ */
+ CREATE FUNCTION subxact_nested_test(swallow boolean = 'f') RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(3)")
+ plpy.execute("error")
+ except plpy.SPIError, e:
+ if not swallow:
+ raise
+ plpy.notice("Swallowed %r" % e)
+ return "ok"
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_nested_test"
+ DETAIL: SyntaxError: invalid syntax (line 4)
+ SELECT subxact_nested_test();
+ ERROR: function subxact_nested_test() does not exist
+ LINE 1: SELECT subxact_nested_test();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_nested_test('t');
+ ERROR: function subxact_nested_test(unknown) does not exist
+ LINE 1: SELECT subxact_nested_test('t');
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Nested subtransactions that recursively call code dealing with
+ subtransactions */
+ CREATE FUNCTION subxact_deeply_nested_test() RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ plpy.execute("select subxact_nested_test('t')")
+ return "ok"
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_deeply_nested_test"
+ DETAIL: SyntaxError: invalid syntax (line 4)
+ SELECT subxact_deeply_nested_test();
+ ERROR: function subxact_deeply_nested_test() does not exist
+ LINE 1: SELECT subxact_deeply_nested_test();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Error conditions from not opening/closing subtransactions */
+ CREATE FUNCTION subxact_exit_without_enter() RETURNS void
+ AS $$
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__exit__(None, None, None)
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ /* No warnings here, as the subxact gets indeed closed */
+ CREATE FUNCTION subxact_enter_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_enter_subxact_in_with"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_exit_subxact_in_with"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT subxact_exit_without_enter();
+ ERROR: ValueError: this subtransaction has not been entered
+ CONTEXT: PL/Python function "subxact_exit_without_enter"
+ SELECT subxact_enter_without_exit();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_without_exit"
+ subxact_enter_without_exit
+ ----------------------------
+
+ (1 row)
+
+ SELECT subxact_exit_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_exit_twice"
+ ERROR: ValueError: this subtransaction has not been entered
+ CONTEXT: PL/Python function "subxact_exit_twice"
+ SELECT subxact_enter_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_twice"
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_twice"
+ subxact_enter_twice
+ ---------------------
+
+ (1 row)
+
+ SELECT subxact_exit_same_subxact_twice();
+ ERROR: ValueError: this subtransaction has already been exited
+ CONTEXT: PL/Python function "subxact_exit_same_subxact_twice"
+ SELECT subxact_enter_same_subxact_twice();
+ WARNING: forcibly aborting a subtransaction that has not been exited
+ CONTEXT: PL/Python function "subxact_enter_same_subxact_twice"
+ ERROR: ValueError: this subtransaction has already been entered
+ CONTEXT: PL/Python function "subxact_enter_same_subxact_twice"
+ SELECT subxact_enter_subxact_in_with();
+ ERROR: function subxact_enter_subxact_in_with() does not exist
+ LINE 1: SELECT subxact_enter_subxact_in_with();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT subxact_exit_subxact_in_with();
+ ERROR: function subxact_exit_subxact_in_with() does not exist
+ LINE 1: SELECT subxact_exit_subxact_in_with();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ /* Make sure we don't get a "current transaction is aborted" error */
+ SELECT 1 as test;
+ test
+ ------
+ 1
+ (1 row)
+
+ /* Mix explicit subtransactions and normal SPI calls */
+ CREATE FUNCTION subxact_mix_explicit_and_implicit() RETURNS void
+ AS $$
+ p = plpy.prepare("insert into subxact_tbl values ($1)", ["integer"])
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error from an explicit subtransaction")
+
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error")
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "subxact_mix_explicit_and_implicit"
+ DETAIL: SyntaxError: invalid syntax (line 5)
+ SELECT subxact_mix_explicit_and_implicit();
+ ERROR: function subxact_mix_explicit_and_implicit() does not exist
+ LINE 1: SELECT subxact_mix_explicit_and_implicit();
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ /* Alternative method names for Python <2.6 */
+ CREATE FUNCTION subxact_alternate_names() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.enter()
+ s.exit(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_alternate_names();
+ subxact_alternate_names
+ -------------------------
+
+ (1 row)
+
+ DROP TABLE subxact_tbl;
diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out
index d92c987..c7d875e 100644
*** a/src/pl/plpython/expected/plpython_test.out
--- b/src/pl/plpython/expected/plpython_test.out
*************** contents.sort()
*** 43,51 ****
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! module_contents
! -------------------------------------------------------------------------------------------
! Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
--- 43,51 ----
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! module_contents
! -----------------------------------------------------------------------------------------------------------
! Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, subtransaction, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index fff7de7..7692c0b 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef struct PLyProcedureEntry
*** 226,231 ****
--- 226,238 ----
PLyProcedure *proc;
} PLyProcedureEntry;
+ /* explicit subtransaction data */
+ typedef struct PLySubxactData
+ {
+ MemoryContext oldcontext;
+ ResourceOwner oldowner;
+ } PLySubxactData;
+
/* Python objects */
typedef struct PLyPlanObject
*************** typedef struct PLyResultObject
*** 247,252 ****
--- 254,265 ----
PyObject *status; /* query status, SPI_OK_*, or SPI_ERR_* */
} PLyResultObject;
+ typedef struct PLySubxactObject
+ {
+ PyObject_HEAD
+ bool started;
+ bool exited;
+ } PLySubxactObject;
/* function declarations */
*************** static HeapTuple PLyObject_ToTuple(PLyTy
*** 372,377 ****
--- 385,394 ----
*/
static PLyProcedure *PLy_curr_procedure = NULL;
+ /* A list of explicit subtransaction data */
+ static List *explicit_subtransactions = NIL;
+
+
static PyObject *PLy_interp_globals = NULL;
static PyObject *PLy_interp_safe_globals = NULL;
static HTAB *PLy_procedure_cache = NULL;
*************** static char PLy_result_doc[] = {
*** 391,396 ****
--- 408,417 ----
"Results of a PostgreSQL query"
};
+ static char PLy_subxact_doc[] = {
+ "PostgreSQL subtransaction context manager"
+ };
+
/*
* the function definitions
*************** PLy_function_handler(FunctionCallInfo fc
*** 1214,1227 ****
return rv;
}
static PyObject *
PLy_procedure_call(PLyProcedure *proc, char *kargs, PyObject *vargs)
{
! PyObject *rv;
PyDict_SetItemString(proc->globals, kargs, vargs);
! rv = PyEval_EvalCode((PyCodeObject *) proc->code,
! proc->globals, proc->globals);
/* If the Python code returned an error, propagate it */
if (rv == NULL)
--- 1235,1302 ----
return rv;
}
+ /*
+ * Abort lingering subtransactions that have been explicitly started by
+ * plpy.subtransaction().start() and not properly closed.
+ */
+ static void
+ PLy_abort_open_subtransactions(int save_subxact_level)
+ {
+ Assert(save_subxact_level >= 0);
+
+ while (list_length(explicit_subtransactions) > save_subxact_level)
+ {
+ PLySubxactData *subxactdata;
+
+ Assert(explicit_subtransactions != NIL);
+
+ ereport(WARNING,
+ (errmsg("forcibly aborting a subtransaction "
+ "that has not been exited")));
+ /* Abort the transaction that has not been closed */
+ RollbackAndReleaseCurrentSubTransaction();
+
+ SPI_restore_connection();
+
+ subxactdata = (PLySubxactData *) linitial(explicit_subtransactions);
+ explicit_subtransactions = list_delete_first(explicit_subtransactions);
+
+ MemoryContextSwitchTo(subxactdata->oldcontext);
+ CurrentResourceOwner = subxactdata->oldowner;
+ PLy_free(subxactdata);
+ }
+ }
+
static PyObject *
PLy_procedure_call(PLyProcedure *proc, char *kargs, PyObject *vargs)
{
! PyObject *rv;
! int volatile save_subxact_level = list_length(explicit_subtransactions);
PyDict_SetItemString(proc->globals, kargs, vargs);
! PG_TRY();
! {
! rv = PyEval_EvalCode((PyCodeObject *) proc->code,
! proc->globals, proc->globals);
! /*
! * since plpy will only let you close subxacts that you started, you
! * cannot *unnest* subtransactions, only *nest* them without closing
! */
! Assert(list_length(explicit_subtransactions) >= save_subxact_level);
! }
! PG_CATCH();
! {
! /* abort subtransactions that the called function forgot to close */
! PLy_abort_open_subtransactions(save_subxact_level);
! PG_RE_THROW();
! }
! PG_END_TRY();
!
! /*
! * abort subtransactions in case the function returned a valid object, but
! * forgot to close some explicitly opened subxacts
! */
! PLy_abort_open_subtransactions(save_subxact_level);
/* If the Python code returned an error, propagate it */
if (rv == NULL)
*************** static PyObject *PLy_spi_execute_query(c
*** 2558,2563 ****
--- 2633,2644 ----
static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long);
static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int);
+ static PyObject *PLy_subxact(PyObject *, PyObject *);
+ static PyObject *PLy_subxact_new(void);
+ static void PLy_subxact_dealloc(PyObject *);
+ static PyObject *PLy_subxact_enter(PyObject *, PyObject *);
+ static PyObject *PLy_subxact_exit(PyObject *, PyObject *);
+
static PyMethodDef PLy_plan_methods[] = {
{"status", PLy_plan_status, METH_VARARGS, NULL},
*************** static PyTypeObject PLy_ResultType = {
*** 2650,2655 ****
--- 2731,2780 ----
PLy_result_methods, /* tp_tpmethods */
};
+ static PyMethodDef PLy_subxact_methods[] = {
+ {"__enter__", PLy_subxact_enter, METH_VARARGS, NULL},
+ {"__exit__", PLy_subxact_exit, METH_VARARGS, NULL},
+ /* user-friendly names for Python <2.6 */
+ {"enter", PLy_subxact_enter, METH_VARARGS, NULL},
+ {"exit", PLy_subxact_exit, METH_VARARGS, NULL},
+ {NULL, NULL, 0, NULL}
+ };
+
+ static PyTypeObject PLy_SubxactType = {
+ PyVarObject_HEAD_INIT(NULL, 0)
+ "PLySubtransaction", /* tp_name */
+ sizeof(PLySubxactObject), /* tp_size */
+ 0, /* tp_itemsize */
+
+ /*
+ * methods
+ */
+ PLy_subxact_dealloc, /* tp_dealloc */
+ 0, /* tp_print */
+ 0, /* tp_getattr */
+ 0, /* tp_setattr */
+ 0, /* tp_compare */
+ 0, /* tp_repr */
+ 0, /* tp_as_number */
+ 0, /* tp_as_sequence */
+ 0, /* tp_as_mapping */
+ 0, /* tp_hash */
+ 0, /* tp_call */
+ 0, /* tp_str */
+ 0, /* tp_getattro */
+ 0, /* tp_setattro */
+ 0, /* tp_as_buffer */
+ Py_TPFLAGS_DEFAULT | Py_TPFLAGS_BASETYPE, /* tp_flags */
+ PLy_subxact_doc, /* tp_doc */
+ 0, /* tp_traverse */
+ 0, /* tp_clear */
+ 0, /* tp_richcompare */
+ 0, /* tp_weaklistoffset */
+ 0, /* tp_iter */
+ 0, /* tp_iternext */
+ PLy_subxact_methods, /* tp_tpmethods */
+ };
+
static PyMethodDef PLy_methods[] = {
/*
* logging methods
*************** static PyMethodDef PLy_methods[] = {
*** 2672,2677 ****
--- 2797,2807 ----
*/
{"execute", PLy_spi_execute, METH_VARARGS, NULL},
+ /*
+ * create the subtransaction context manager
+ */
+ {"subtransaction", PLy_subxact, METH_NOARGS, NULL},
+
{NULL, NULL, 0, NULL}
};
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 2883,2890 ****
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! BeginInternalSubTransaction(NULL);
! MemoryContextSwitchTo(oldcontext);
PG_TRY();
{
--- 3013,3024 ----
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! if (explicit_subtransactions == NIL)
! {
! /* Not in an explicit subtransaction */
! BeginInternalSubTransaction(NULL);
! MemoryContextSwitchTo(oldcontext);
! }
PG_TRY();
{
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 2966,2981 ****
elog(ERROR, "SPI_saveplan failed: %s",
SPI_result_code_string(SPI_result));
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
}
PG_CATCH();
{
--- 3100,3118 ----
elog(ERROR, "SPI_saveplan failed: %s",
SPI_result_code_string(SPI_result));
! if (explicit_subtransactions == NIL)
! {
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
! }
}
PG_CATCH();
{
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 2988,3004 ****
Py_DECREF(plan);
Py_XDECREF(optr);
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
--- 3125,3144 ----
Py_DECREF(plan);
Py_XDECREF(optr);
! if (explicit_subtransactions == NIL)
! {
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
! }
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3080,3088 ****
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! BeginInternalSubTransaction(NULL);
! /* Want to run inside function's memory context */
! MemoryContextSwitchTo(oldcontext);
PG_TRY();
{
--- 3220,3231 ----
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! if (explicit_subtransactions == NIL)
! {
! BeginInternalSubTransaction(NULL);
! /* Want to run inside function's memory context */
! MemoryContextSwitchTo(oldcontext);
! }
PG_TRY();
{
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3137,3152 ****
if (nargs > 0)
pfree(nulls);
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
}
PG_CATCH();
{
--- 3280,3298 ----
if (nargs > 0)
pfree(nulls);
! if (explicit_subtransactions == NIL)
! {
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
! }
}
PG_CATCH();
{
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3171,3187 ****
}
}
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
--- 3317,3336 ----
}
}
! if (explicit_subtransactions == NIL)
! {
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
! }
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
*************** PLy_spi_execute_query(char *query, long
*** 3221,3229 ****
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! BeginInternalSubTransaction(NULL);
! /* Want to run inside function's memory context */
! MemoryContextSwitchTo(oldcontext);
PG_TRY();
{
--- 3370,3385 ----
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
! if (explicit_subtransactions == NIL)
! {
! /*
! * Execute the query inside a sub-transaction, so we can cope with errors
! * sanely
! */
! BeginInternalSubTransaction(NULL);
! /* Want to run inside function's memory context */
! MemoryContextSwitchTo(oldcontext);
! }
PG_TRY();
{
*************** PLy_spi_execute_query(char *query, long
*** 3231,3246 ****
rv = SPI_execute(query, PLy_curr_procedure->fn_readonly, limit);
ret = PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv);
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
}
PG_CATCH();
{
--- 3387,3405 ----
rv = SPI_execute(query, PLy_curr_procedure->fn_readonly, limit);
ret = PLy_spi_execute_fetch_result(SPI_tuptable, SPI_processed, rv);
! if (explicit_subtransactions == NIL)
! {
! /* Commit the inner transaction, return to outer xact context */
! ReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * AtEOSubXact_SPI() should not have popped any SPI context, but just
! * in case it did, make sure we remain connected.
! */
! SPI_restore_connection();
! }
}
PG_CATCH();
{
*************** PLy_spi_execute_query(char *query, long
*** 3251,3267 ****
edata = CopyErrorData();
FlushErrorState();
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
--- 3410,3429 ----
edata = CopyErrorData();
FlushErrorState();
! if (explicit_subtransactions == NIL)
! {
! /* Abort the inner transaction */
! RollbackAndReleaseCurrentSubTransaction();
! MemoryContextSwitchTo(oldcontext);
! CurrentResourceOwner = oldowner;
! /*
! * If AtEOSubXact_SPI() popped any SPI context of the subxact, it
! * will have left us in a disconnected state. We need this hack to
! * return to connected state.
! */
! SPI_restore_connection();
! }
/* Make Python raise the exception */
PLy_spi_exception_set(edata);
*************** PLy_spi_execute_fetch_result(SPITupleTab
*** 3342,3347 ****
--- 3504,3651 ----
return (PyObject *) result;
}
+ /* s = plpy.subtransaction() */
+ static PyObject *
+ PLy_subxact(PyObject *self, PyObject *unused)
+ {
+ return PLy_subxact_new();
+ }
+
+ /* Allocate and initialize a PLySubxactObject */
+ static PyObject *
+ PLy_subxact_new(void)
+ {
+ PLySubxactObject *ob;
+
+ if ((ob = PyObject_New(PLySubxactObject, &PLy_SubxactType)) == NULL)
+ return NULL;
+
+ ob->started = false;
+ ob->exited = false;
+
+ return (PyObject *) ob;
+ }
+
+ /* Python requires a dealloc function to be defined */
+ static void
+ PLy_subxact_dealloc(PyObject *subxact) { };
+
+ /*
+ * subxact.__enter__() or subxact.enter()
+ *
+ * Start an explicit subtransaction. SPI calls within an explicit
+ * subtransaction will not start another one, so you can atomically execute
+ * many SPI calls and still get a controllable exception if one of them fails
+ */
+ static PyObject *
+ PLy_subxact_enter(PyObject *self, PyObject *unused)
+ {
+ PLySubxactData *subxactdata;
+ MemoryContext oldcontext;
+ PLySubxactObject *subxact = (PLySubxactObject *) self;
+
+ if (subxact->started)
+ {
+ PLy_exception_set(PyExc_ValueError, "this subtransaction has already been entered");
+ return NULL;
+ }
+
+ if (subxact->exited)
+ {
+ PLy_exception_set(PyExc_ValueError, "this subtransaction has already been exited");
+ return NULL;
+ }
+
+ subxact->started = true;
+ oldcontext = CurrentMemoryContext;
+
+ subxactdata = PLy_malloc(sizeof(PLySubxactData));
+ subxactdata->oldcontext = oldcontext;
+ subxactdata->oldowner = CurrentResourceOwner;
+
+ /* Enter a subtransaction */
+ BeginInternalSubTransaction(NULL);
+ /* Do not want to leave the previous memory context */
+ MemoryContextSwitchTo(oldcontext);
+
+ explicit_subtransactions = lcons(subxactdata, explicit_subtransactions);
+
+ Py_INCREF(self);
+ return self;
+ }
+
+ /*
+ * subxact.__exit__(exc_type, exc, tb) or subxact.exit(exc_type, exc, tb)
+ *
+ * Exit an explicit subtransaction. exc_type is an exception type, exc is the
+ * exception object, tb is the traceback. If exc_type is None, commit the
+ * subtransactiony, if not abort it.
+ *
+ * The method signature is chosen to allow subxact objects to be used as
+ * context managers as described in http://www.python.org/dev/peps/pep-0343/
+ */
+ static PyObject *
+ PLy_subxact_exit(PyObject *self, PyObject *args)
+ {
+ PyObject *type;
+ PyObject *value;
+ PyObject *traceback;
+ PLySubxactData *subxactdata;
+ PLySubxactObject *subxact = (PLySubxactObject *) self;
+
+ if (!PyArg_ParseTuple(args, "OOO", &type, &value, &traceback))
+ return NULL;
+
+ if (!subxact->started)
+ {
+ PLy_exception_set(PyExc_ValueError,
+ "this subtransaction has not been entered");
+ return NULL;
+ }
+
+ if (subxact->exited)
+ {
+ PLy_exception_set(PyExc_ValueError,
+ "this subtransaction has already been exited");
+ return NULL;
+ }
+
+ if (explicit_subtransactions == NIL)
+ {
+ PLy_exception_set(PyExc_ValueError,
+ "there is no subtransaction to be exited from");
+ return NULL;
+ }
+
+ subxact->exited = true;
+
+ if (type != Py_None)
+ {
+ /* Abort the inner transaction */
+ RollbackAndReleaseCurrentSubTransaction();
+ }
+ else
+ {
+ ReleaseCurrentSubTransaction();
+ }
+
+ subxactdata = (PLySubxactData *) linitial(explicit_subtransactions);
+ explicit_subtransactions = list_delete_first(explicit_subtransactions);
+
+ MemoryContextSwitchTo(subxactdata->oldcontext);
+ CurrentResourceOwner = subxactdata->oldowner;
+ PLy_free(subxactdata);
+
+ /*
+ * AtEOSubXact_SPI() should not have popped any SPI context, but just
+ * in case it did, make sure we remain connected.
+ */
+ SPI_restore_connection();
+
+ Py_INCREF(Py_None);
+ return Py_None;
+ }
+
/*
* language handler and interpreter initialization
*************** _PG_init(void)
*** 3442,3447 ****
--- 3746,3753 ----
PLy_trigger_cache = hash_create("PL/Python triggers", 32, &hash_ctl,
HASH_ELEM | HASH_FUNCTION);
+ explicit_subtransactions = NIL;
+
inited = true;
}
*************** PLy_init_plpy(void)
*** 3477,3482 ****
--- 3783,3790 ----
elog(ERROR, "could not initialize PLy_PlanType");
if (PyType_Ready(&PLy_ResultType) < 0)
elog(ERROR, "could not initialize PLy_ResultType");
+ if (PyType_Ready(&PLy_SubxactType) < 0)
+ elog(ERROR, "could not initialize PLy_SubxactType");
#if PY_MAJOR_VERSION >= 3
plpy = PyModule_Create(&PLy_module);
diff --git a/src/pl/plpython/sql/plpython_subxact.sql b/src/pl/plpython/sql/plpython_subxact.sql
index ...d45c8c7 .
*** a/src/pl/plpython/sql/plpython_subxact.sql
--- b/src/pl/plpython/sql/plpython_subxact.sql
***************
*** 0 ****
--- 1,207 ----
+ -- test explicit subtransaction starting
+
+ /* Test table to see if transactions get properly rolled back
+ */
+ CREATE TABLE subxact_tbl (
+ i integer
+ );
+
+ /* Explicit case for Python <2.6
+ */
+ CREATE FUNCTION subxact_test(what_error text = NULL) RETURNS text
+ AS $$
+ import sys
+ subxact = plpy.subtransaction()
+ subxact.__enter__()
+ exc = True
+ try:
+ try:
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ except:
+ exc = False
+ subxact.__exit__(*sys.exc_info())
+ raise
+ finally:
+ if exc:
+ subxact.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_test();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('SPI');
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('Python');
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ /* Context manager case for Python >=2.6
+ */
+ CREATE FUNCTION subxact_ctx_test(what_error text = NULL) RETURNS text
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(1)")
+ plpy.execute("insert into subxact_tbl values(2)")
+ if what_error == "SPI":
+ plpy.execute("insert into subxact_tbl values('oops')")
+ elif what_error == "Python":
+ plpy.attribute_error
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_ctx_test();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('SPI');
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('Python');
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ /* Nested subtransactions
+ */
+ CREATE FUNCTION subxact_nested_test(swallow boolean = 'f') RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(3)")
+ plpy.execute("error")
+ except plpy.SPIError, e:
+ if not swallow:
+ raise
+ plpy.notice("Swallowed %r" % e)
+ return "ok"
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_nested_test();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ SELECT subxact_nested_test('t');
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ /* Nested subtransactions that recursively call code dealing with
+ subtransactions */
+ CREATE FUNCTION subxact_deeply_nested_test() RETURNS text
+ AS $$
+ plpy.execute("insert into subxact_tbl values(1)")
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values(2)")
+ plpy.execute("select subxact_nested_test('t')")
+ return "ok"
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_deeply_nested_test();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ /* Error conditions from not opening/closing subtransactions */
+ CREATE FUNCTION subxact_exit_without_enter() RETURNS void
+ AS $$
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__exit__(None, None, None)
+ plpy.subtransaction().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subtransaction().__enter__()
+ plpy.subtransaction().__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_enter_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.__enter__()
+ s.__enter__()
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ /* No warnings here, as the subxact gets indeed closed */
+ CREATE FUNCTION subxact_enter_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subtransaction() as s:
+ s.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_exit_without_enter();
+ SELECT subxact_enter_without_exit();
+ SELECT subxact_exit_twice();
+ SELECT subxact_enter_twice();
+ SELECT subxact_exit_same_subxact_twice();
+ SELECT subxact_enter_same_subxact_twice();
+ SELECT subxact_enter_subxact_in_with();
+ SELECT subxact_exit_subxact_in_with();
+
+ /* Make sure we don't get a "current transaction is aborted" error */
+ SELECT 1 as test;
+
+ /* Mix explicit subtransactions and normal SPI calls */
+ CREATE FUNCTION subxact_mix_explicit_and_implicit() RETURNS void
+ AS $$
+ p = plpy.prepare("insert into subxact_tbl values ($1)", ["integer"])
+ try:
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error from an explicit subtransaction")
+
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ plpy.execute(p, [2])
+ plpy.execute(p, ["wrong"])
+ except plpy.SPIError:
+ plpy.warning("Caught a SPI error")
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_mix_explicit_and_implicit();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ /* Alternative method names for Python <2.6 */
+ CREATE FUNCTION subxact_alternate_names() RETURNS void
+ AS $$
+ s = plpy.subtransaction()
+ s.enter()
+ s.exit(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ SELECT subxact_alternate_names();
+
+ DROP TABLE subxact_tbl;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
