blob: 5c14d4732eb904db6c40530e32f04f25f1d8edf5 (
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
|
CREATE TABLE test1 (a int, b text);
CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
if ($i % 2 == 0) {
spi_commit();
} else {
spi_rollback();
}
}
$$;
CALL transaction_test1();
SELECT * FROM test1;
TRUNCATE test1;
DO
LANGUAGE plperl
$$
foreach my $i (0..9) {
spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
if ($i % 2 == 0) {
spi_commit();
} else {
spi_rollback();
}
}
$$;
SELECT * FROM test1;
TRUNCATE test1;
-- not allowed in a function
CREATE FUNCTION transaction_test2() RETURNS int
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
if ($i % 2 == 0) {
spi_commit();
} else {
spi_rollback();
}
}
return 1;
$$;
SELECT transaction_test2();
SELECT * FROM test1;
-- also not allowed if procedure is called from a function
CREATE FUNCTION transaction_test3() RETURNS int
LANGUAGE plperl
AS $$
spi_exec_query("CALL transaction_test1()");
return 1;
$$;
SELECT transaction_test3();
SELECT * FROM test1;
-- DO block inside function
CREATE FUNCTION transaction_test4() RETURNS int
LANGUAGE plperl
AS $$
spi_exec_query('DO LANGUAGE plperl $x$ spi_commit(); $x$');
return 1;
$$;
SELECT transaction_test4();
-- commit inside cursor loop
CREATE TABLE test2 (x int);
INSERT INTO test2 VALUES (0), (1), (2), (3), (4);
TRUNCATE test1;
DO LANGUAGE plperl $$
my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
my $row;
while (defined($row = spi_fetchrow($sth))) {
spi_exec_query("INSERT INTO test1 (a) VALUES (" . $row->{x} . ")");
spi_commit();
}
$$;
SELECT * FROM test1;
-- rollback inside cursor loop
TRUNCATE test1;
DO LANGUAGE plperl $$
my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
my $row;
while (defined($row = spi_fetchrow($sth))) {
spi_exec_query("INSERT INTO test1 (a) VALUES (" . $row->{x} . ")");
spi_rollback();
}
$$;
SELECT * FROM test1;
DROP TABLE test1;
DROP TABLE test2;
|
