On 11/02/11 17:22, Steve Singer wrote:
> On 11-02-10 05:20 AM, Jan Urbański wrote:
>>
>> D'oh, I was thinking about whether it's safe to skip the internal
>> subxact if you're in an implicit one and somehow I always convinced
>> myself that since you eventually close the explicit one, it is.
>>
>> Obviously my testing wasn't enough :( Attaching an updated patch with
>> improved docs incorporating Steve's fixes, and fixes & tests for not
>> statring the implicit subxact. That actually makes the patch a bit
>> smaller ;) OTOH I had to remove the section from the docs that claimed
>> performance improvement due to only starting the explicit subxact...
>>
>
> This version of the patch looks fine to me and seems to work as expected.
Thanks,
attached is a version merged with master.
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..87be8c2 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;
*** 968,973 ****
--- 968,1089 ----
</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 the <literal>plpy.subtransaction()</literal> function.
+ Objects created by this function implement the
+ <ulink url="http://docs.python.org/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 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>
+ </sect2>
+
+ <sect2>
+ <title>Older Python versions</title>
+
+ <para>
+ Context managers syntax using the <literal>with</literal> keyword is
+ available by default in Python 2.6. If using PL/Python with an older
+ Python version, it is still possible to use explicit subtransactions,
+ although not as transparently. You can call the subtransaction
+ manager's <literal>__enter__</literal> and <literal>__exit__</literal>
+ functions using the <literal>enter</literal> and <literal>exit</literal>
+ convenience aliases, and the example function that transfers funds could
+ 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>
+ <note>
+ <para>
+ Although context managers were implemented in Python 2.5, to use
+ the <literal>with</literal> syntax in that version you need to use
+ a <ulink url="http://docs.python.org/reference/simple_stmts.html#future";>future
+ statement</ulink>. Unfortunately, due to implementation details you cannot
+ use future statements in PL/Python functions.
+ </para>
+ </note>
+ </para>
+ </sect2>
+ </sect1>
<sect1 id="plpython-util">
<title>Utility Functions</title>
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 ...7a478fc .
*** a/src/pl/plpython/expected/plpython_subxact.out
--- b/src/pl/plpython/expected/plpython_subxact.out
***************
*** 0 ****
--- 1,380 ----
+ -- 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)
+
+ /* try/catch inside a subtransaction block */
+ CREATE FUNCTION try_catch_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values ('a')")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+ SELECT try_catch_inside_subxact();
+ NOTICE: caught
+ CONTEXT: PL/Python function "try_catch_inside_subxact"
+ try_catch_inside_subxact
+ --------------------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ (1 row)
+
+ TRUNCATE subxact_tbl;
+ ALTER TABLE subxact_tbl ADD PRIMARY KEY (i);
+ NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "subxact_tbl_pkey" for table "subxact_tbl"
+ CREATE FUNCTION pk_violation_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+ SELECT pk_violation_inside_subxact();
+ NOTICE: caught
+ CONTEXT: PL/Python function "pk_violation_inside_subxact"
+ pk_violation_inside_subxact
+ -----------------------------
+
+ (1 row)
+
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ 1
+ (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 ...7d8d11b .
*** a/src/pl/plpython/expected/plpython_subxact_0.out
--- b/src/pl/plpython/expected/plpython_subxact_0.out
***************
*** 0 ****
--- 1,368 ----
+ -- 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)
+
+ /* try/catch inside a subtransaction block */
+ CREATE FUNCTION try_catch_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values ('a')")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "try_catch_inside_subxact"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT try_catch_inside_subxact();
+ ERROR: function try_catch_inside_subxact() does not exist
+ LINE 1: SELECT try_catch_inside_subxact();
+ ^
+ 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;
+ ALTER TABLE subxact_tbl ADD PRIMARY KEY (i);
+ NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "subxact_tbl_pkey" for table "subxact_tbl"
+ CREATE FUNCTION pk_violation_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+ ERROR: could not compile PL/Python function "pk_violation_inside_subxact"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT pk_violation_inside_subxact();
+ ERROR: function pk_violation_inside_subxact() does not exist
+ LINE 1: SELECT pk_violation_inside_subxact();
+ ^
+ 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)
+
+ 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..381e91c 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_execute_fetch_result(SPITupleTab
*** 3342,3347 ****
--- 3472,3619 ----
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 ****
--- 3714,3721 ----
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 ****
--- 3751,3758 ----
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 ...3b8d9f7 .
*** a/src/pl/plpython/sql/plpython_subxact.sql
--- b/src/pl/plpython/sql/plpython_subxact.sql
***************
*** 0 ****
--- 1,236 ----
+ -- 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();
+
+ /* try/catch inside a subtransaction block */
+ CREATE FUNCTION try_catch_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values ('a')")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+
+ SELECT try_catch_inside_subxact();
+ SELECT * FROM subxact_tbl;
+ TRUNCATE subxact_tbl;
+
+ ALTER TABLE subxact_tbl ADD PRIMARY KEY (i);
+
+ CREATE FUNCTION pk_violation_inside_subxact() RETURNS void
+ AS $$
+ with plpy.subtransaction():
+ plpy.execute("insert into subxact_tbl values (1)")
+ try:
+ plpy.execute("insert into subxact_tbl values (1)")
+ except plpy.SPIError:
+ plpy.notice("caught")
+ $$ LANGUAGE plpythonu;
+
+ SELECT pk_violation_inside_subxact();
+ SELECT * FROM subxact_tbl;
+ 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
