-- performed in the aborted subtransaction

begin;

savepoint x;

create tableabc (a int);

insert intoabc values (5);

insert intoabc values (10);

declare foo cursor for select * fromabc;

create tabletrans_abc (a int);

insert intotrans_abc values (5);

insert intotrans_abc values (10);

declare foo cursor for select * fromtrans_abc;

fetch from foo;

a

---

ERROR: cursor "foo" does not exist

commit;

begin;

create tableabc (a int);

insert intoabc values (5);

insert intoabc values (10);

insert intoabc values (15);

declare foo cursor for select * fromabc;

create tabletrans_abc (a int);

insert intotrans_abc values (5);

insert intotrans_abc values (10);

insert intotrans_abc values (15);

declare foo cursor for select * fromtrans_abc;

fetch from foo;

a

---

DROP FUNCTION create_temp_tab();

DROP FUNCTION invert(x float8);

-- Tests for AND CHAIN

CREATE TABLEabc (a int);

CREATE TABLEtrans_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;

on

(1 row)

INSERT INTOabc VALUES (1);

INSERT INTOabc VALUES (2);

INSERT INTOtrans_abc VALUES (1);

INSERT INTOtrans_abc VALUES (2);

COMMIT AND CHAIN; -- TBLOCK_END

SHOW transaction_isolation;

transaction_isolation

on

(1 row)

INSERT INTOabc VALUES ('error');

INSERT INTOtrans_abc VALUES ('error');

ERROR: invalid input syntax for type integer: "error"

LINE 1: INSERT INTOabc VALUES ('error');

^

INSERT INTOabc VALUES (3); -- check it's really aborted

LINE 1: INSERT INTOtrans_abc VALUES ('error');

^

INSERT INTOtrans_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;

on

(1 row)

INSERT INTOabc VALUES (4);

INSERT INTOtrans_abc VALUES (4);

COMMIT;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;

SHOW transaction_isolation;

(1 row)

SAVEPOINT x;

INSERT INTOabc VALUES ('error');

INSERT INTOtrans_abc VALUES ('error');

ERROR: invalid input syntax for type integer: "error"

LINE 1: INSERT INTOabc VALUES ('error');

^

LINE 1: INSERT INTOtrans_abc VALUES ('error');

^

COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING

SHOW transaction_isolation;

transaction_isolation

on

(1 row)

INSERT INTOabc VALUES (5);

INSERT INTOtrans_abc VALUES (5);

COMMIT;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;

SHOW transaction_isolation;

off

(1 row)

INSERT INTOabc VALUES (6);

INSERT INTOtrans_abc VALUES (6);

ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING

SHOW transaction_isolation;

transaction_isolation

off

(1 row)

INSERT INTOabc VALUES ('error');

INSERT INTOtrans_abc VALUES ('error');

ERROR: invalid input syntax for type integer: "error"

LINE 1: INSERT INTOabc VALUES ('error');

^

LINE 1: INSERT INTOtrans_abc VALUES ('error');

^

ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END

SHOW transaction_isolation;

transaction_isolation

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 * FROMabc ORDER BY 1;

SELECT * FROMtrans_abc ORDER BY 1;

a

---

1

(4 rows)

RESET default_transaction_read_only;

DROP TABLEabc;

DROP TABLEtrans_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

off

(1 row)

CREATE TABLEabc (a int);

CREATE TABLEtrans_abc (a int);

-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN

INSERT INTOabc VALUES (7)\; COMMIT\; INSERT INTOabc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error

INSERT INTOtrans_abc VALUES (7)\; COMMIT\; INSERT INTOtrans_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 INTOabc VALUES (9)\; ROLLBACK\; INSERT INTOabc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error

INSERT INTOtrans_abc VALUES (9)\; ROLLBACK\; INSERT INTOtrans_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 INTOabc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTOabc VALUES (12)\; COMMIT; -- 11 error, 12 not reached

INSERT INTOtrans_abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTOtrans_abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached

ERROR: COMMIT AND CHAIN can only be used in transaction blocks

INSERT INTOabc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTOabc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached

INSERT INTOtrans_abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTOtrans_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 INTOabc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTOtrans_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 INTOabc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTOtrans_abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok

SHOW transaction_isolation; -- transaction is active at this point

transaction_isolation

-----------------------

ROLLBACK;

-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTOabc VALUES (17)\; COMMIT\; INSERT INTOabc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTOtrans_abc VALUES (17)\; COMMIT\; INSERT INTOtrans_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 INTOabc VALUES (19)\; ROLLBACK\; INSERT INTOabc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTOtrans_abc VALUES (19)\; ROLLBACK\; INSERT INTOtrans_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)

SELECT * FROMabc ORDER BY 1;

SELECT * FROMtrans_abc ORDER BY 1;

a

----

7

15

17

(3 rows)

DROP TABLEabc;

DROP TABLEtrans_abc;

-- Test for successful cleanup of an aborted transaction at session exit.

-- THIS MUST BE THE LAST TEST IN THIS FILE.

begin;

begin;

savepoint x;

createtableabc (aint);

insert intoabcvalues (5);

insert intoabcvalues (10);

declare foo cursor forselect*fromabc;

createtabletrans_abc (aint);

insert intotrans_abcvalues (5);

insert intotrans_abcvalues (10);

declare foo cursor forselect*fromtrans_abc;

fetchfrom foo;

rollback to x;

begin;

createtableabc (aint);

insert intoabcvalues (5);

insert intoabcvalues (10);

insert intoabcvalues (15);

declare foo cursor forselect*fromabc;

createtabletrans_abc (aint);

insert intotrans_abcvalues (5);

insert intotrans_abcvalues (10);

insert intotrans_abcvalues (15);

declare foo cursor forselect*fromtrans_abc;

fetchfrom foo;

CREATETABLEabc (aint);

CREATETABLEtrans_abc (aint);

SET default_transaction_read_only=on;

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES (1);

INSERT INTOabcVALUES (2);

INSERT INTOtrans_abcVALUES (1);

INSERT INTOtrans_abcVALUES (2);

COMMITAND CHAIN;-- TBLOCK_END

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES ('error');

INSERT INTOabcVALUES (3);-- check it's really aborted

INSERT INTOtrans_abcVALUES ('error');

INSERT INTOtrans_abcVALUES (3);-- check it's really aborted

COMMITAND CHAIN;-- TBLOCK_ABORT_END

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES (4);

INSERT INTOtrans_abcVALUES (4);

COMMIT;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

SAVEPOINT x;

INSERT INTOabcVALUES ('error');

INSERT INTOtrans_abcVALUES ('error');

COMMITAND CHAIN;-- TBLOCK_ABORT_PENDING

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES (5);

INSERT INTOtrans_abcVALUES (5);

COMMIT;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES (6);

INSERT INTOtrans_abcVALUES (6);

ROLLBACKAND CHAIN;-- TBLOCK_ABORT_PENDING

SHOW transaction_isolation;

SHOW transaction_read_only;

SHOW transaction_deferrable;

INSERT INTOabcVALUES ('error');

INSERT INTOtrans_abcVALUES ('error');

ROLLBACKAND CHAIN;-- TBLOCK_ABORT_END

SHOW transaction_isolation;

SHOW transaction_read_only;

COMMITAND CHAIN;-- error

ROLLBACKAND CHAIN;-- error

SELECT*FROMabcORDER BY1;

SELECT*FROMtrans_abcORDER BY1;

RESET default_transaction_read_only;

DROPTABLEabc;

DROPTABLEtrans_abc;

SET TRANSACTION READ ONLY\;ROLLBACKAND CHAIN;-- error

SHOW transaction_read_only;

CREATETABLEabc (aint);

CREATETABLEtrans_abc (aint);

INSERT INTOabcVALUES (7)\;COMMIT\;INSERT INTOabcVALUES (8)\;COMMITAND CHAIN;-- 7 commit, 8 error

INSERT INTOabcVALUES (9)\;ROLLBACK\;INSERT INTOabcVALUES (10)\;ROLLBACKAND CHAIN;-- 9 rollback, 10 error

INSERT INTOtrans_abcVALUES (7)\;COMMIT\;INSERT INTOtrans_abcVALUES (8)\;COMMITAND CHAIN;-- 7 commit, 8 error

INSERT INTOtrans_abcVALUES (9)\;ROLLBACK\;INSERT INTOtrans_abcVALUES (10)\;ROLLBACKAND CHAIN;-- 9 rollback, 10 error

INSERT INTOabcVALUES (11)\;COMMITAND CHAIN\;INSERT INTOabcVALUES (12)\;COMMIT;-- 11 error, 12 not reached

INSERT INTOabcVALUES (13)\;ROLLBACKAND CHAIN\;INSERT INTOabcVALUES (14)\;ROLLBACK;-- 13 error, 14 not reached

INSERT INTOtrans_abcVALUES (11)\;COMMITAND CHAIN\;INSERT INTOtrans_abcVALUES (12)\;COMMIT;-- 11 error, 12 not reached

INSERT INTOtrans_abcVALUES (13)\;ROLLBACKAND CHAIN\;INSERT INTOtrans_abcVALUES (14)\;ROLLBACK;-- 13 error, 14 not reached

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOabcVALUES (15)\;COMMITAND CHAIN;-- 15 ok

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOtrans_abcVALUES (15)\;COMMITAND CHAIN;-- 15 ok

SHOW transaction_isolation;-- transaction is active at this point

COMMIT;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOabcVALUES (16)\;ROLLBACKAND CHAIN;-- 16 ok

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOtrans_abcVALUES (16)\;ROLLBACKAND CHAIN;-- 16 ok

SHOW transaction_isolation;-- transaction is active at this point

ROLLBACK;

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOabcVALUES (17)\;COMMIT\;INSERT INTOabcVALUES (18)\;COMMITAND CHAIN;-- 17 commit, 18 error

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOtrans_abcVALUES (17)\;COMMIT\;INSERT INTOtrans_abcVALUES (18)\;COMMITAND CHAIN;-- 17 commit, 18 error

SHOW transaction_isolation;-- out of transaction block

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOabcVALUES (19)\;ROLLBACK\;INSERT INTOabcVALUES (20)\;ROLLBACKAND CHAIN;-- 19 rollback, 20 error

START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTOtrans_abcVALUES (19)\;ROLLBACK\;INSERT INTOtrans_abcVALUES (20)\;ROLLBACKAND CHAIN;-- 19 rollback, 20 error

SHOW transaction_isolation;-- out of transaction block

SELECT*FROMabcORDER BY1;

SELECT*FROMtrans_abcORDER BY1;

DROPTABLEabc;

DROPTABLEtrans_abc;