summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/transactions.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/transactions.out')
-rw-r--r--src/test/regress/expected/transactions.out1198
1 files changed, 1198 insertions, 0 deletions
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
new file mode 100644
index 0000000..535f73c
--- /dev/null
+++ b/src/test/regress/expected/transactions.out
@@ -0,0 +1,1198 @@
+--
+-- TRANSACTIONS
+--
+BEGIN;
+CREATE TABLE xacttest (a smallint, b real);
+INSERT INTO xacttest VALUES
+ (56, 7.8),
+ (100, 99.097),
+ (0, 0.09561),
+ (42, 324.78);
+INSERT INTO xacttest (a, b) VALUES (777, 777.777);
+END;
+-- should retrieve one value--
+SELECT a FROM xacttest WHERE a > 100;
+ a
+-----
+ 777
+(1 row)
+
+BEGIN;
+CREATE TABLE disappear (a int4);
+DELETE FROM xacttest;
+-- should be empty
+SELECT * FROM xacttest;
+ a | b
+---+---
+(0 rows)
+
+ABORT;
+-- should not exist
+SELECT oid FROM pg_class WHERE relname = 'disappear';
+ oid
+-----
+(0 rows)
+
+-- should have members again
+SELECT * FROM xacttest;
+ a | b
+-----+---------
+ 56 | 7.8
+ 100 | 99.097
+ 0 | 0.09561
+ 42 | 324.78
+ 777 | 777.777
+(5 rows)
+
+-- Test that transaction characteristics cannot be reset.
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SELECT COUNT(*) FROM xacttest;
+ count
+-------
+ 5
+(1 row)
+
+RESET transaction_isolation; -- error
+ERROR: parameter "transaction_isolation" cannot be reset
+END;
+BEGIN TRANSACTION READ ONLY;
+SELECT COUNT(*) FROM xacttest;
+ count
+-------
+ 5
+(1 row)
+
+RESET transaction_read_only; -- error
+ERROR: parameter "transaction_read_only" cannot be reset
+END;
+BEGIN TRANSACTION DEFERRABLE;
+SELECT COUNT(*) FROM xacttest;
+ count
+-------
+ 5
+(1 row)
+
+RESET transaction_deferrable; -- error
+ERROR: parameter "transaction_deferrable" cannot be reset
+END;
+CREATE FUNCTION errfunc() RETURNS int LANGUAGE SQL AS 'SELECT 1'
+SET transaction_read_only = on; -- error
+ERROR: parameter "transaction_read_only" cannot be set locally in functions
+-- Read-only tests
+CREATE TABLE writetest (a int);
+CREATE TEMPORARY TABLE temptest (a int);
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+SET TRANSACTION READ WRITE; --fail
+ERROR: transaction read-write mode must be set before any query
+COMMIT;
+BEGIN;
+SET TRANSACTION READ ONLY; -- ok
+SET TRANSACTION READ WRITE; -- ok
+SET TRANSACTION READ ONLY; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+SAVEPOINT x;
+SET TRANSACTION READ ONLY; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+SET TRANSACTION READ ONLY; -- ok
+SET TRANSACTION READ WRITE; --fail
+ERROR: cannot set transaction read-write mode inside a read-only transaction
+COMMIT;
+BEGIN;
+SET TRANSACTION READ WRITE; -- ok
+SAVEPOINT x;
+SET TRANSACTION READ WRITE; -- ok
+SET TRANSACTION READ ONLY; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+SET TRANSACTION READ ONLY; -- ok
+SET TRANSACTION READ WRITE; --fail
+ERROR: cannot set transaction read-write mode inside a read-only transaction
+COMMIT;
+BEGIN;
+SET TRANSACTION READ WRITE; -- ok
+SAVEPOINT x;
+SET TRANSACTION READ ONLY; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+ROLLBACK TO SAVEPOINT x;
+SHOW transaction_read_only; -- off
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SAVEPOINT y;
+SET TRANSACTION READ ONLY; -- ok
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+RELEASE SAVEPOINT y;
+SHOW transaction_read_only; -- off
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+COMMIT;
+SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
+DROP TABLE writetest; -- fail
+ERROR: cannot execute DROP TABLE in a read-only transaction
+INSERT INTO writetest VALUES (1); -- fail
+ERROR: cannot execute INSERT in a read-only transaction
+SELECT * FROM writetest; -- ok
+ a
+---
+(0 rows)
+
+DELETE FROM temptest; -- ok
+UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok
+PREPARE test AS UPDATE writetest SET a = 0; -- ok
+EXECUTE test; -- fail
+ERROR: cannot execute UPDATE in a read-only transaction
+SELECT * FROM writetest, temptest; -- ok
+ a | a
+---+---
+(0 rows)
+
+CREATE TABLE test AS SELECT * FROM writetest; -- fail
+ERROR: cannot execute CREATE TABLE AS in a read-only transaction
+START TRANSACTION READ WRITE;
+DROP TABLE writetest; -- ok
+COMMIT;
+-- Subtransactions, basic tests
+-- create & drop tables
+SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
+CREATE TABLE trans_foobar (a int);
+BEGIN;
+ CREATE TABLE trans_foo (a int);
+ SAVEPOINT one;
+ DROP TABLE trans_foo;
+ CREATE TABLE trans_bar (a int);
+ ROLLBACK TO SAVEPOINT one;
+ RELEASE SAVEPOINT one;
+ SAVEPOINT two;
+ CREATE TABLE trans_baz (a int);
+ RELEASE SAVEPOINT two;
+ drop TABLE trans_foobar;
+ CREATE TABLE trans_barbaz (a int);
+COMMIT;
+-- should exist: trans_barbaz, trans_baz, trans_foo
+SELECT * FROM trans_foo; -- should be empty
+ a
+---
+(0 rows)
+
+SELECT * FROM trans_bar; -- shouldn't exist
+ERROR: relation "trans_bar" does not exist
+LINE 1: SELECT * FROM trans_bar;
+ ^
+SELECT * FROM trans_barbaz; -- should be empty
+ a
+---
+(0 rows)
+
+SELECT * FROM trans_baz; -- should be empty
+ a
+---
+(0 rows)
+
+-- inserts
+BEGIN;
+ INSERT INTO trans_foo VALUES (1);
+ SAVEPOINT one;
+ INSERT into trans_bar VALUES (1);
+ERROR: relation "trans_bar" does not exist
+LINE 1: INSERT into trans_bar VALUES (1);
+ ^
+ ROLLBACK TO one;
+ RELEASE SAVEPOINT one;
+ SAVEPOINT two;
+ INSERT into trans_barbaz VALUES (1);
+ RELEASE two;
+ SAVEPOINT three;
+ SAVEPOINT four;
+ INSERT INTO trans_foo VALUES (2);
+ RELEASE SAVEPOINT four;
+ ROLLBACK TO SAVEPOINT three;
+ RELEASE SAVEPOINT three;
+ INSERT INTO trans_foo VALUES (3);
+COMMIT;
+SELECT * FROM trans_foo; -- should have 1 and 3
+ a
+---
+ 1
+ 3
+(2 rows)
+
+SELECT * FROM trans_barbaz; -- should have 1
+ a
+---
+ 1
+(1 row)
+
+-- test whole-tree commit
+BEGIN;
+ SAVEPOINT one;
+ SELECT trans_foo;
+ERROR: column "trans_foo" does not exist
+LINE 1: SELECT trans_foo;
+ ^
+ ROLLBACK TO SAVEPOINT one;
+ RELEASE SAVEPOINT one;
+ SAVEPOINT two;
+ CREATE TABLE savepoints (a int);
+ SAVEPOINT three;
+ INSERT INTO savepoints VALUES (1);
+ SAVEPOINT four;
+ INSERT INTO savepoints VALUES (2);
+ SAVEPOINT five;
+ INSERT INTO savepoints VALUES (3);
+ ROLLBACK TO SAVEPOINT five;
+COMMIT;
+COMMIT; -- should not be in a transaction block
+WARNING: there is no transaction in progress
+SELECT * FROM savepoints;
+ a
+---
+ 1
+ 2
+(2 rows)
+
+-- test whole-tree rollback
+BEGIN;
+ SAVEPOINT one;
+ DELETE FROM savepoints WHERE a=1;
+ RELEASE SAVEPOINT one;
+ SAVEPOINT two;
+ DELETE FROM savepoints WHERE a=1;
+ SAVEPOINT three;
+ DELETE FROM savepoints WHERE a=2;
+ROLLBACK;
+COMMIT; -- should not be in a transaction block
+WARNING: there is no transaction in progress
+SELECT * FROM savepoints;
+ a
+---
+ 1
+ 2
+(2 rows)
+
+-- test whole-tree commit on an aborted subtransaction
+BEGIN;
+ INSERT INTO savepoints VALUES (4);
+ SAVEPOINT one;
+ INSERT INTO savepoints VALUES (5);
+ SELECT trans_foo;
+ERROR: column "trans_foo" does not exist
+LINE 1: SELECT trans_foo;
+ ^
+COMMIT;
+SELECT * FROM savepoints;
+ a
+---
+ 1
+ 2
+(2 rows)
+
+BEGIN;
+ INSERT INTO savepoints VALUES (6);
+ SAVEPOINT one;
+ INSERT INTO savepoints VALUES (7);
+ RELEASE SAVEPOINT one;
+ INSERT INTO savepoints VALUES (8);
+COMMIT;
+-- rows 6 and 8 should have been created by the same xact
+SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
+ ?column?
+----------
+ t
+(1 row)
+
+-- rows 6 and 7 should have been created by different xacts
+SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
+ ?column?
+----------
+ f
+(1 row)
+
+BEGIN;
+ INSERT INTO savepoints VALUES (9);
+ SAVEPOINT one;
+ INSERT INTO savepoints VALUES (10);
+ ROLLBACK TO SAVEPOINT one;
+ INSERT INTO savepoints VALUES (11);
+COMMIT;
+SELECT a FROM savepoints WHERE a in (9, 10, 11);
+ a
+----
+ 9
+ 11
+(2 rows)
+
+-- rows 9 and 11 should have been created by different xacts
+SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
+ ?column?
+----------
+ f
+(1 row)
+
+BEGIN;
+ INSERT INTO savepoints VALUES (12);
+ SAVEPOINT one;
+ INSERT INTO savepoints VALUES (13);
+ SAVEPOINT two;
+ INSERT INTO savepoints VALUES (14);
+ ROLLBACK TO SAVEPOINT one;
+ INSERT INTO savepoints VALUES (15);
+ SAVEPOINT two;
+ INSERT INTO savepoints VALUES (16);
+ SAVEPOINT three;
+ INSERT INTO savepoints VALUES (17);
+COMMIT;
+SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
+ a
+----
+ 12
+ 15
+ 16
+ 17
+(4 rows)
+
+BEGIN;
+ INSERT INTO savepoints VALUES (18);
+ SAVEPOINT one;
+ INSERT INTO savepoints VALUES (19);
+ SAVEPOINT two;
+ INSERT INTO savepoints VALUES (20);
+ ROLLBACK TO SAVEPOINT one;
+ INSERT INTO savepoints VALUES (21);
+ ROLLBACK TO SAVEPOINT one;
+ INSERT INTO savepoints VALUES (22);
+COMMIT;
+SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
+ a
+----
+ 18
+ 22
+(2 rows)
+
+DROP TABLE savepoints;
+-- only in a transaction block:
+SAVEPOINT one;
+ERROR: SAVEPOINT can only be used in transaction blocks
+ROLLBACK TO SAVEPOINT one;
+ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
+RELEASE SAVEPOINT one;
+ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
+-- Only "rollback to" allowed in aborted state
+BEGIN;
+ SAVEPOINT one;
+ SELECT 0/0;
+ERROR: division by zero
+ SAVEPOINT two; -- ignored till the end of ...
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+ RELEASE SAVEPOINT one; -- ignored till the end of ...
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+ ROLLBACK TO SAVEPOINT one;
+ SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+COMMIT;
+SELECT 1; -- this should work
+ ?column?
+----------
+ 1
+(1 row)
+
+-- check non-transactional behavior of cursors
+BEGIN;
+ DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
+ SAVEPOINT one;
+ FETCH 10 FROM c;
+ unique2
+---------
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+(10 rows)
+
+ ROLLBACK TO SAVEPOINT one;
+ FETCH 10 FROM c;
+ unique2
+---------
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+(10 rows)
+
+ RELEASE SAVEPOINT one;
+ FETCH 10 FROM c;
+ unique2
+---------
+ 20
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+(10 rows)
+
+ CLOSE c;
+ DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
+ SAVEPOINT two;
+ FETCH 10 FROM c;
+ERROR: division by zero
+ ROLLBACK TO SAVEPOINT two;
+ -- c is now dead to the world ...
+ FETCH 10 FROM c;
+ERROR: portal "c" cannot be run
+ ROLLBACK TO SAVEPOINT two;
+ RELEASE SAVEPOINT two;
+ FETCH 10 FROM c;
+ERROR: portal "c" cannot be run
+COMMIT;
+--
+-- Check that "stable" functions are really stable. They should not be
+-- able to see the partial results of the calling query. (Ideally we would
+-- also check that they don't see commits of concurrent transactions, but
+-- that's a mite hard to do within the limitations of pg_regress.)
+--
+select * from xacttest;
+ a | b
+-----+---------
+ 56 | 7.8
+ 100 | 99.097
+ 0 | 0.09561
+ 42 | 324.78
+ 777 | 777.777
+(5 rows)
+
+create or replace function max_xacttest() returns smallint language sql as
+'select max(a) from xacttest' stable;
+begin;
+update xacttest set a = max_xacttest() + 10 where a > 0;
+select * from xacttest;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 787 | 7.8
+ 787 | 99.097
+ 787 | 324.78
+ 787 | 777.777
+(5 rows)
+
+rollback;
+-- But a volatile function can see the partial results of the calling query
+create or replace function max_xacttest() returns smallint language sql as
+'select max(a) from xacttest' volatile;
+begin;
+update xacttest set a = max_xacttest() + 10 where a > 0;
+select * from xacttest;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 787 | 7.8
+ 797 | 99.097
+ 807 | 324.78
+ 817 | 777.777
+(5 rows)
+
+rollback;
+-- Now the same test with plpgsql (since it depends on SPI which is different)
+create or replace function max_xacttest() returns smallint language plpgsql as
+'begin return max(a) from xacttest; end' stable;
+begin;
+update xacttest set a = max_xacttest() + 10 where a > 0;
+select * from xacttest;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 787 | 7.8
+ 787 | 99.097
+ 787 | 324.78
+ 787 | 777.777
+(5 rows)
+
+rollback;
+create or replace function max_xacttest() returns smallint language plpgsql as
+'begin return max(a) from xacttest; end' volatile;
+begin;
+update xacttest set a = max_xacttest() + 10 where a > 0;
+select * from xacttest;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 787 | 7.8
+ 797 | 99.097
+ 807 | 324.78
+ 817 | 777.777
+(5 rows)
+
+rollback;
+-- test case for problems with dropping an open relation during abort
+BEGIN;
+ savepoint x;
+ CREATE TABLE koju (a INT UNIQUE);
+ INSERT INTO koju VALUES (1);
+ INSERT INTO koju VALUES (1);
+ERROR: duplicate key value violates unique constraint "koju_a_key"
+DETAIL: Key (a)=(1) already exists.
+ rollback to x;
+ CREATE TABLE koju (a INT UNIQUE);
+ INSERT INTO koju VALUES (1);
+ INSERT INTO koju VALUES (1);
+ERROR: duplicate key value violates unique constraint "koju_a_key"
+DETAIL: Key (a)=(1) already exists.
+ROLLBACK;
+DROP TABLE trans_foo;
+DROP TABLE trans_baz;
+DROP TABLE trans_barbaz;
+-- test case for problems with revalidating an open relation during abort
+create function inverse(int) returns float8 as
+$$
+begin
+ analyze revalidate_bug;
+ return 1::float8/$1;
+exception
+ when division_by_zero then return 0;
+end$$ language plpgsql volatile;
+create table revalidate_bug (c float8 unique);
+insert into revalidate_bug values (1);
+insert into revalidate_bug values (inverse(0));
+drop table revalidate_bug;
+drop function inverse(int);
+-- verify that cursors created during an aborted subtransaction are
+-- closed, but that we do not rollback the effect of any FETCHs
+-- performed in the aborted subtransaction
+begin;
+savepoint x;
+create table trans_abc (a int);
+insert into trans_abc values (5);
+insert into trans_abc values (10);
+declare foo cursor for select * from trans_abc;
+fetch from foo;
+ a
+---
+ 5
+(1 row)
+
+rollback to x;
+-- should fail
+fetch from foo;
+ERROR: cursor "foo" does not exist
+commit;
+begin;
+create table trans_abc (a int);
+insert into trans_abc values (5);
+insert into trans_abc values (10);
+insert into trans_abc values (15);
+declare foo cursor for select * from trans_abc;
+fetch from foo;
+ a
+---
+ 5
+(1 row)
+
+savepoint x;
+fetch from foo;
+ a
+----
+ 10
+(1 row)
+
+rollback to x;
+fetch from foo;
+ a
+----
+ 15
+(1 row)
+
+abort;
+-- Test for proper cleanup after a failure in a cursor portal
+-- that was created in an outer subtransaction
+CREATE FUNCTION invert(x float8) RETURNS float8 LANGUAGE plpgsql AS
+$$ begin return 1/x; end $$;
+CREATE FUNCTION create_temp_tab() RETURNS text
+LANGUAGE plpgsql AS $$
+BEGIN
+ CREATE TEMP TABLE new_table (f1 float8);
+ -- case of interest is that we fail while holding an open
+ -- relcache reference to new_table
+ INSERT INTO new_table SELECT invert(0.0);
+ RETURN 'foo';
+END $$;
+BEGIN;
+DECLARE ok CURSOR FOR SELECT * FROM int8_tbl;
+DECLARE ctt CURSOR FOR SELECT create_temp_tab();
+FETCH ok;
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
+SAVEPOINT s1;
+FETCH ok; -- should work
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+(1 row)
+
+FETCH ctt; -- error occurs here
+ERROR: division by zero
+CONTEXT: PL/pgSQL function invert(double precision) line 1 at RETURN
+SQL statement "INSERT INTO new_table SELECT invert(0.0)"
+PL/pgSQL function create_temp_tab() line 6 at SQL statement
+ROLLBACK TO s1;
+FETCH ok; -- should work
+ q1 | q2
+------------------+-----
+ 4567890123456789 | 123
+(1 row)
+
+FETCH ctt; -- must be rejected
+ERROR: portal "ctt" cannot be run
+COMMIT;
+DROP FUNCTION create_temp_tab();
+DROP FUNCTION invert(x float8);
+-- Tests for AND CHAIN
+CREATE TABLE trans_abc (a int);
+-- set nondefault value so we have something to override below
+SET default_transaction_read_only = on;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO trans_abc VALUES (1);
+INSERT INTO trans_abc VALUES (2);
+COMMIT AND CHAIN; -- TBLOCK_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO trans_abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO trans_abc VALUES ('error');
+ ^
+INSERT INTO trans_abc VALUES (3); -- check it's really aborted
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+COMMIT AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO trans_abc VALUES (4);
+COMMIT;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+SAVEPOINT x;
+INSERT INTO trans_abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO trans_abc VALUES ('error');
+ ^
+COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO trans_abc VALUES (5);
+COMMIT;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+SAVEPOINT x;
+COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+COMMIT;
+START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ read committed
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+SAVEPOINT x;
+COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ read committed
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+COMMIT;
+-- different mix of options just for fun
+START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ serializable
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ off
+(1 row)
+
+INSERT INTO trans_abc VALUES (6);
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ serializable
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ off
+(1 row)
+
+INSERT INTO trans_abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO trans_abc VALUES ('error');
+ ^
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ serializable
+(1 row)
+
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ off
+(1 row)
+
+ROLLBACK;
+-- not allowed outside a transaction block
+COMMIT AND CHAIN; -- error
+ERROR: COMMIT AND CHAIN can only be used in transaction blocks
+ROLLBACK AND CHAIN; -- error
+ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
+SELECT * FROM trans_abc ORDER BY 1;
+ a
+---
+ 1
+ 2
+ 4
+ 5
+(4 rows)
+
+RESET default_transaction_read_only;
+DROP TABLE trans_abc;
+-- Test assorted behaviors around the implicit transaction block created
+-- when multiple SQL commands are sent in a single Query message. These
+-- tests rely on the fact that psql will not break SQL commands apart at a
+-- backslash-quoted semicolon, but will send them as one Query.
+create temp table i_table (f1 int);
+-- psql will show all results of a multi-statement Query
+SELECT 1\; SELECT 2\; SELECT 3;
+ ?column?
+----------
+ 1
+(1 row)
+
+ ?column?
+----------
+ 2
+(1 row)
+
+ ?column?
+----------
+ 3
+(1 row)
+
+-- this implicitly commits:
+insert into i_table values(1)\; select * from i_table;
+ f1
+----
+ 1
+(1 row)
+
+-- 1/0 error will cause rolling back the whole implicit transaction
+insert into i_table values(2)\; select * from i_table\; select 1/0;
+ f1
+----
+ 1
+ 2
+(2 rows)
+
+ERROR: division by zero
+select * from i_table;
+ f1
+----
+ 1
+(1 row)
+
+rollback; -- we are not in a transaction at this point
+WARNING: there is no transaction in progress
+-- can use regular begin/commit/rollback within a single Query
+begin\; insert into i_table values(3)\; commit;
+rollback; -- we are not in a transaction at this point
+WARNING: there is no transaction in progress
+begin\; insert into i_table values(4)\; rollback;
+rollback; -- we are not in a transaction at this point
+WARNING: there is no transaction in progress
+-- begin converts implicit transaction into a regular one that
+-- can extend past the end of the Query
+select 1\; begin\; insert into i_table values(5);
+ ?column?
+----------
+ 1
+(1 row)
+
+commit;
+select 1\; begin\; insert into i_table values(6);
+ ?column?
+----------
+ 1
+(1 row)
+
+rollback;
+-- commit in implicit-transaction state commits but issues a warning.
+insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0;
+WARNING: there is no transaction in progress
+ERROR: division by zero
+-- similarly, rollback aborts but issues a warning.
+insert into i_table values(9)\; rollback\; select 2;
+WARNING: there is no transaction in progress
+ ?column?
+----------
+ 2
+(1 row)
+
+select * from i_table;
+ f1
+----
+ 1
+ 3
+ 5
+ 7
+(4 rows)
+
+rollback; -- we are not in a transaction at this point
+WARNING: there is no transaction in progress
+-- implicit transaction block is still a transaction block, for e.g. VACUUM
+SELECT 1\; VACUUM;
+ ?column?
+----------
+ 1
+(1 row)
+
+ERROR: VACUUM cannot run inside a transaction block
+SELECT 1\; COMMIT\; VACUUM;
+WARNING: there is no transaction in progress
+ ?column?
+----------
+ 1
+(1 row)
+
+ERROR: VACUUM cannot run inside a transaction block
+-- we disallow savepoint-related commands in implicit-transaction state
+SELECT 1\; SAVEPOINT sp;
+ ?column?
+----------
+ 1
+(1 row)
+
+ERROR: SAVEPOINT can only be used in transaction blocks
+SELECT 1\; COMMIT\; SAVEPOINT sp;
+WARNING: there is no transaction in progress
+ ?column?
+----------
+ 1
+(1 row)
+
+ERROR: SAVEPOINT can only be used in transaction blocks
+ROLLBACK TO SAVEPOINT sp\; SELECT 2;
+ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
+SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
+ ?column?
+----------
+ 2
+(1 row)
+
+ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
+-- but this is OK, because the BEGIN converts it to a regular xact
+SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
+ ?column?
+----------
+ 1
+(1 row)
+
+-- Tests for AND CHAIN in implicit transaction blocks
+SET TRANSACTION READ ONLY\; COMMIT AND CHAIN; -- error
+ERROR: COMMIT AND CHAIN can only be used in transaction blocks
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+SET TRANSACTION READ ONLY\; ROLLBACK AND CHAIN; -- error
+ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
+SHOW transaction_read_only;
+ transaction_read_only
+-----------------------
+ off
+(1 row)
+
+CREATE TABLE trans_abc (a int);
+-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
+INSERT INTO trans_abc VALUES (7)\; COMMIT\; INSERT INTO trans_abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error
+WARNING: there is no transaction in progress
+ERROR: COMMIT AND CHAIN can only be used in transaction blocks
+INSERT INTO trans_abc VALUES (9)\; ROLLBACK\; INSERT INTO trans_abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
+WARNING: there is no transaction in progress
+ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
+-- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
+INSERT INTO trans_abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO trans_abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached
+ERROR: COMMIT AND CHAIN can only be used in transaction blocks
+INSERT INTO trans_abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO trans_abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
+ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
+-- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
+SHOW transaction_isolation; -- transaction is active at this point
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+COMMIT;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO trans_abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
+SHOW transaction_isolation; -- transaction is active at this point
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+ROLLBACK;
+SET default_transaction_isolation = 'read committed';
+-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
+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
+ERROR: COMMIT AND CHAIN can only be used in transaction blocks
+SHOW transaction_isolation; -- out of transaction block
+ transaction_isolation
+-----------------------
+ read committed
+(1 row)
+
+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
+ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
+SHOW transaction_isolation; -- out of transaction block
+ transaction_isolation
+-----------------------
+ read committed
+(1 row)
+
+RESET default_transaction_isolation;
+SELECT * FROM trans_abc ORDER BY 1;
+ a
+----
+ 7
+ 15
+ 17
+(3 rows)
+
+DROP TABLE trans_abc;
+-- Test for successful cleanup of an aborted transaction at session exit.
+-- THIS MUST BE THE LAST TEST IN THIS FILE.
+begin;
+select 1/0;
+ERROR: division by zero
+rollback to X;
+ERROR: savepoint "x" does not exist
+-- DO NOT ADD ANYTHING HERE.
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-29 14:15:26 +0000