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