11
INSERT INTO xacttest (a, b) VALUES (777, 777.777);
15
-- should retrieve one value--
16
SELECT a FROM xacttest WHERE a > 100;
21
CREATE TABLE disappear (a int4);
26
SELECT * FROM aggtest;
31
SELECT oid FROM pg_class WHERE relname = 'disappear';
33
-- should have members again
34
SELECT * FROM aggtest;
39
CREATE TABLE writetest (a int);
40
CREATE TEMPORARY TABLE temptest (a int);
42
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
44
DROP TABLE writetest; -- fail
45
INSERT INTO writetest VALUES (1); -- fail
46
SELECT * FROM writetest; -- ok
47
DELETE FROM temptest; -- ok
48
UPDATE temptest SET a = 0 WHERE a = 1 AND writetest.a = temptest.a; -- ok
49
PREPARE test AS UPDATE writetest SET a = 0; -- ok
51
SELECT * FROM writetest, temptest; -- ok
52
CREATE TABLE test AS SELECT * FROM writetest; -- fail
54
START TRANSACTION READ WRITE;
55
DROP TABLE writetest; -- ok
58
-- Subtransactions, basic tests
59
-- create & drop tables
60
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
61
CREATE TABLE foobar (a int);
63
CREATE TABLE foo (a int);
66
CREATE TABLE bar (a int);
67
ROLLBACK TO SAVEPOINT one;
68
RELEASE SAVEPOINT one;
70
CREATE TABLE baz (a int);
71
RELEASE SAVEPOINT two;
73
CREATE TABLE barbaz (a int);
75
-- should exist: barbaz, baz, foo
76
SELECT * FROM foo; -- should be empty
77
SELECT * FROM bar; -- shouldn't exist
78
SELECT * FROM barbaz; -- should be empty
79
SELECT * FROM baz; -- should be empty
83
INSERT INTO foo VALUES (1);
85
INSERT into bar VALUES (1);
87
RELEASE SAVEPOINT one;
89
INSERT into barbaz VALUES (1);
93
INSERT INTO foo VALUES (2);
94
RELEASE SAVEPOINT four;
95
ROLLBACK TO SAVEPOINT three;
96
RELEASE SAVEPOINT three;
97
INSERT INTO foo VALUES (3);
99
SELECT * FROM foo; -- should have 1 and 3
100
SELECT * FROM barbaz; -- should have 1
102
-- test whole-tree commit
106
ROLLBACK TO SAVEPOINT one;
107
RELEASE SAVEPOINT one;
109
CREATE TABLE savepoints (a int);
111
INSERT INTO savepoints VALUES (1);
113
INSERT INTO savepoints VALUES (2);
115
INSERT INTO savepoints VALUES (3);
116
ROLLBACK TO SAVEPOINT five;
118
COMMIT; -- should not be in a transaction block
119
SELECT * FROM savepoints;
121
-- test whole-tree rollback
124
DELETE FROM savepoints WHERE a=1;
125
RELEASE SAVEPOINT one;
127
DELETE FROM savepoints WHERE a=1;
129
DELETE FROM savepoints WHERE a=2;
131
COMMIT; -- should not be in a transaction block
133
SELECT * FROM savepoints;
135
-- test whole-tree commit on an aborted subtransaction
137
INSERT INTO savepoints VALUES (4);
139
INSERT INTO savepoints VALUES (5);
142
SELECT * FROM savepoints;
145
INSERT INTO savepoints VALUES (6);
147
INSERT INTO savepoints VALUES (7);
148
RELEASE SAVEPOINT one;
149
INSERT INTO savepoints VALUES (8);
151
-- rows 6 and 8 should have been created by the same xact
152
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
153
-- rows 6 and 7 should have been created by different xacts
154
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
157
INSERT INTO savepoints VALUES (9);
159
INSERT INTO savepoints VALUES (10);
160
ROLLBACK TO SAVEPOINT one;
161
INSERT INTO savepoints VALUES (11);
163
SELECT a FROM savepoints WHERE a in (9, 10, 11);
164
-- rows 9 and 11 should have been created by different xacts
165
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
168
INSERT INTO savepoints VALUES (12);
170
INSERT INTO savepoints VALUES (13);
172
INSERT INTO savepoints VALUES (14);
173
ROLLBACK TO SAVEPOINT one;
174
INSERT INTO savepoints VALUES (15);
176
INSERT INTO savepoints VALUES (16);
178
INSERT INTO savepoints VALUES (17);
180
SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
183
INSERT INTO savepoints VALUES (18);
185
INSERT INTO savepoints VALUES (19);
187
INSERT INTO savepoints VALUES (20);
188
ROLLBACK TO SAVEPOINT one;
189
INSERT INTO savepoints VALUES (21);
190
ROLLBACK TO SAVEPOINT one;
191
INSERT INTO savepoints VALUES (22);
193
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
195
DROP TABLE savepoints;
197
-- only in a transaction block:
199
ROLLBACK TO SAVEPOINT one;
200
RELEASE SAVEPOINT one;
202
-- Only "rollback to" allowed in aborted state
206
SAVEPOINT two; -- ignored till the end of ...
207
RELEASE SAVEPOINT one; -- ignored till the end of ...
208
ROLLBACK TO SAVEPOINT one;
211
SELECT 1; -- this should work
213
-- check non-transactional behavior of cursors
215
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
218
ROLLBACK TO SAVEPOINT one;
220
RELEASE SAVEPOINT one;
223
DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1;
226
ROLLBACK TO SAVEPOINT two;
227
-- c is now dead to the world ...
229
ROLLBACK TO SAVEPOINT two;
230
RELEASE SAVEPOINT two;
235
-- Check that "stable" functions are really stable. They should not be
236
-- able to see the partial results of the calling query. (Ideally we would
237
-- also check that they don't see commits of concurrent transactions, but
238
-- that's a mite hard to do within the limitations of pg_regress.)
240
select * from xacttest;
242
create or replace function max_xacttest() returns smallint language sql as
243
'select max(a) from xacttest' stable;
246
update xacttest set a = max_xacttest() + 10 where a > 0;
247
select * from xacttest;
250
-- But a volatile function can see the partial results of the calling query
251
create or replace function max_xacttest() returns smallint language sql as
252
'select max(a) from xacttest' volatile;
255
update xacttest set a = max_xacttest() + 10 where a > 0;
256
select * from xacttest;
259
-- Now the same test with plpgsql (since it depends on SPI which is different)
260
create or replace function max_xacttest() returns smallint language plpgsql as
261
'begin return max(a) from xacttest; end' stable;
264
update xacttest set a = max_xacttest() + 10 where a > 0;
265
select * from xacttest;
268
create or replace function max_xacttest() returns smallint language plpgsql as
269
'begin return max(a) from xacttest; end' volatile;
272
update xacttest set a = max_xacttest() + 10 where a > 0;
273
select * from xacttest;
277
-- test case for problems with dropping an open relation during abort
280
CREATE TABLE koju (a INT UNIQUE);
281
INSERT INTO koju VALUES (1);
282
INSERT INTO koju VALUES (1);
285
CREATE TABLE koju (a INT UNIQUE);
286
INSERT INTO koju VALUES (1);
287
INSERT INTO koju VALUES (1);