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);
43 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- ok
44 SELECT * FROM writetest; -- ok
45 SET TRANSACTION READ WRITE; --fail
49 SET TRANSACTION READ ONLY; -- ok
50 SET TRANSACTION READ WRITE; -- ok
51 SET TRANSACTION READ ONLY; -- ok
52 SELECT * FROM writetest; -- ok
54 SET TRANSACTION READ ONLY; -- ok
55 SELECT * FROM writetest; -- ok
56 SET TRANSACTION READ ONLY; -- ok
57 SET TRANSACTION READ WRITE; --fail
61 SET TRANSACTION READ WRITE; -- ok
63 SET TRANSACTION READ WRITE; -- ok
64 SET TRANSACTION READ ONLY; -- ok
65 SELECT * FROM writetest; -- ok
66 SET TRANSACTION READ ONLY; -- ok
67 SET TRANSACTION READ WRITE; --fail
71 SET TRANSACTION READ WRITE; -- ok
73 SET TRANSACTION READ ONLY; -- ok
74 SELECT * FROM writetest; -- ok
75 ROLLBACK TO SAVEPOINT x;
76 SHOW transaction_read_only; -- off
78 SET TRANSACTION READ ONLY; -- ok
79 SELECT * FROM writetest; -- ok
81 SHOW transaction_read_only; -- off
84 SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
86 DROP TABLE writetest; -- fail
87 INSERT INTO writetest VALUES (1); -- fail
88 SELECT * FROM writetest; -- ok
89 DELETE FROM temptest; -- ok
90 UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok
91 PREPARE test AS UPDATE writetest SET a = 0; -- ok
93 SELECT * FROM writetest, temptest; -- ok
94 CREATE TABLE test AS SELECT * FROM writetest; -- fail
96 START TRANSACTION READ WRITE;
97 DROP TABLE writetest; -- ok
100 -- Subtransactions, basic tests
101 -- create & drop tables
102 SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
103 CREATE TABLE trans_foobar (a int);
105 CREATE TABLE trans_foo (a int);
107 DROP TABLE trans_foo;
108 CREATE TABLE trans_bar (a int);
109 ROLLBACK TO SAVEPOINT one;
110 RELEASE SAVEPOINT one;
112 CREATE TABLE trans_baz (a int);
113 RELEASE SAVEPOINT two;
114 drop TABLE trans_foobar;
115 CREATE TABLE trans_barbaz (a int);
117 -- should exist: trans_barbaz, trans_baz, trans_foo
118 SELECT * FROM trans_foo; -- should be empty
119 SELECT * FROM trans_bar; -- shouldn't exist
120 SELECT * FROM trans_barbaz; -- should be empty
121 SELECT * FROM trans_baz; -- should be empty
125 INSERT INTO trans_foo VALUES (1);
127 INSERT into trans_bar VALUES (1);
129 RELEASE SAVEPOINT one;
131 INSERT into trans_barbaz VALUES (1);
135 INSERT INTO trans_foo VALUES (2);
136 RELEASE SAVEPOINT four;
137 ROLLBACK TO SAVEPOINT three;
138 RELEASE SAVEPOINT three;
139 INSERT INTO trans_foo VALUES (3);
141 SELECT * FROM trans_foo; -- should have 1 and 3
142 SELECT * FROM trans_barbaz; -- should have 1
144 -- test whole-tree commit
148 ROLLBACK TO SAVEPOINT one;
149 RELEASE SAVEPOINT one;
151 CREATE TABLE savepoints (a int);
153 INSERT INTO savepoints VALUES (1);
155 INSERT INTO savepoints VALUES (2);
157 INSERT INTO savepoints VALUES (3);
158 ROLLBACK TO SAVEPOINT five;
160 COMMIT; -- should not be in a transaction block
161 SELECT * FROM savepoints;
163 -- test whole-tree rollback
166 DELETE FROM savepoints WHERE a=1;
167 RELEASE SAVEPOINT one;
169 DELETE FROM savepoints WHERE a=1;
171 DELETE FROM savepoints WHERE a=2;
173 COMMIT; -- should not be in a transaction block
175 SELECT * FROM savepoints;
177 -- test whole-tree commit on an aborted subtransaction
179 INSERT INTO savepoints VALUES (4);
181 INSERT INTO savepoints VALUES (5);
184 SELECT * FROM savepoints;
187 INSERT INTO savepoints VALUES (6);
189 INSERT INTO savepoints VALUES (7);
190 RELEASE SAVEPOINT one;
191 INSERT INTO savepoints VALUES (8);
193 -- rows 6 and 8 should have been created by the same xact
194 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
195 -- rows 6 and 7 should have been created by different xacts
196 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
199 INSERT INTO savepoints VALUES (9);
201 INSERT INTO savepoints VALUES (10);
202 ROLLBACK TO SAVEPOINT one;
203 INSERT INTO savepoints VALUES (11);
205 SELECT a FROM savepoints WHERE a in (9, 10, 11);
206 -- rows 9 and 11 should have been created by different xacts
207 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
210 INSERT INTO savepoints VALUES (12);
212 INSERT INTO savepoints VALUES (13);
214 INSERT INTO savepoints VALUES (14);
215 ROLLBACK TO SAVEPOINT one;
216 INSERT INTO savepoints VALUES (15);
218 INSERT INTO savepoints VALUES (16);
220 INSERT INTO savepoints VALUES (17);
222 SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
225 INSERT INTO savepoints VALUES (18);
227 INSERT INTO savepoints VALUES (19);
229 INSERT INTO savepoints VALUES (20);
230 ROLLBACK TO SAVEPOINT one;
231 INSERT INTO savepoints VALUES (21);
232 ROLLBACK TO SAVEPOINT one;
233 INSERT INTO savepoints VALUES (22);
235 SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
237 DROP TABLE savepoints;
239 -- only in a transaction block:
241 ROLLBACK TO SAVEPOINT one;
242 RELEASE SAVEPOINT one;
244 -- Only "rollback to" allowed in aborted state
248 SAVEPOINT two; -- ignored till the end of ...
249 RELEASE SAVEPOINT one; -- ignored till the end of ...
250 ROLLBACK TO SAVEPOINT one;
253 SELECT 1; -- this should work
255 -- check non-transactional behavior of cursors
257 DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
260 ROLLBACK TO SAVEPOINT one;
262 RELEASE SAVEPOINT one;
265 DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
268 ROLLBACK TO SAVEPOINT two;
269 -- c is now dead to the world ...
271 ROLLBACK TO SAVEPOINT two;
272 RELEASE SAVEPOINT two;
277 -- Check that "stable" functions are really stable. They should not be
278 -- able to see the partial results of the calling query. (Ideally we would
279 -- also check that they don't see commits of concurrent transactions, but
280 -- that's a mite hard to do within the limitations of pg_regress.)
282 select * from xacttest;
284 create or replace function max_xacttest() returns smallint language sql as
285 'select max(a) from xacttest' stable;
288 update xacttest set a = max_xacttest() + 10 where a > 0;
289 select * from xacttest;
292 -- But a volatile function can see the partial results of the calling query
293 create or replace function max_xacttest() returns smallint language sql as
294 'select max(a) from xacttest' volatile;
297 update xacttest set a = max_xacttest() + 10 where a > 0;
298 select * from xacttest;
301 -- Now the same test with plpgsql (since it depends on SPI which is different)
302 create or replace function max_xacttest() returns smallint language plpgsql as
303 'begin return max(a) from xacttest; end' stable;
306 update xacttest set a = max_xacttest() + 10 where a > 0;
307 select * from xacttest;
310 create or replace function max_xacttest() returns smallint language plpgsql as
311 'begin return max(a) from xacttest; end' volatile;
314 update xacttest set a = max_xacttest() + 10 where a > 0;
315 select * from xacttest;
319 -- test case for problems with dropping an open relation during abort
322 CREATE TABLE koju (a INT UNIQUE);
323 INSERT INTO koju VALUES (1);
324 INSERT INTO koju VALUES (1);
327 CREATE TABLE koju (a INT UNIQUE);
328 INSERT INTO koju VALUES (1);
329 INSERT INTO koju VALUES (1);
332 DROP TABLE trans_foo;
333 DROP TABLE trans_baz;
334 DROP TABLE trans_barbaz;
337 -- test case for problems with revalidating an open relation during abort
338 create function inverse(int) returns float8 as
341 analyze revalidate_bug;
344 when division_by_zero then return 0;
345 end$$ language plpgsql volatile;
347 create table revalidate_bug (c float8 unique);
348 insert into revalidate_bug values (1);
349 insert into revalidate_bug values (inverse(0));
351 drop table revalidate_bug;
352 drop function inverse(int);
355 -- verify that cursors created during an aborted subtransaction are
356 -- closed, but that we do not rollback the effect of any FETCHs
357 -- performed in the aborted subtransaction
361 create table trans_abc (a int);
362 insert into trans_abc values (5);
363 insert into trans_abc values (10);
364 declare foo cursor for select * from trans_abc;
374 create table trans_abc (a int);
375 insert into trans_abc values (5);
376 insert into trans_abc values (10);
377 insert into trans_abc values (15);
378 declare foo cursor for select * from trans_abc;
391 -- Test for proper cleanup after a failure in a cursor portal
392 -- that was created in an outer subtransaction
393 CREATE FUNCTION invert(x float8) RETURNS float8 LANGUAGE plpgsql AS
394 $$ begin return 1/x; end $$;
396 CREATE FUNCTION create_temp_tab() RETURNS text
397 LANGUAGE plpgsql AS $$
399 CREATE TEMP TABLE new_table (f1 float8);
400 -- case of interest is that we fail while holding an open
401 -- relcache reference to new_table
402 INSERT INTO new_table SELECT invert(0.0);
407 DECLARE ok CURSOR FOR SELECT * FROM int8_tbl;
408 DECLARE ctt CURSOR FOR SELECT create_temp_tab();
411 FETCH ok; -- should work
412 FETCH ctt; -- error occurs here
414 FETCH ok; -- should work
415 FETCH ctt; -- must be rejected
418 DROP FUNCTION create_temp_tab();
419 DROP FUNCTION invert(x float8);
422 -- Tests for AND CHAIN
424 CREATE TABLE trans_abc (a int);
426 -- set nondefault value so we have something to override below
427 SET default_transaction_read_only = on;
429 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
430 SHOW transaction_isolation;
431 SHOW transaction_read_only;
432 SHOW transaction_deferrable;
433 INSERT INTO trans_abc VALUES (1);
434 INSERT INTO trans_abc VALUES (2);
435 COMMIT AND CHAIN; -- TBLOCK_END
436 SHOW transaction_isolation;
437 SHOW transaction_read_only;
438 SHOW transaction_deferrable;
439 INSERT INTO trans_abc VALUES ('error');
440 INSERT INTO trans_abc VALUES (3); -- check it's really aborted
441 COMMIT AND CHAIN; -- TBLOCK_ABORT_END
442 SHOW transaction_isolation;
443 SHOW transaction_read_only;
444 SHOW transaction_deferrable;
445 INSERT INTO trans_abc VALUES (4);
448 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
449 SHOW transaction_isolation;
450 SHOW transaction_read_only;
451 SHOW transaction_deferrable;
453 INSERT INTO trans_abc VALUES ('error');
454 COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
455 SHOW transaction_isolation;
456 SHOW transaction_read_only;
457 SHOW transaction_deferrable;
458 INSERT INTO trans_abc VALUES (5);
461 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
462 SHOW transaction_isolation;
463 SHOW transaction_read_only;
464 SHOW transaction_deferrable;
466 COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT
467 SHOW transaction_isolation;
468 SHOW transaction_read_only;
469 SHOW transaction_deferrable;
472 START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE, DEFERRABLE;
473 SHOW transaction_isolation;
474 SHOW transaction_read_only;
475 SHOW transaction_deferrable;
477 COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT
478 SHOW transaction_isolation;
479 SHOW transaction_read_only;
480 SHOW transaction_deferrable;
483 -- different mix of options just for fun
484 START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE;
485 SHOW transaction_isolation;
486 SHOW transaction_read_only;
487 SHOW transaction_deferrable;
488 INSERT INTO trans_abc VALUES (6);
489 ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
490 SHOW transaction_isolation;
491 SHOW transaction_read_only;
492 SHOW transaction_deferrable;
493 INSERT INTO trans_abc VALUES ('error');
494 ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
495 SHOW transaction_isolation;
496 SHOW transaction_read_only;
497 SHOW transaction_deferrable;
500 -- not allowed outside a transaction block
501 COMMIT AND CHAIN; -- error
502 ROLLBACK AND CHAIN; -- error
504 SELECT * FROM trans_abc ORDER BY 1;
506 RESET default_transaction_read_only;
508 DROP TABLE trans_abc;
511 -- Test assorted behaviors around the implicit transaction block created
512 -- when multiple SQL commands are sent in a single Query message. These
513 -- tests rely on the fact that psql will not break SQL commands apart at a
514 -- backslash-quoted semicolon, but will send them as one Query.
516 create temp table i_table (f1 int);
518 -- psql will show only the last result in a multi-statement Query
519 SELECT 1\; SELECT 2\; SELECT 3;
521 -- this implicitly commits:
522 insert into i_table values(1)\; select * from i_table;
523 -- 1/0 error will cause rolling back the whole implicit transaction
524 insert into i_table values(2)\; select * from i_table\; select 1/0;
525 select * from i_table;
527 rollback; -- we are not in a transaction at this point
529 -- can use regular begin/commit/rollback within a single Query
530 begin\; insert into i_table values(3)\; commit;
531 rollback; -- we are not in a transaction at this point
532 begin\; insert into i_table values(4)\; rollback;
533 rollback; -- we are not in a transaction at this point
535 -- begin converts implicit transaction into a regular one that
536 -- can extend past the end of the Query
537 select 1\; begin\; insert into i_table values(5);
539 select 1\; begin\; insert into i_table values(6);
542 -- commit in implicit-transaction state commits but issues a warning.
543 insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0;
544 -- similarly, rollback aborts but issues a warning.
545 insert into i_table values(9)\; rollback\; select 2;
547 select * from i_table;
549 rollback; -- we are not in a transaction at this point
551 -- implicit transaction block is still a transaction block, for e.g. VACUUM
553 SELECT 1\; COMMIT\; VACUUM;
555 -- we disallow savepoint-related commands in implicit-transaction state
556 SELECT 1\; SAVEPOINT sp;
557 SELECT 1\; COMMIT\; SAVEPOINT sp;
558 ROLLBACK TO SAVEPOINT sp\; SELECT 2;
559 SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
561 -- but this is OK, because the BEGIN converts it to a regular xact
562 SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
565 -- Tests for AND CHAIN in implicit transaction blocks
567 SET TRANSACTION READ ONLY\; COMMIT AND CHAIN; -- error
568 SHOW transaction_read_only;
570 SET TRANSACTION READ ONLY\; ROLLBACK AND CHAIN; -- error
571 SHOW transaction_read_only;
573 CREATE TABLE trans_abc (a int);
575 -- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
576 INSERT INTO trans_abc VALUES (7)\; COMMIT\; INSERT INTO trans_abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error
577 INSERT INTO trans_abc VALUES (9)\; ROLLBACK\; INSERT INTO trans_abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
579 -- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
580 INSERT INTO trans_abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO trans_abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached
581 INSERT INTO trans_abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO trans_abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
583 -- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
584 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
585 SHOW transaction_isolation; -- transaction is active at this point
588 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
589 SHOW transaction_isolation; -- transaction is active at this point
592 -- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
593 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (17)\; COMMIT\; INSERT INTO trans_abc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error
594 SHOW transaction_isolation; -- out of transaction block
596 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (19)\; ROLLBACK\; INSERT INTO trans_abc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error
597 SHOW transaction_isolation; -- out of transaction block
599 SELECT * FROM trans_abc ORDER BY 1;
601 DROP TABLE trans_abc;
604 -- Test for successful cleanup of an aborted transaction at session exit.
605 -- THIS MUST BE THE LAST TEST IN THIS FILE.
611 -- DO NOT ADD ANYTHING HERE.