1--
2-- Test explicit subtransactions
3--
4
5CREATE TABLE subtransaction_tbl (
6    i integer
7);
8
9--
10-- We use this wrapper to catch errors and return errormsg only,
11-- because values of $::errorinfo variable contain procedure name which
12-- includes OID, so it's not stable
13--
14CREATE FUNCTION pltcl_wrapper(statement text) RETURNS text
15AS $$
16    if [catch {spi_exec $1} msg] {
17        return "ERROR: $msg"
18    } else {
19        return "SUCCESS: $msg"
20    }
21$$ LANGUAGE pltcl;
22
23-- Test subtransaction successfully committed
24
25CREATE FUNCTION subtransaction_ctx_success() RETURNS void
26AS $$
27    spi_exec "INSERT INTO subtransaction_tbl VALUES(1)"
28    subtransaction {
29        spi_exec "INSERT INTO subtransaction_tbl VALUES(2)"
30    }
31$$ LANGUAGE pltcl;
32
33BEGIN;
34INSERT INTO subtransaction_tbl VALUES(0);
35SELECT subtransaction_ctx_success();
36COMMIT;
37SELECT * FROM subtransaction_tbl;
38TRUNCATE subtransaction_tbl;
39
40-- Test subtransaction rollback
41
42CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS void
43AS $$
44    spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
45    subtransaction {
46        spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
47        if {$1 == "SPI"} {
48            spi_exec "INSERT INTO subtransaction_tbl VALUES ('oops')"
49        } elseif { $1 == "Tcl"} {
50            elog ERROR "Tcl error"
51        }
52    }
53$$ LANGUAGE pltcl;
54
55SELECT pltcl_wrapper('SELECT subtransaction_ctx_test()');
56SELECT * FROM subtransaction_tbl;
57TRUNCATE subtransaction_tbl;
58
59SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''SPI'')');
60SELECT * FROM subtransaction_tbl;
61TRUNCATE subtransaction_tbl;
62
63SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''Tcl'')');
64SELECT * FROM subtransaction_tbl;
65TRUNCATE subtransaction_tbl;
66
67-- Nested subtransactions
68
69CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text
70AS $$
71spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
72subtransaction {
73    spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
74    if [catch {
75        subtransaction {
76            spi_exec "INSERT INTO subtransaction_tbl VALUES (3)"
77            spi_exec "error"
78        }
79    } errormsg] {
80        if {$1 != "t"} {
81            error $errormsg $::errorInfo $::errorCode
82        }
83        elog NOTICE "Swallowed $errormsg"
84    }
85}
86return "ok"
87$$ LANGUAGE pltcl;
88
89SELECT pltcl_wrapper('SELECT subtransaction_nested_test()');
90SELECT * FROM subtransaction_tbl;
91TRUNCATE subtransaction_tbl;
92
93SELECT pltcl_wrapper('SELECT subtransaction_nested_test(''t'')');
94SELECT * FROM subtransaction_tbl;
95TRUNCATE subtransaction_tbl;
96