Here's a patch implementing explicitly starting subtransactions mentioned in
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
an incremental patch on top of the spi-in-subxacts patch sent eariler.
Git branch for this patch:
https://github.com/wulczer/postgres/tree/explicit-subxacts.
The idea has been proposed in
http://archives.postgresql.org/pgsql-hackers/2010-11/msg00122.php
This patch provides a subtransaction context manager, in the vein of
http://www.python.org/dev/peps/pep-0343/.
When inside an explicit subtransaction, SPI calls do not start another
one, so you pay the subxact start overhead only once, and you get atomic
behaviour.
For instance this:
with plpy.subxact():
plpy.execute("insert into t values (1)")
plpy.execute("insert into t values (2)")
plpy.execute("ooops")
will not insert any rows into t. Just so you realise it, it *will* raise
the exception from the last execute, if you want to continue execution
you need to put your with block in a try/catch. If the code starts a
subtransaction but fails to close it, PL/Python will forcibly roll it
back to leave the backend in a clean state.
The patch lacks user-facing documentation, I'll add that later if it
gets accepted. For more usage examples refer to the unit tests that the
patch adds.
Cheers,
Jan
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 f011528..362cc0d 100644
*** a/src/pl/plpython/expected/README
--- b/src/pl/plpython/expected/README
*************** plpython_unicode_2.out Python 2.2
*** 6,8 ****
--- 6,11 ----
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 ...25a5a4b .
*** a/src/pl/plpython/expected/plpython_subxact.out
--- b/src/pl/plpython/expected/plpython_subxact.out
***************
*** 0 ****
--- 1,321 ----
+ -- 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.subxact()
+ 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"
+ 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.subxact():
+ 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"
+ 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.subxact():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subxact():
+ 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"
+ 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.subxact():
+ 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.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__exit__(None, None, None)
+ plpy.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subxact()
+ 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.subxact()
+ 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.subxact() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subxact() 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.subxact():
+ 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.subxact()
+ 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 ...11e08d3 .
*** a/src/pl/plpython/expected/plpython_subxact_0.out
--- b/src/pl/plpython/expected/plpython_subxact_0.out
***************
*** 0 ****
--- 1,289 ----
+ -- 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.subxact()
+ 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"
+ 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.subxact():
+ 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();
+ ERROR: could not compile PL/Python function "subxact_ctx_test"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('SPI');
+ ERROR: could not compile PL/Python function "subxact_ctx_test"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('Python');
+ ERROR: could not compile PL/Python function "subxact_ctx_test"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ 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.subxact():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subxact():
+ 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: could not compile PL/Python function "subxact_nested_test"
+ DETAIL: SyntaxError: invalid syntax (line 4)
+ SELECT * FROM subxact_tbl;
+ i
+ ---
+ (0 rows)
+
+ TRUNCATE subxact_tbl;
+ SELECT subxact_nested_test('t');
+ ERROR: could not compile PL/Python function "subxact_nested_test"
+ DETAIL: SyntaxError: invalid syntax (line 4)
+ 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.subxact():
+ plpy.execute("insert into subxact_tbl values(2)")
+ plpy.execute("select subxact_nested_test('t')")
+ return "ok"
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_deeply_nested_test();
+ ERROR: could not compile PL/Python function "subxact_deeply_nested_test"
+ DETAIL: SyntaxError: invalid syntax (line 4)
+ 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.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__exit__(None, None, None)
+ plpy.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subxact()
+ 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.subxact()
+ 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.subxact() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subxact() 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: could not compile PL/Python function "subxact_enter_subxact_in_with"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ SELECT subxact_exit_subxact_in_with();
+ ERROR: could not compile PL/Python function "subxact_exit_subxact_in_with"
+ DETAIL: SyntaxError: invalid syntax (line 3)
+ /* 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.subxact():
+ 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();
+ ERROR: could not compile PL/Python function "subxact_mix_explicit_and_implicit"
+ DETAIL: SyntaxError: invalid syntax (line 5)
+ 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.subxact()
+ 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..674c739 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, subxact, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 5b216b2..bd5dc65 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef struct PLyProcedureEntry
*** 224,229 ****
--- 224,236 ----
PLyProcedure *proc;
} PLyProcedureEntry;
+ /* explicit subtransaction data */
+ typedef struct PLySubxactData
+ {
+ MemoryContext oldcontext;
+ ResourceOwner oldowner;
+ } PLySubxactData;
+
/* Python objects */
typedef struct PLyPlanObject
{
*************** typedef struct PLyResultObject
*** 244,249 ****
--- 251,262 ----
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
*** 362,367 ****
--- 375,382 ----
*/
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 char PLy_result_doc[] = {
*** 382,387 ****
--- 397,406 ----
"Results of a PostgreSQL query"
};
+ static char PLy_subxact_doc[] = {
+ "PostgreSQL subtransaction context manager"
+ };
+
/*
* the function definitions
*************** PLy_function_handler(FunctionCallInfo fc
*** 1167,1185 ****
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 there was an error in a PG callback, propagate that no matter what
! * Python claims about its success.
*/
/* if the Python code returned an error, propagate it */
if (rv == NULL)
--- 1186,1253 ----
return rv;
}
+ /*
+ * Abort lingering subtransactions that have been explicitly started by
+ * plpy.subxact().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
*** 2525,2530 ****
--- 2593,2604 ----
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 = {
*** 2617,2622 ****
--- 2691,2740 ----
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)
+ "PLySubxact", /* 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[] = {
*** 2639,2644 ****
--- 2757,2767 ----
*/
{"execute", PLy_spi_execute, METH_VARARGS, NULL},
+ /*
+ * create the subtransaction context manager
+ */
+ {"subxact", PLy_subxact, METH_NOARGS, NULL},
+
{NULL, NULL, 0, NULL}
};
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 2854,2861 ****
plan->values = PLy_malloc(sizeof(Datum) * nargs);
plan->args = PLy_malloc(sizeof(PLyTypeInfo) * nargs);
! BeginInternalSubTransaction(NULL);
! MemoryContextSwitchTo(oldcontext);
PG_TRY();
{
--- 2977,2988 ----
plan->values = PLy_malloc(sizeof(Datum) * nargs);
plan->args = PLy_malloc(sizeof(PLyTypeInfo) * nargs);
! if (explicit_subtransactions == NIL)
! {
! /* Not in an explicit subtransaction */
! BeginInternalSubTransaction(NULL);
! MemoryContextSwitchTo(oldcontext);
! }
PG_TRY();
{
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 2937,2952 ****
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();
{
--- 3064,3082 ----
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
*** 2960,2976 ****
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_exception_set(PLy_exc_spi_error, edata->message);
--- 3090,3109 ----
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_exception_set(PLy_exc_spi_error, edata->message);
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3048,3056 ****
return NULL;
}
! BeginInternalSubTransaction(NULL);
! /* Want to run inside function's memory context */
! MemoryContextSwitchTo(oldcontext);
PG_TRY();
{
--- 3181,3192 ----
return NULL;
}
! 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
*** 3105,3120 ****
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();
{
--- 3241,3259 ----
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
*** 3138,3154 ****
}
}
! /* 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_exception_set(PLy_exc_spi_error, edata->message);
--- 3277,3296 ----
}
}
! 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_exception_set(PLy_exc_spi_error, edata->message);
*************** PLy_spi_execute_query(char *query, long
*** 3177,3189 ****
MemoryContext volatile oldcontext = CurrentMemoryContext;
ResourceOwner volatile oldowner = CurrentResourceOwner;
! /*
! * 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();
{
--- 3319,3334 ----
MemoryContext volatile oldcontext = CurrentMemoryContext;
ResourceOwner volatile 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
*** 3191,3206 ****
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();
{
--- 3336,3354 ----
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
*** 3211,3227 ****
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_exception_set(PLy_exc_spi_error, edata->message);
--- 3359,3378 ----
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_exception_set(PLy_exc_spi_error, edata->message);
*************** PLy_spi_execute_fetch_result(SPITupleTab
*** 3294,3299 ****
--- 3445,3593 ----
return (PyObject *) result;
}
+ /* s = plpy.subxact() */
+ 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;
+ }
+
/*
* Exception support
*/
*************** _PG_init(void)
*** 3418,3423 ****
--- 3712,3719 ----
32, &hash_ctl,
HASH_ELEM | HASH_FUNCTION);
+ explicit_subtransactions = NIL;
+
inited = true;
}
*************** PLy_init_plpy(void)
*** 3453,3458 ****
--- 3749,3756 ----
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 ...3567863 .
*** 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.subxact()
+ 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.subxact():
+ 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.subxact():
+ plpy.execute("insert into subxact_tbl values(2)")
+ try:
+ with plpy.subxact():
+ 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.subxact():
+ 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.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_enter_without_exit() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__exit__(None, None, None)
+ plpy.subxact().__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_enter_twice() RETURNS void
+ AS $$
+ plpy.subxact().__enter__()
+ plpy.subxact().__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_same_subxact_twice() RETURNS void
+ AS $$
+ s = plpy.subxact()
+ 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.subxact()
+ 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.subxact() as s:
+ s.__enter__()
+ $$ LANGUAGE plpythonu;
+
+ CREATE FUNCTION subxact_exit_subxact_in_with() RETURNS void
+ AS $$
+ with plpy.subxact() 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.subxact():
+ 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.subxact()
+ 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
