On 11-02-06 11:40 AM, Jan Urbański wrote:
PFA an updated patch with documentation.
Yeah, changed them.
Those changes look fine. The tests now pass.
I've attached a new version of the patch that fixes a few typos/wording
issues I saw in the documentation. I also changed the link to the
python reference manual section on context managers. I think it is
better to link to that versus the original PEP.
The documentation could probably still use more word-smithing but that
can happen later. I'm marking this as ready for a committer.
Thanks,
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..9137ceb 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>
*************** $$ LANGUAGE plpythonu;
*** 969,974 ****
--- 969,1087 ----
</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 the <literal>plpy.subtransaction()</literal> function.
+ Objects created by this function implement the <ulink url="http://www.python.org/doc//current/library/stdtypes.html#context-manager-types";>context manager interface</ulink>.
+
+ Using explicit subtransactions 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 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.
+ </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 improve the
+ performance of your function.
+ </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>
+
<sect1 id="plpython-util">
<title>Utility Functions</title>
<para>
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
