--
-- Test explicit subtransactions
--
-- Test table to see if transactions get properly rolled back
CREATE TABLE subtransaction_tbl (
    i integer
);
-- Explicit case for Python <2.6
CREATE FUNCTION subtransaction_test(what_error text = NULL) RETURNS text
AS $$
import sys
subxact = plpy.subtransaction()
subxact.__enter__()
exc = True
try:
    try:
        plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
        plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)")
        if what_error == "SPI":
            plpy.execute("INSERT INTO subtransaction_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 subtransaction_test();
 subtransaction_test 
---------------------
 
(1 row)

SELECT * FROM subtransaction_tbl;
 i 
---
 1
 2
(2 rows)

TRUNCATE subtransaction_tbl;
SELECT subtransaction_test('SPI');
ERROR:  spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
                                               ^
QUERY:  INSERT INTO subtransaction_tbl VALUES ('oops')
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_test", line 11, in <module>
    plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')")
PL/Python function "subtransaction_test"
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
SELECT subtransaction_test('Python');
ERROR:  AttributeError: 'module' object has no attribute 'attribute_error'
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_test", line 13, in <module>
    plpy.attribute_error
PL/Python function "subtransaction_test"
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
-- Context manager case for Python >=2.6
CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS text
AS $$
with plpy.subtransaction():
    plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
    plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)")
    if what_error == "SPI":
        plpy.execute("INSERT INTO subtransaction_tbl VALUES ('oops')")
    elif what_error == "Python":
        plpy.attribute_error
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "subtransaction_ctx_test"
DETAIL:  SyntaxError: invalid syntax (<string>, line 3)
SELECT subtransaction_ctx_test();
ERROR:  function subtransaction_ctx_test() does not exist
LINE 1: SELECT subtransaction_ctx_test();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
SELECT subtransaction_ctx_test('SPI');
ERROR:  function subtransaction_ctx_test(unknown) does not exist
LINE 1: SELECT subtransaction_ctx_test('SPI');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
SELECT subtransaction_ctx_test('Python');
ERROR:  function subtransaction_ctx_test(unknown) does not exist
LINE 1: SELECT subtransaction_ctx_test('Python');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
-- Nested subtransactions
CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text
AS $$
plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
with plpy.subtransaction():
    plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)")
    try:
        with plpy.subtransaction():
            plpy.execute("INSERT INTO subtransaction_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 "subtransaction_nested_test"
DETAIL:  SyntaxError: invalid syntax (<string>, line 4)
SELECT subtransaction_nested_test();
ERROR:  function subtransaction_nested_test() does not exist
LINE 1: SELECT subtransaction_nested_test();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
SELECT subtransaction_nested_test('t');
ERROR:  function subtransaction_nested_test(unknown) does not exist
LINE 1: SELECT subtransaction_nested_test('t');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
-- Nested subtransactions that recursively call code dealing with
-- subtransactions
CREATE FUNCTION subtransaction_deeply_nested_test() RETURNS text
AS $$
plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
with plpy.subtransaction():
    plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)")
    plpy.execute("SELECT subtransaction_nested_test('t')")
return "ok"
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "subtransaction_deeply_nested_test"
DETAIL:  SyntaxError: invalid syntax (<string>, line 4)
SELECT subtransaction_deeply_nested_test();
ERROR:  function subtransaction_deeply_nested_test() does not exist
LINE 1: SELECT subtransaction_deeply_nested_test();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
-- Error conditions from not opening/closing subtransactions
CREATE FUNCTION subtransaction_exit_without_enter() RETURNS void
AS $$
plpy.subtransaction().__exit__(None, None, None)
$$ LANGUAGE plpythonu;
CREATE FUNCTION subtransaction_enter_without_exit() RETURNS void
AS $$
plpy.subtransaction().__enter__()
$$ LANGUAGE plpythonu;
CREATE FUNCTION subtransaction_exit_twice() RETURNS void
AS $$
plpy.subtransaction().__enter__()
plpy.subtransaction().__exit__(None, None, None)
plpy.subtransaction().__exit__(None, None, None)
$$ LANGUAGE plpythonu;
CREATE FUNCTION subtransaction_enter_twice() RETURNS void
AS $$
plpy.subtransaction().__enter__()
plpy.subtransaction().__enter__()
$$ LANGUAGE plpythonu;
CREATE FUNCTION subtransaction_exit_same_subtransaction_twice() RETURNS void
AS $$
s = plpy.subtransaction()
s.__enter__()
s.__exit__(None, None, None)
s.__exit__(None, None, None)
$$ LANGUAGE plpythonu;
CREATE FUNCTION subtransaction_enter_same_subtransaction_twice() RETURNS void
AS $$
s = plpy.subtransaction()
s.__enter__()
s.__enter__()
s.__exit__(None, None, None)
$$ LANGUAGE plpythonu;
-- No warnings here, as the subtransaction gets indeed closed
CREATE FUNCTION subtransaction_enter_subtransaction_in_with() RETURNS void
AS $$
with plpy.subtransaction() as s:
    s.__enter__()
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "subtransaction_enter_subtransaction_in_with"
DETAIL:  SyntaxError: invalid syntax (<string>, line 3)
CREATE FUNCTION subtransaction_exit_subtransaction_in_with() RETURNS void
AS $$
with plpy.subtransaction() as s:
    s.__exit__(None, None, None)
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "subtransaction_exit_subtransaction_in_with"
DETAIL:  SyntaxError: invalid syntax (<string>, line 3)
SELECT subtransaction_exit_without_enter();
ERROR:  ValueError: this subtransaction has not been entered
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_exit_without_enter", line 2, in <module>
    plpy.subtransaction().__exit__(None, None, None)
PL/Python function "subtransaction_exit_without_enter"
SELECT subtransaction_enter_without_exit();
WARNING:  forcibly aborting a subtransaction that has not been exited
CONTEXT:  PL/Python function "subtransaction_enter_without_exit"
 subtransaction_enter_without_exit 
-----------------------------------
 
(1 row)

SELECT subtransaction_exit_twice();
WARNING:  forcibly aborting a subtransaction that has not been exited
CONTEXT:  PL/Python function "subtransaction_exit_twice"
ERROR:  ValueError: this subtransaction has not been entered
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_exit_twice", line 3, in <module>
    plpy.subtransaction().__exit__(None, None, None)
PL/Python function "subtransaction_exit_twice"
SELECT subtransaction_enter_twice();
WARNING:  forcibly aborting a subtransaction that has not been exited
CONTEXT:  PL/Python function "subtransaction_enter_twice"
WARNING:  forcibly aborting a subtransaction that has not been exited
CONTEXT:  PL/Python function "subtransaction_enter_twice"
 subtransaction_enter_twice 
----------------------------
 
(1 row)

SELECT subtransaction_exit_same_subtransaction_twice();
ERROR:  ValueError: this subtransaction has already been exited
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_exit_same_subtransaction_twice", line 5, in <module>
    s.__exit__(None, None, None)
PL/Python function "subtransaction_exit_same_subtransaction_twice"
SELECT subtransaction_enter_same_subtransaction_twice();
WARNING:  forcibly aborting a subtransaction that has not been exited
CONTEXT:  PL/Python function "subtransaction_enter_same_subtransaction_twice"
ERROR:  ValueError: this subtransaction has already been entered
CONTEXT:  Traceback (most recent call last):
  PL/Python function "subtransaction_enter_same_subtransaction_twice", line 4, in <module>
    s.__enter__()
PL/Python function "subtransaction_enter_same_subtransaction_twice"
SELECT subtransaction_enter_subtransaction_in_with();
ERROR:  function subtransaction_enter_subtransaction_in_with() does not exist
LINE 1: SELECT subtransaction_enter_subtransaction_in_with();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT subtransaction_exit_subtransaction_in_with();
ERROR:  function subtransaction_exit_subtransaction_in_with() does not exist
LINE 1: SELECT subtransaction_exit_subtransaction_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 subtransaction_mix_explicit_and_implicit() RETURNS void
AS $$
p = plpy.prepare("INSERT INTO subtransaction_tbl VALUES ($1)", ["integer"])
try:
    with plpy.subtransaction():
        plpy.execute("INSERT INTO subtransaction_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 subtransaction_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 "subtransaction_mix_explicit_and_implicit"
DETAIL:  SyntaxError: invalid syntax (<string>, line 5)
SELECT subtransaction_mix_explicit_and_implicit();
ERROR:  function subtransaction_mix_explicit_and_implicit() does not exist
LINE 1: SELECT subtransaction_mix_explicit_and_implicit();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
-- Alternative method names for Python <2.6
CREATE FUNCTION subtransaction_alternative_names() RETURNS void
AS $$
s = plpy.subtransaction()
s.enter()
s.exit(None, None, None)
$$ LANGUAGE plpythonu;
SELECT subtransaction_alternative_names();
 subtransaction_alternative_names 
----------------------------------
 
(1 row)

-- try/catch inside a subtransaction block
CREATE FUNCTION try_catch_inside_subtransaction() RETURNS void
AS $$
with plpy.subtransaction():
     plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
     try:
         plpy.execute("INSERT INTO subtransaction_tbl VALUES ('a')")
     except plpy.SPIError:
         plpy.notice("caught")
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "try_catch_inside_subtransaction"
DETAIL:  SyntaxError: invalid syntax (<string>, line 3)
SELECT try_catch_inside_subtransaction();
ERROR:  function try_catch_inside_subtransaction() does not exist
LINE 1: SELECT try_catch_inside_subtransaction();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

TRUNCATE subtransaction_tbl;
ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "subtransaction_tbl_pkey" for table "subtransaction_tbl"
CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void
AS $$
with plpy.subtransaction():
     plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
     try:
         plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
     except plpy.SPIError:
         plpy.notice("caught")
$$ LANGUAGE plpythonu;
ERROR:  could not compile PL/Python function "pk_violation_inside_subtransaction"
DETAIL:  SyntaxError: invalid syntax (<string>, line 3)
SELECT pk_violation_inside_subtransaction();
ERROR:  function pk_violation_inside_subtransaction() does not exist
LINE 1: SELECT pk_violation_inside_subtransaction();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM subtransaction_tbl;
 i 
---
(0 rows)

DROP TABLE subtransaction_tbl;