blob: 5e19bbbc636e27a2828b2f02ba41ee923c5e4981 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
--
-- Test explicit subtransactions
--
CREATE TABLE subtransaction_tbl (
i integer
);
--
-- We use this wrapper to catch errors and return errormsg only,
-- because values of $::errorinfo variable contain procedure name which
-- includes OID, so it's not stable
--
CREATE FUNCTION pltcl_wrapper(statement text) RETURNS text
AS $$
if [catch {spi_exec $1} msg] {
return "ERROR: $msg"
} else {
return "SUCCESS: $msg"
}
$$ LANGUAGE pltcl;
-- Test subtransaction successfully committed
CREATE FUNCTION subtransaction_ctx_success() RETURNS void
AS $$
spi_exec "INSERT INTO subtransaction_tbl VALUES(1)"
subtransaction {
spi_exec "INSERT INTO subtransaction_tbl VALUES(2)"
}
$$ LANGUAGE pltcl;
BEGIN;
INSERT INTO subtransaction_tbl VALUES(0);
SELECT subtransaction_ctx_success();
subtransaction_ctx_success
----------------------------
(1 row)
COMMIT;
SELECT * FROM subtransaction_tbl;
i
---
0
1
2
(3 rows)
TRUNCATE subtransaction_tbl;
-- Test subtransaction rollback
CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS void
AS $$
spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
subtransaction {
spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
if {$1 == "SPI"} {
spi_exec "INSERT INTO subtransaction_tbl VALUES ('oops')"
} elseif { $1 == "Tcl"} {
elog ERROR "Tcl error"
}
}
$$ LANGUAGE pltcl;
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test()');
pltcl_wrapper
---------------
SUCCESS: 1
(1 row)
SELECT * FROM subtransaction_tbl;
i
---
1
2
(2 rows)
TRUNCATE subtransaction_tbl;
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''SPI'')');
pltcl_wrapper
------------------------------------------------------
ERROR: invalid input syntax for type integer: "oops"
(1 row)
SELECT * FROM subtransaction_tbl;
i
---
(0 rows)
TRUNCATE subtransaction_tbl;
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''Tcl'')');
pltcl_wrapper
------------------
ERROR: Tcl error
(1 row)
SELECT * FROM subtransaction_tbl;
i
---
(0 rows)
TRUNCATE subtransaction_tbl;
-- Nested subtransactions
CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text
AS $$
spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
subtransaction {
spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
if [catch {
subtransaction {
spi_exec "INSERT INTO subtransaction_tbl VALUES (3)"
spi_exec "error"
}
} errormsg] {
if {$1 != "t"} {
error $errormsg $::errorInfo $::errorCode
}
elog NOTICE "Swallowed $errormsg"
}
}
return "ok"
$$ LANGUAGE pltcl;
SELECT pltcl_wrapper('SELECT subtransaction_nested_test()');
pltcl_wrapper
----------------------------------------
ERROR: syntax error at or near "error"
(1 row)
SELECT * FROM subtransaction_tbl;
i
---
(0 rows)
TRUNCATE subtransaction_tbl;
SELECT pltcl_wrapper('SELECT subtransaction_nested_test(''t'')');
NOTICE: Swallowed syntax error at or near "error"
pltcl_wrapper
---------------
SUCCESS: 1
(1 row)
SELECT * FROM subtransaction_tbl;
i
---
1
2
(2 rows)
TRUNCATE subtransaction_tbl;
|
