| blob | 61862d595d1abec90127bb56a4ff17f2581c750c |
1 --
2 -- TRANSACTIONS
3 --
4 BEGIN;
5 SELECT *
6 INTO TABLE xacttest
7 FROM aggtest;
8 INSERT INTO xacttest (a, b) VALUES (777, 777.777);
9 END;
10 -- should retrieve one value--
11 SELECT a FROM xacttest WHERE a > 100;
12 a
13 -----
14 777
15 (1 row)
17 BEGIN;
18 CREATE TABLE disappear (a int4);
19 DELETE FROM aggtest;
20 -- should be empty
21 SELECT * FROM aggtest;
22 a | b
23 ---+---
24 (0 rows)
26 ABORT;
27 -- should not exist
28 SELECT oid FROM pg_class WHERE relname = 'disappear';
29 oid
30 -----
31 (0 rows)
33 -- should have members again
34 SELECT * FROM aggtest;
35 a | b
36 -----+---------
37 56 | 7.8
38 100 | 99.097
39 0 | 0.09561
40 42 | 324.78
41 (4 rows)
43 -- Read-only tests
44 CREATE TABLE writetest (a int);
45 CREATE TEMPORARY TABLE temptest (a int);
46 BEGIN;
47 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- ok
48 SELECT * FROM writetest; -- ok
49 a
50 ---
51 (0 rows)
53 SET TRANSACTION READ WRITE; --fail
54 ERROR: transaction read-write mode must be set before any query
55 COMMIT;
56 BEGIN;
57 SET TRANSACTION READ ONLY; -- ok
58 SET TRANSACTION READ WRITE; -- ok
59 SET TRANSACTION READ ONLY; -- ok
60 SELECT * FROM writetest; -- ok
61 a
62 ---
63 (0 rows)
65 SAVEPOINT x;
66 SET TRANSACTION READ ONLY; -- ok
67 SELECT * FROM writetest; -- ok
68 a
69 ---
70 (0 rows)
72 SET TRANSACTION READ ONLY; -- ok
73 SET TRANSACTION READ WRITE; --fail
74 ERROR: cannot set transaction read-write mode inside a read-only transaction
75 COMMIT;
76 BEGIN;
77 SET TRANSACTION READ WRITE; -- ok
78 SAVEPOINT x;
79 SET TRANSACTION READ WRITE; -- ok
80 SET TRANSACTION READ ONLY; -- ok
81 SELECT * FROM writetest; -- ok
82 a
83 ---
84 (0 rows)
86 SET TRANSACTION READ ONLY; -- ok
87 SET TRANSACTION READ WRITE; --fail
88 ERROR: cannot set transaction read-write mode inside a read-only transaction
89 COMMIT;
90 BEGIN;
91 SET TRANSACTION READ WRITE; -- ok
92 SAVEPOINT x;
93 SET TRANSACTION READ ONLY; -- ok
94 SELECT * FROM writetest; -- ok
95 a
96 ---
97 (0 rows)
99 ROLLBACK TO SAVEPOINT x;
100 SHOW transaction_read_only; -- off
101 transaction_read_only
102 -----------------------
103 off
104 (1 row)
106 SAVEPOINT y;
107 SET TRANSACTION READ ONLY; -- ok
108 SELECT * FROM writetest; -- ok
109 a
110 ---
111 (0 rows)
113 RELEASE SAVEPOINT y;
114 SHOW transaction_read_only; -- off
115 transaction_read_only
116 -----------------------
117 off
118 (1 row)
120 COMMIT;
121 SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
122 DROP TABLE writetest; -- fail
123 ERROR: cannot execute DROP TABLE in a read-only transaction
124 INSERT INTO writetest VALUES (1); -- fail
125 ERROR: cannot execute INSERT in a read-only transaction
126 SELECT * FROM writetest; -- ok
127 a
128 ---
129 (0 rows)
131 DELETE FROM temptest; -- ok
132 UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok
133 PREPARE test AS UPDATE writetest SET a = 0; -- ok
134 EXECUTE test; -- fail
135 ERROR: cannot execute UPDATE in a read-only transaction
136 SELECT * FROM writetest, temptest; -- ok
137 a | a
138 ---+---
139 (0 rows)
141 CREATE TABLE test AS SELECT * FROM writetest; -- fail
142 ERROR: cannot execute CREATE TABLE AS in a read-only transaction
143 START TRANSACTION READ WRITE;
144 DROP TABLE writetest; -- ok
145 COMMIT;
146 -- Subtransactions, basic tests
147 -- create & drop tables
148 SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
149 CREATE TABLE trans_foobar (a int);
150 BEGIN;
151 CREATE TABLE trans_foo (a int);
152 SAVEPOINT one;
153 DROP TABLE trans_foo;
154 CREATE TABLE trans_bar (a int);
155 ROLLBACK TO SAVEPOINT one;
156 RELEASE SAVEPOINT one;
157 SAVEPOINT two;
158 CREATE TABLE trans_baz (a int);
159 RELEASE SAVEPOINT two;
160 drop TABLE trans_foobar;
161 CREATE TABLE trans_barbaz (a int);
162 COMMIT;
163 -- should exist: trans_barbaz, trans_baz, trans_foo
164 SELECT * FROM trans_foo; -- should be empty
165 a
166 ---
167 (0 rows)
169 SELECT * FROM trans_bar; -- shouldn't exist
170 ERROR: relation "trans_bar" does not exist
171 LINE 1: SELECT * FROM trans_bar;
172 ^
173 SELECT * FROM trans_barbaz; -- should be empty
174 a
175 ---
176 (0 rows)
178 SELECT * FROM trans_baz; -- should be empty
179 a
180 ---
181 (0 rows)
183 -- inserts
184 BEGIN;
185 INSERT INTO trans_foo VALUES (1);
186 SAVEPOINT one;
187 INSERT into trans_bar VALUES (1);
188 ERROR: relation "trans_bar" does not exist
189 LINE 1: INSERT into trans_bar VALUES (1);
190 ^
191 ROLLBACK TO one;
192 RELEASE SAVEPOINT one;
193 SAVEPOINT two;
194 INSERT into trans_barbaz VALUES (1);
195 RELEASE two;
196 SAVEPOINT three;
197 SAVEPOINT four;
198 INSERT INTO trans_foo VALUES (2);
199 RELEASE SAVEPOINT four;
200 ROLLBACK TO SAVEPOINT three;
201 RELEASE SAVEPOINT three;
202 INSERT INTO trans_foo VALUES (3);
203 COMMIT;
204 SELECT * FROM trans_foo; -- should have 1 and 3
205 a
206 ---
207 1
208 3
209 (2 rows)
211 SELECT * FROM trans_barbaz; -- should have 1
212 a
213 ---
214 1
215 (1 row)
217 -- test whole-tree commit
218 BEGIN;
219 SAVEPOINT one;
220 SELECT trans_foo;
221 ERROR: column "trans_foo" does not exist
222 LINE 1: SELECT trans_foo;
223 ^
224 ROLLBACK TO SAVEPOINT one;
225 RELEASE SAVEPOINT one;
226 SAVEPOINT two;
227 CREATE TABLE savepoints (a int);
228 SAVEPOINT three;
229 INSERT INTO savepoints VALUES (1);
230 SAVEPOINT four;
231 INSERT INTO savepoints VALUES (2);
232 SAVEPOINT five;
233 INSERT INTO savepoints VALUES (3);
234 ROLLBACK TO SAVEPOINT five;
235 COMMIT;
236 COMMIT; -- should not be in a transaction block
237 WARNING: there is no transaction in progress
238 SELECT * FROM savepoints;
239 a
240 ---
241 1
242 2
243 (2 rows)
245 -- test whole-tree rollback
246 BEGIN;
247 SAVEPOINT one;
248 DELETE FROM savepoints WHERE a=1;
249 RELEASE SAVEPOINT one;
250 SAVEPOINT two;
251 DELETE FROM savepoints WHERE a=1;
252 SAVEPOINT three;
253 DELETE FROM savepoints WHERE a=2;
254 ROLLBACK;
255 COMMIT; -- should not be in a transaction block
256 WARNING: there is no transaction in progress
257 SELECT * FROM savepoints;
258 a
259 ---
260 1
261 2
262 (2 rows)
264 -- test whole-tree commit on an aborted subtransaction
265 BEGIN;
266 INSERT INTO savepoints VALUES (4);
267 SAVEPOINT one;
268 INSERT INTO savepoints VALUES (5);
269 SELECT trans_foo;
270 ERROR: column "trans_foo" does not exist
271 LINE 1: SELECT trans_foo;
272 ^
273 COMMIT;
274 SELECT * FROM savepoints;
275 a
276 ---
277 1
278 2
279 (2 rows)
281 BEGIN;
282 INSERT INTO savepoints VALUES (6);
283 SAVEPOINT one;
284 INSERT INTO savepoints VALUES (7);
285 RELEASE SAVEPOINT one;
286 INSERT INTO savepoints VALUES (8);
287 COMMIT;
288 -- rows 6 and 8 should have been created by the same xact
289 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
290 ?column?
291 ----------
292 t
293 (1 row)
295 -- rows 6 and 7 should have been created by different xacts
296 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
297 ?column?
298 ----------
299 f
300 (1 row)
302 BEGIN;
303 INSERT INTO savepoints VALUES (9);
304 SAVEPOINT one;
305 INSERT INTO savepoints VALUES (10);
306 ROLLBACK TO SAVEPOINT one;
307 INSERT INTO savepoints VALUES (11);
308 COMMIT;
309 SELECT a FROM savepoints WHERE a in (9, 10, 11);
310 a
311 ----
312 9
313 11
314 (2 rows)
316 -- rows 9 and 11 should have been created by different xacts
317 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
318 ?column?
319 ----------
320 f
321 (1 row)
323 BEGIN;
324 INSERT INTO savepoints VALUES (12);
325 SAVEPOINT one;
326 INSERT INTO savepoints VALUES (13);
327 SAVEPOINT two;
328 INSERT INTO savepoints VALUES (14);
329 ROLLBACK TO SAVEPOINT one;
330 INSERT INTO savepoints VALUES (15);
331 SAVEPOINT two;
332 INSERT INTO savepoints VALUES (16);
333 SAVEPOINT three;
334 INSERT INTO savepoints VALUES (17);
335 COMMIT;
336 SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
337 a
338 ----
339 12
340 15
341 16
342 17
343 (4 rows)
345 BEGIN;
346 INSERT INTO savepoints VALUES (18);
347 SAVEPOINT one;
348 INSERT INTO savepoints VALUES (19);
349 SAVEPOINT two;
350 INSERT INTO savepoints VALUES (20);
351 ROLLBACK TO SAVEPOINT one;
352 INSERT INTO savepoints VALUES (21);
353 ROLLBACK TO SAVEPOINT one;
354 INSERT INTO savepoints VALUES (22);
355 COMMIT;
356 SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
357 a
358 ----
359 18
360 22
361 (2 rows)
363 DROP TABLE savepoints;
364 -- only in a transaction block:
365 SAVEPOINT one;
366 ERROR: SAVEPOINT can only be used in transaction blocks
367 ROLLBACK TO SAVEPOINT one;
368 ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
369 RELEASE SAVEPOINT one;
370 ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
371 -- Only "rollback to" allowed in aborted state
372 BEGIN;
373 SAVEPOINT one;
374 SELECT 0/0;
375 ERROR: division by zero
376 SAVEPOINT two; -- ignored till the end of ...
377 ERROR: current transaction is aborted, commands ignored until end of transaction block
378 RELEASE SAVEPOINT one; -- ignored till the end of ...
379 ERROR: current transaction is aborted, commands ignored until end of transaction block
380 ROLLBACK TO SAVEPOINT one;
381 SELECT 1;
382 ?column?
383 ----------
384 1
385 (1 row)
387 COMMIT;
388 SELECT 1; -- this should work
389 ?column?
390 ----------
391 1
392 (1 row)
394 -- check non-transactional behavior of cursors
395 BEGIN;
396 DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
397 SAVEPOINT one;
398 FETCH 10 FROM c;
399 unique2
400 ---------
401 0
402 1
403 2
404 3
405 4
406 5
407 6
408 7
409 8
410 9
411 (10 rows)
413 ROLLBACK TO SAVEPOINT one;
414 FETCH 10 FROM c;
415 unique2
416 ---------
417 10
418 11
419 12
420 13
421 14
422 15
423 16
424 17
425 18
426 19
427 (10 rows)
429 RELEASE SAVEPOINT one;
430 FETCH 10 FROM c;
431 unique2
432 ---------
433 20
434 21
435 22
436 23
437 24
438 25
439 26
440 27
441 28
442 29
443 (10 rows)
445 CLOSE c;
446 DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
447 SAVEPOINT two;
448 FETCH 10 FROM c;
449 ERROR: division by zero
450 ROLLBACK TO SAVEPOINT two;
451 -- c is now dead to the world ...
452 FETCH 10 FROM c;
453 ERROR: portal "c" cannot be run
454 ROLLBACK TO SAVEPOINT two;
455 RELEASE SAVEPOINT two;
456 FETCH 10 FROM c;
457 ERROR: portal "c" cannot be run
458 COMMIT;
459 --
460 -- Check that "stable" functions are really stable. They should not be
461 -- able to see the partial results of the calling query. (Ideally we would
462 -- also check that they don't see commits of concurrent transactions, but
463 -- that's a mite hard to do within the limitations of pg_regress.)
464 --
465 select * from xacttest;
466 a | b
467 -----+---------
468 56 | 7.8
469 100 | 99.097
470 0 | 0.09561
471 42 | 324.78
472 777 | 777.777
473 (5 rows)
475 create or replace function max_xacttest() returns smallint language sql as
476 'select max(a) from xacttest' stable;
477 begin;
478 update xacttest set a = max_xacttest() + 10 where a > 0;
479 select * from xacttest;
480 a | b
481 -----+---------
482 0 | 0.09561
483 787 | 7.8
484 787 | 99.097
485 787 | 324.78
486 787 | 777.777
487 (5 rows)
489 rollback;
490 -- But a volatile function can see the partial results of the calling query
491 create or replace function max_xacttest() returns smallint language sql as
492 'select max(a) from xacttest' volatile;
493 begin;
494 update xacttest set a = max_xacttest() + 10 where a > 0;
495 select * from xacttest;
496 a | b
497 -----+---------
498 0 | 0.09561
499 787 | 7.8
500 797 | 99.097
501 807 | 324.78
502 817 | 777.777
503 (5 rows)
505 rollback;
506 -- Now the same test with plpgsql (since it depends on SPI which is different)
507 create or replace function max_xacttest() returns smallint language plpgsql as
508 'begin return max(a) from xacttest; end' stable;
509 begin;
510 update xacttest set a = max_xacttest() + 10 where a > 0;
511 select * from xacttest;
512 a | b
513 -----+---------
514 0 | 0.09561
515 787 | 7.8
516 787 | 99.097
517 787 | 324.78
518 787 | 777.777
519 (5 rows)
521 rollback;
522 create or replace function max_xacttest() returns smallint language plpgsql as
523 'begin return max(a) from xacttest; end' volatile;
524 begin;
525 update xacttest set a = max_xacttest() + 10 where a > 0;
526 select * from xacttest;
527 a | b
528 -----+---------
529 0 | 0.09561
530 787 | 7.8
531 797 | 99.097
532 807 | 324.78
533 817 | 777.777
534 (5 rows)
536 rollback;
537 -- test case for problems with dropping an open relation during abort
538 BEGIN;
539 savepoint x;
540 CREATE TABLE koju (a INT UNIQUE);
541 INSERT INTO koju VALUES (1);
542 INSERT INTO koju VALUES (1);
543 ERROR: duplicate key value violates unique constraint "koju_a_key"
544 DETAIL: Key (a)=(1) already exists.
545 rollback to x;
546 CREATE TABLE koju (a INT UNIQUE);
547 INSERT INTO koju VALUES (1);
548 INSERT INTO koju VALUES (1);
549 ERROR: duplicate key value violates unique constraint "koju_a_key"
550 DETAIL: Key (a)=(1) already exists.
551 ROLLBACK;
552 DROP TABLE trans_foo;
553 DROP TABLE trans_baz;
554 DROP TABLE trans_barbaz;
555 -- test case for problems with revalidating an open relation during abort
556 create function inverse(int) returns float8 as
557 $$
558 begin
559 analyze revalidate_bug;
560 return 1::float8/$1;
561 exception
562 when division_by_zero then return 0;
563 end$$ language plpgsql volatile;
564 create table revalidate_bug (c float8 unique);
565 insert into revalidate_bug values (1);
566 insert into revalidate_bug values (inverse(0));
567 drop table revalidate_bug;
568 drop function inverse(int);
569 -- verify that cursors created during an aborted subtransaction are
570 -- closed, but that we do not rollback the effect of any FETCHs
571 -- performed in the aborted subtransaction
572 begin;
573 savepoint x;
574 create table abc (a int);
575 insert into abc values (5);
576 insert into abc values (10);
577 declare foo cursor for select * from abc;
578 fetch from foo;
579 a
580 ---
581 5
582 (1 row)
584 rollback to x;
585 -- should fail
586 fetch from foo;
587 ERROR: cursor "foo" does not exist
588 commit;
589 begin;
590 create table abc (a int);
591 insert into abc values (5);
592 insert into abc values (10);
593 insert into abc values (15);
594 declare foo cursor for select * from abc;
595 fetch from foo;
596 a
597 ---
598 5
599 (1 row)
601 savepoint x;
602 fetch from foo;
603 a
604 ----
605 10
606 (1 row)
608 rollback to x;
609 fetch from foo;
610 a
611 ----
612 15
613 (1 row)
615 abort;
616 -- Test for proper cleanup after a failure in a cursor portal
617 -- that was created in an outer subtransaction
618 CREATE FUNCTION invert(x float8) RETURNS float8 LANGUAGE plpgsql AS
619 $$ begin return 1/x; end $$;
620 CREATE FUNCTION create_temp_tab() RETURNS text
621 LANGUAGE plpgsql AS $$
622 BEGIN
623 CREATE TEMP TABLE new_table (f1 float8);
624 -- case of interest is that we fail while holding an open
625 -- relcache reference to new_table
626 INSERT INTO new_table SELECT invert(0.0);
627 RETURN 'foo';
628 END $$;
629 BEGIN;
630 DECLARE ok CURSOR FOR SELECT * FROM int8_tbl;
631 DECLARE ctt CURSOR FOR SELECT create_temp_tab();
632 FETCH ok;
633 q1 | q2
634 -----+-----
635 123 | 456
636 (1 row)
638 SAVEPOINT s1;
639 FETCH ok; -- should work
640 q1 | q2
641 -----+------------------
642 123 | 4567890123456789
643 (1 row)
645 FETCH ctt; -- error occurs here
646 ERROR: division by zero
647 CONTEXT: PL/pgSQL function invert(double precision) line 1 at RETURN
648 SQL statement "INSERT INTO new_table SELECT invert(0.0)"
649 PL/pgSQL function create_temp_tab() line 6 at SQL statement
650 ROLLBACK TO s1;
651 FETCH ok; -- should work
652 q1 | q2
653 ------------------+-----
654 4567890123456789 | 123
655 (1 row)
657 FETCH ctt; -- must be rejected
658 ERROR: portal "ctt" cannot be run
659 COMMIT;
660 DROP FUNCTION create_temp_tab();
661 DROP FUNCTION invert(x float8);
662 -- Tests for AND CHAIN
663 CREATE TABLE abc (a int);
664 -- set nondefault value so we have something to override below
665 SET default_transaction_read_only = on;
666 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
667 SHOW transaction_isolation;
668 transaction_isolation
669 -----------------------
670 repeatable read
671 (1 row)
673 SHOW transaction_read_only;
674 transaction_read_only
675 -----------------------
676 off
677 (1 row)
679 SHOW transaction_deferrable;
680 transaction_deferrable
681 ------------------------
682 on
683 (1 row)
685 INSERT INTO abc VALUES (1);
686 INSERT INTO abc VALUES (2);
687 COMMIT AND CHAIN; -- TBLOCK_END
688 SHOW transaction_isolation;
689 transaction_isolation
690 -----------------------
691 repeatable read
692 (1 row)
694 SHOW transaction_read_only;
695 transaction_read_only
696 -----------------------
697 off
698 (1 row)
700 SHOW transaction_deferrable;
701 transaction_deferrable
702 ------------------------
703 on
704 (1 row)
706 INSERT INTO abc VALUES ('error');
707 ERROR: invalid input syntax for type integer: "error"
708 LINE 1: INSERT INTO abc VALUES ('error');
709 ^
710 INSERT INTO abc VALUES (3); -- check it's really aborted
711 ERROR: current transaction is aborted, commands ignored until end of transaction block
712 COMMIT AND CHAIN; -- TBLOCK_ABORT_END
713 SHOW transaction_isolation;
714 transaction_isolation
715 -----------------------
716 repeatable read
717 (1 row)
719 SHOW transaction_read_only;
720 transaction_read_only
721 -----------------------
722 off
723 (1 row)
725 SHOW transaction_deferrable;
726 transaction_deferrable
727 ------------------------
728 on
729 (1 row)
731 INSERT INTO abc VALUES (4);
732 COMMIT;
733 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
734 SHOW transaction_isolation;
735 transaction_isolation
736 -----------------------
737 repeatable read
738 (1 row)
740 SHOW transaction_read_only;
741 transaction_read_only
742 -----------------------
743 off
744 (1 row)
746 SHOW transaction_deferrable;
747 transaction_deferrable
748 ------------------------
749 on
750 (1 row)
752 SAVEPOINT x;
753 INSERT INTO abc VALUES ('error');
754 ERROR: invalid input syntax for type integer: "error"
755 LINE 1: INSERT INTO abc VALUES ('error');
756 ^
757 COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
758 SHOW transaction_isolation;
759 transaction_isolation
760 -----------------------
761 repeatable read
762 (1 row)
764 SHOW transaction_read_only;
765 transaction_read_only
766 -----------------------
767 off
768 (1 row)
770 SHOW transaction_deferrable;
771 transaction_deferrable
772 ------------------------
773 on
774 (1 row)
776 INSERT INTO abc VALUES (5);
777 COMMIT;
778 START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
779 SHOW transaction_isolation;
780 transaction_isolation
781 -----------------------
782 repeatable read
783 (1 row)
785 SHOW transaction_read_only;
786 transaction_read_only
787 -----------------------
788 off
789 (1 row)
791 SHOW transaction_deferrable;
792 transaction_deferrable
793 ------------------------
794 on
795 (1 row)
797 SAVEPOINT x;
798 COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT
799 SHOW transaction_isolation;
800 transaction_isolation
801 -----------------------
802 repeatable read
803 (1 row)
805 SHOW transaction_read_only;
806 transaction_read_only
807 -----------------------
808 off
809 (1 row)
811 SHOW transaction_deferrable;
812 transaction_deferrable
813 ------------------------
814 on
815 (1 row)
817 COMMIT;
818 -- different mix of options just for fun
819 START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE;
820 SHOW transaction_isolation;
821 transaction_isolation
822 -----------------------
823 serializable
824 (1 row)
826 SHOW transaction_read_only;
827 transaction_read_only
828 -----------------------
829 off
830 (1 row)
832 SHOW transaction_deferrable;
833 transaction_deferrable
834 ------------------------
835 off
836 (1 row)
838 INSERT INTO abc VALUES (6);
839 ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
840 SHOW transaction_isolation;
841 transaction_isolation
842 -----------------------
843 serializable
844 (1 row)
846 SHOW transaction_read_only;
847 transaction_read_only
848 -----------------------
849 off
850 (1 row)
852 SHOW transaction_deferrable;
853 transaction_deferrable
854 ------------------------
855 off
856 (1 row)
858 INSERT INTO abc VALUES ('error');
859 ERROR: invalid input syntax for type integer: "error"
860 LINE 1: INSERT INTO abc VALUES ('error');
861 ^
862 ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
863 SHOW transaction_isolation;
864 transaction_isolation
865 -----------------------
866 serializable
867 (1 row)
869 SHOW transaction_read_only;
870 transaction_read_only
871 -----------------------
872 off
873 (1 row)
875 SHOW transaction_deferrable;
876 transaction_deferrable
877 ------------------------
878 off
879 (1 row)
881 ROLLBACK;
882 -- not allowed outside a transaction block
883 COMMIT AND CHAIN; -- error
884 ERROR: COMMIT AND CHAIN can only be used in transaction blocks
885 ROLLBACK AND CHAIN; -- error
886 ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
887 SELECT * FROM abc ORDER BY 1;
888 a
889 ---
890 1
891 2
892 4
893 5
894 (4 rows)
896 RESET default_transaction_read_only;
897 DROP TABLE abc;
898 -- Test assorted behaviors around the implicit transaction block created
899 -- when multiple SQL commands are sent in a single Query message. These
900 -- tests rely on the fact that psql will not break SQL commands apart at a
901 -- backslash-quoted semicolon, but will send them as one Query.
902 create temp table i_table (f1 int);
903 -- psql will show only the last result in a multi-statement Query
904 SELECT 1\; SELECT 2\; SELECT 3;
905 ?column?
906 ----------
907 3
908 (1 row)
910 -- this implicitly commits:
911 insert into i_table values(1)\; select * from i_table;
912 f1
913 ----
914 1
915 (1 row)
917 -- 1/0 error will cause rolling back the whole implicit transaction
918 insert into i_table values(2)\; select * from i_table\; select 1/0;
919 ERROR: division by zero
920 select * from i_table;
921 f1
922 ----
923 1
924 (1 row)
926 rollback; -- we are not in a transaction at this point
927 WARNING: there is no transaction in progress
928 -- can use regular begin/commit/rollback within a single Query
929 begin\; insert into i_table values(3)\; commit;
930 rollback; -- we are not in a transaction at this point
931 WARNING: there is no transaction in progress
932 begin\; insert into i_table values(4)\; rollback;
933 rollback; -- we are not in a transaction at this point
934 WARNING: there is no transaction in progress
935 -- begin converts implicit transaction into a regular one that
936 -- can extend past the end of the Query
937 select 1\; begin\; insert into i_table values(5);
938 commit;
939 select 1\; begin\; insert into i_table values(6);
940 rollback;
941 -- commit in implicit-transaction state commits but issues a warning.
942 insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0;
943 WARNING: there is no transaction in progress
944 ERROR: division by zero
945 -- similarly, rollback aborts but issues a warning.
946 insert into i_table values(9)\; rollback\; select 2;
947 WARNING: there is no transaction in progress
948 ?column?
949 ----------
950 2
951 (1 row)
953 select * from i_table;
954 f1
955 ----
956 1
957 3
958 5
959 7
960 (4 rows)
962 rollback; -- we are not in a transaction at this point
963 WARNING: there is no transaction in progress
964 -- implicit transaction block is still a transaction block, for e.g. VACUUM
965 SELECT 1\; VACUUM;
966 ERROR: VACUUM cannot run inside a transaction block
967 SELECT 1\; COMMIT\; VACUUM;
968 WARNING: there is no transaction in progress
969 ERROR: VACUUM cannot run inside a transaction block
970 -- we disallow savepoint-related commands in implicit-transaction state
971 SELECT 1\; SAVEPOINT sp;
972 ERROR: SAVEPOINT can only be used in transaction blocks
973 SELECT 1\; COMMIT\; SAVEPOINT sp;
974 WARNING: there is no transaction in progress
975 ERROR: SAVEPOINT can only be used in transaction blocks
976 ROLLBACK TO SAVEPOINT sp\; SELECT 2;
977 ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
978 SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
979 ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
980 -- but this is OK, because the BEGIN converts it to a regular xact
981 SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
982 -- Tests for AND CHAIN in implicit transaction blocks
983 SET TRANSACTION READ ONLY\; COMMIT AND CHAIN; -- error
984 ERROR: COMMIT AND CHAIN can only be used in transaction blocks
985 SHOW transaction_read_only;
986 transaction_read_only
987 -----------------------
988 off
989 (1 row)
991 SET TRANSACTION READ ONLY\; ROLLBACK AND CHAIN; -- error
992 ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
993 SHOW transaction_read_only;
994 transaction_read_only
995 -----------------------
996 off
997 (1 row)
999 CREATE TABLE abc (a int);
1000 -- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
1001 INSERT INTO abc VALUES (7)\; COMMIT\; INSERT INTO abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error
1002 WARNING: there is no transaction in progress
1003 ERROR: COMMIT AND CHAIN can only be used in transaction blocks
1004 INSERT INTO abc VALUES (9)\; ROLLBACK\; INSERT INTO abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
1005 WARNING: there is no transaction in progress
1006 ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
1007 -- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
1008 INSERT INTO abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached
1009 ERROR: COMMIT AND CHAIN can only be used in transaction blocks
1010 INSERT INTO abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
1011 ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
1012 -- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
1013 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
1014 SHOW transaction_isolation; -- transaction is active at this point
1015 transaction_isolation
1016 -----------------------
1017 repeatable read
1018 (1 row)
1020 COMMIT;
1021 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
1022 SHOW transaction_isolation; -- transaction is active at this point
1023 transaction_isolation
1024 -----------------------
1025 repeatable read
1026 (1 row)
1028 ROLLBACK;
1029 SET default_transaction_isolation = 'read committed';
1030 -- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
1031 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (17)\; COMMIT\; INSERT INTO abc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error
1032 ERROR: COMMIT AND CHAIN can only be used in transaction blocks
1033 SHOW transaction_isolation; -- out of transaction block
1034 transaction_isolation
1035 -----------------------
1036 read committed
1037 (1 row)
1039 START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (19)\; ROLLBACK\; INSERT INTO abc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error
1040 ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
1041 SHOW transaction_isolation; -- out of transaction block
1042 transaction_isolation
1043 -----------------------
1044 read committed
1045 (1 row)
1047 RESET default_transaction_isolation;
1048 SELECT * FROM abc ORDER BY 1;
1049 a
1050 ----
1051 7
1052 15
1053 17
1054 (3 rows)
1056 DROP TABLE abc;
1057 -- Test for successful cleanup of an aborted transaction at session exit.
1058 -- THIS MUST BE THE LAST TEST IN THIS FILE.
1059 begin;
1060 select 1/0;
1061 ERROR: division by zero
1062 rollback to X;
1063 ERROR: savepoint "x" does not exist
1064 -- DO NOT ADD ANYTHING HERE.