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 FROM writetest WHERE temptest.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
145 ROLLBACK TO SAVEPOINT one;
146 RELEASE SAVEPOINT one;
148 CREATE TABLE savepoints (a int);
150 INSERT INTO savepoints VALUES (1);
152 INSERT INTO savepoints VALUES (2);
154 INSERT INTO savepoints VALUES (3);
155 ROLLBACK TO SAVEPOINT five;
157 COMMIT; -- should not be in a transaction block
158 WARNING: there is no transaction in progress
159 SELECT * FROM savepoints;
166 -- test whole-tree rollback
169 DELETE FROM savepoints WHERE a=1;
170 RELEASE SAVEPOINT one;
172 DELETE FROM savepoints WHERE a=1;
174 DELETE FROM savepoints WHERE a=2;
176 COMMIT; -- should not be in a transaction block
177 WARNING: there is no transaction in progress
179 SELECT * FROM savepoints;
186 -- test whole-tree commit on an aborted subtransaction
188 INSERT INTO savepoints VALUES (4);
190 INSERT INTO savepoints VALUES (5);
192 ERROR: column "foo" does not exist
196 SELECT * FROM savepoints;
204 INSERT INTO savepoints VALUES (6);
206 INSERT INTO savepoints VALUES (7);
207 RELEASE SAVEPOINT one;
208 INSERT INTO savepoints VALUES (8);
210 -- rows 6 and 8 should have been created by the same xact
211 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
217 -- rows 6 and 7 should have been created by different xacts
218 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
225 INSERT INTO savepoints VALUES (9);
227 INSERT INTO savepoints VALUES (10);
228 ROLLBACK TO SAVEPOINT one;
229 INSERT INTO savepoints VALUES (11);
231 SELECT a FROM savepoints WHERE a in (9, 10, 11);
238 -- rows 9 and 11 should have been created by different xacts
239 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
246 INSERT INTO savepoints VALUES (12);
248 INSERT INTO savepoints VALUES (13);
250 INSERT INTO savepoints VALUES (14);
251 ROLLBACK TO SAVEPOINT one;
252 INSERT INTO savepoints VALUES (15);
254 INSERT INTO savepoints VALUES (16);
256 INSERT INTO savepoints VALUES (17);
258 SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
268 INSERT INTO savepoints VALUES (18);
270 INSERT INTO savepoints VALUES (19);
272 INSERT INTO savepoints VALUES (20);
273 ROLLBACK TO SAVEPOINT one;
274 INSERT INTO savepoints VALUES (21);
275 ROLLBACK TO SAVEPOINT one;
276 INSERT INTO savepoints VALUES (22);
278 SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
285 DROP TABLE savepoints;
286 -- only in a transaction block:
288 ERROR: SAVEPOINT can only be used in transaction blocks
289 ROLLBACK TO SAVEPOINT one;
290 ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
291 RELEASE SAVEPOINT one;
292 ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
293 -- Only "rollback to" allowed in aborted state
297 ERROR: division by zero
298 SAVEPOINT two; -- ignored till the end of ...
299 ERROR: current transaction is aborted, commands ignored until end of transaction block
300 RELEASE SAVEPOINT one; -- ignored till the end of ...
301 ERROR: current transaction is aborted, commands ignored until end of transaction block
302 ROLLBACK TO SAVEPOINT one;
310 SELECT 1; -- this should work
316 -- check non-transactional behavior of cursors
318 DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
335 ROLLBACK TO SAVEPOINT one;
351 RELEASE SAVEPOINT one;
368 DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
371 ERROR: division by zero
372 ROLLBACK TO SAVEPOINT two;
373 -- c is now dead to the world ...
375 ERROR: portal "c" cannot be run
376 ROLLBACK TO SAVEPOINT two;
377 RELEASE SAVEPOINT two;
379 ERROR: portal "c" cannot be run
382 -- Check that "stable" functions are really stable. They should not be
383 -- able to see the partial results of the calling query. (Ideally we would
384 -- also check that they don't see commits of concurrent transactions, but
385 -- that's a mite hard to do within the limitations of pg_regress.)
387 select * from xacttest;
397 create or replace function max_xacttest() returns smallint language sql as
398 'select max(a) from xacttest' stable;
400 update xacttest set a = max_xacttest() + 10 where a > 0;
401 select * from xacttest;
412 -- But a volatile function can see the partial results of the calling query
413 create or replace function max_xacttest() returns smallint language sql as
414 'select max(a) from xacttest' volatile;
416 update xacttest set a = max_xacttest() + 10 where a > 0;
417 select * from xacttest;
428 -- Now the same test with plpgsql (since it depends on SPI which is different)
429 create or replace function max_xacttest() returns smallint language plpgsql as
430 'begin return max(a) from xacttest; end' stable;
432 update xacttest set a = max_xacttest() + 10 where a > 0;
433 select * from xacttest;
444 create or replace function max_xacttest() returns smallint language plpgsql as
445 'begin return max(a) from xacttest; end' volatile;
447 update xacttest set a = max_xacttest() + 10 where a > 0;
448 select * from xacttest;
459 -- test case for problems with dropping an open relation during abort
462 CREATE TABLE koju (a INT UNIQUE);
463 NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
464 INSERT INTO koju VALUES (1);
465 INSERT INTO koju VALUES (1);
466 ERROR: duplicate key value violates unique constraint "koju_a_key"
468 CREATE TABLE koju (a INT UNIQUE);
469 NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
470 INSERT INTO koju VALUES (1);
471 INSERT INTO koju VALUES (1);
472 ERROR: duplicate key value violates unique constraint "koju_a_key"
477 -- verify that cursors created during an aborted subtransaction are
478 -- closed, but that we do not rollback the effect of any FETCHs
479 -- performed in the aborted subtransaction
482 create table abc (a int);
483 insert into abc values (5);
484 insert into abc values (10);
485 declare foo cursor for select * from abc;
495 ERROR: cursor "foo" does not exist
498 create table abc (a int);
499 insert into abc values (5);
500 insert into abc values (10);
501 insert into abc values (15);
502 declare foo cursor for select * from abc;
524 -- tests for the "tid" type
525 SELECT '(3, 3)'::tid = '(3, 4)'::tid;
531 SELECT '(3, 3)'::tid = '(3, 3)'::tid;
537 SELECT '(3, 3)'::tid <> '(3, 3)'::tid;
543 SELECT '(3, 3)'::tid <> '(3, 4)'::tid;