| -- |
| -- 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 (9); |
| RELEASE SAVEPOINT one; |
| INSERT INTO savepoints VALUES (10); |
| COMMIT; |
| -- CBDB: 6, 9, 10 are routed to the same segment(numseg=3) |
| -- rows 6 and 10 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=10; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- rows 6 and 9 should have been created by different xacts |
| SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=9; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| -- CBDB: delete 9, 10, so the following test is not interferred. |
| DELETE FROM savepoints WHERE a in (9, 10); |
| 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 READS SQL DATA; |
| begin; |
| update xacttest set a = (select 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; |
| -- start_ignore |
| -- 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 READS SQL DATA; |
| 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; |
| -- end_ignore |
| -- 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 READS SQL DATA; |
| begin; |
| update xacttest set a = (select 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; |
| -- start_ignore |
| create or replace function max_xacttest() returns smallint language plpgsql as |
| 'begin return max(a) from xacttest; end' volatile READS SQL DATA; |
| 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; |
| -- end_ignore |
| -- 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; |
| -- CBDB: the order of value is not guaranteed |
| -- start_ignore |
| fetch from foo; |
| a |
| --- |
| 5 |
| (1 row) |
| |
| -- end_ignore |
| savepoint x; |
| -- start_ignore |
| fetch from foo; |
| a |
| ---- |
| 10 |
| (1 row) |
| |
| -- end_ignore |
| rollback to x; |
| -- start_ignore |
| fetch from foo; |
| a |
| ---- |
| 15 |
| (1 row) |
| |
| -- end_ignore |
| 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(); |
| -- start_ignore |
| FETCH ok; |
| q1 | q2 |
| -----+----- |
| 123 | 456 |
| (1 row) |
| |
| -- end_ignore |
| SAVEPOINT s1; |
| -- start_ignore |
| FETCH ok; -- should work |
| q1 | q2 |
| -----+------------------ |
| 123 | 4567890123456789 |
| (1 row) |
| |
| -- end_ignore |
| 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; |
| -- start_ignore |
| FETCH ok; -- should work |
| q1 | q2 |
| ------------------+----- |
| 4567890123456789 | 123 |
| (1 row) |
| |
| -- end_ignore |
| 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 |
| ----------------------- |
| repeatable read |
| (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 |
| ----------------------- |
| repeatable read |
| (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 |
| ----------------------- |
| repeatable read |
| (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. |
