8
INSERT INTO xacttest (a, b) VALUES (777, 777.777);
10
-- should retrieve one value--
11
SELECT a FROM xacttest WHERE a > 100;
18
CREATE TABLE disappear (a int4);
21
SELECT * FROM aggtest;
28
SELECT oid FROM pg_class WHERE relname = 'disappear';
33
-- should have members again
34
SELECT * FROM aggtest;
44
CREATE TABLE writetest (a int);
45
CREATE TEMPORARY TABLE temptest (a int);
46
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
47
DROP TABLE writetest; -- fail
48
ERROR: transaction is read-only
49
INSERT INTO writetest VALUES (1); -- fail
50
ERROR: transaction is read-only
51
SELECT * FROM writetest; -- ok
56
DELETE FROM temptest; -- ok
57
UPDATE temptest SET a = 0 WHERE a = 1 AND writetest.a = temptest.a; -- ok
58
PREPARE test AS UPDATE writetest SET a = 0; -- ok
60
ERROR: transaction is read-only
61
SELECT * FROM writetest, temptest; -- ok
66
CREATE TABLE test AS SELECT * FROM writetest; -- fail
67
ERROR: transaction is read-only
68
START TRANSACTION READ WRITE;
69
DROP TABLE writetest; -- ok
71
-- Subtransactions, basic tests
72
-- create & drop tables
73
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
74
CREATE TABLE foobar (a int);
76
CREATE TABLE foo (a int);
79
CREATE TABLE bar (a int);
80
ROLLBACK TO SAVEPOINT one;
81
RELEASE SAVEPOINT one;
83
CREATE TABLE baz (a int);
84
RELEASE SAVEPOINT two;
86
CREATE TABLE barbaz (a int);
88
-- should exist: barbaz, baz, foo
89
SELECT * FROM foo; -- should be empty
94
SELECT * FROM bar; -- shouldn't exist
95
ERROR: relation "bar" does not exist
96
SELECT * FROM barbaz; -- should be empty
101
SELECT * FROM baz; -- should be empty
108
INSERT INTO foo VALUES (1);
110
INSERT into bar VALUES (1);
111
ERROR: relation "bar" does not exist
113
RELEASE SAVEPOINT one;
115
INSERT into barbaz VALUES (1);
119
INSERT INTO foo VALUES (2);
120
RELEASE SAVEPOINT four;
121
ROLLBACK TO SAVEPOINT three;
122
RELEASE SAVEPOINT three;
123
INSERT INTO foo VALUES (3);
125
SELECT * FROM foo; -- should have 1 and 3
132
SELECT * FROM barbaz; -- should have 1
138
-- test whole-tree commit
142
ERROR: column "foo" does not exist
143
ROLLBACK TO SAVEPOINT one;
144
RELEASE SAVEPOINT one;
146
CREATE TABLE savepoints (a int);
148
INSERT INTO savepoints VALUES (1);
150
INSERT INTO savepoints VALUES (2);
152
INSERT INTO savepoints VALUES (3);
153
ROLLBACK TO SAVEPOINT five;
155
COMMIT; -- should not be in a transaction block
156
WARNING: there is no transaction in progress
157
SELECT * FROM savepoints;
164
-- test whole-tree rollback
167
DELETE FROM savepoints WHERE a=1;
168
RELEASE SAVEPOINT one;
170
DELETE FROM savepoints WHERE a=1;
172
DELETE FROM savepoints WHERE a=2;
174
COMMIT; -- should not be in a transaction block
175
WARNING: there is no transaction in progress
177
SELECT * FROM savepoints;
184
-- test whole-tree commit on an aborted subtransaction
186
INSERT INTO savepoints VALUES (4);
188
INSERT INTO savepoints VALUES (5);
190
ERROR: column "foo" does not exist
192
SELECT * FROM savepoints;
200
INSERT INTO savepoints VALUES (6);
202
INSERT INTO savepoints VALUES (7);
203
RELEASE SAVEPOINT one;
204
INSERT INTO savepoints VALUES (8);
206
-- rows 6 and 8 should have been created by the same xact
207
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
213
-- rows 6 and 7 should have been created by different xacts
214
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
221
INSERT INTO savepoints VALUES (9);
223
INSERT INTO savepoints VALUES (10);
224
ROLLBACK TO SAVEPOINT one;
225
INSERT INTO savepoints VALUES (11);
227
SELECT a FROM savepoints WHERE a in (9, 10, 11);
234
-- rows 9 and 11 should have been created by different xacts
235
SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
242
INSERT INTO savepoints VALUES (12);
244
INSERT INTO savepoints VALUES (13);
246
INSERT INTO savepoints VALUES (14);
247
ROLLBACK TO SAVEPOINT one;
248
INSERT INTO savepoints VALUES (15);
250
INSERT INTO savepoints VALUES (16);
252
INSERT INTO savepoints VALUES (17);
254
SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
264
INSERT INTO savepoints VALUES (18);
266
INSERT INTO savepoints VALUES (19);
268
INSERT INTO savepoints VALUES (20);
269
ROLLBACK TO SAVEPOINT one;
270
INSERT INTO savepoints VALUES (21);
271
ROLLBACK TO SAVEPOINT one;
272
INSERT INTO savepoints VALUES (22);
274
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
281
DROP TABLE savepoints;
282
-- only in a transaction block:
284
ERROR: SAVEPOINT may only be used in transaction blocks
285
ROLLBACK TO SAVEPOINT one;
286
ERROR: ROLLBACK TO SAVEPOINT may only be used in transaction blocks
287
RELEASE SAVEPOINT one;
288
ERROR: RELEASE SAVEPOINT may only be used in transaction blocks
289
-- Only "rollback to" allowed in aborted state
293
ERROR: division by zero
294
SAVEPOINT two; -- ignored till the end of ...
295
ERROR: current transaction is aborted, commands ignored until end of transaction block
296
RELEASE SAVEPOINT one; -- ignored till the end of ...
297
ERROR: current transaction is aborted, commands ignored until end of transaction block
298
ROLLBACK TO SAVEPOINT one;
306
SELECT 1; -- this should work
312
-- check non-transactional behavior of cursors
314
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
331
ROLLBACK TO SAVEPOINT one;
347
RELEASE SAVEPOINT one;
364
DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1;
367
ERROR: division by zero
368
ROLLBACK TO SAVEPOINT two;
369
-- c is now dead to the world ...
371
ERROR: portal "c" cannot be run
372
ROLLBACK TO SAVEPOINT two;
373
RELEASE SAVEPOINT two;
375
ERROR: portal "c" cannot be run
378
-- Check that "stable" functions are really stable. They should not be
379
-- able to see the partial results of the calling query. (Ideally we would
380
-- also check that they don't see commits of concurrent transactions, but
381
-- that's a mite hard to do within the limitations of pg_regress.)
383
select * from xacttest;
393
create or replace function max_xacttest() returns smallint language sql as
394
'select max(a) from xacttest' stable;
396
update xacttest set a = max_xacttest() + 10 where a > 0;
397
select * from xacttest;
408
-- But a volatile function can see the partial results of the calling query
409
create or replace function max_xacttest() returns smallint language sql as
410
'select max(a) from xacttest' volatile;
412
update xacttest set a = max_xacttest() + 10 where a > 0;
413
select * from xacttest;
424
-- Now the same test with plpgsql (since it depends on SPI which is different)
425
create or replace function max_xacttest() returns smallint language plpgsql as
426
'begin return max(a) from xacttest; end' stable;
428
update xacttest set a = max_xacttest() + 10 where a > 0;
429
select * from xacttest;
440
create or replace function max_xacttest() returns smallint language plpgsql as
441
'begin return max(a) from xacttest; end' volatile;
443
update xacttest set a = max_xacttest() + 10 where a > 0;
444
select * from xacttest;
455
-- test case for problems with dropping an open relation during abort
458
CREATE TABLE koju (a INT UNIQUE);
459
NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
460
INSERT INTO koju VALUES (1);
461
INSERT INTO koju VALUES (1);
462
ERROR: duplicate key violates unique constraint "koju_a_key"
464
CREATE TABLE koju (a INT UNIQUE);
465
NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
466
INSERT INTO koju VALUES (1);
467
INSERT INTO koju VALUES (1);
468
ERROR: duplicate key violates unique constraint "koju_a_key"