Hi all, If a transaction block fails outside a savepoint, the transaction started by COMMIT/ROLLBACK AND CHAIN gets the default characteristics instead of the block's set characteristics:
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY,
DEFERRABLE;
SELECT 1/0; -- ERROR: division by zero
COMMIT AND CHAIN;
SHOW transaction_isolation; -- read committed, expected repeatable
read
SHOW transaction_read_only; -- off, expected on
SHOW transaction_deferrable; -- off, expected on
ROLLBACK;
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
SELECT 1/0; -- ERROR: division by zero
ROLLBACK AND CHAIN;
SHOW transaction_isolation; -- read committed, expected serializable
SHOW transaction_read_only; -- off, expected on
SHOW transaction_deferrable; -- off, expected on
ROLLBACK;
In the attached patchset, the first commit adds a regression test similar
to the above.
The second commit fixes AbortCurrentTransactionInternal to
keep the characteristics of a failed transaction block for AND CHAIN.
Details on the fix:
COMMIT AND CHAIN and ROLLBACK AND CHAIN start the new transaction with
the characteristics of the finished one, which
CommitTransactionCommand() reads when the command arrives.
AbortTransaction() reverts the isolation level, read-only and deferrable
settings set by BEGIN or SET TRANSACTION, along with the other GUCs. A
retry loop doing ROLLBACK AND CHAIN after a serialization failure thus
silently retried in READ COMMITTED.
The commit restores the characteristics right after AbortTransaction()
when a transaction block fails, so they last until the block ends. A
failed transaction that was itself started by AND CHAIN already keeps
them, because they are not set through the GUC stack.
Bug present since AND CHAIN was added in v12.
0002-Keep-the-characteristics-of-a-failed-transaction-blo.patch
Description: Binary data
0001-Add-tests-for-AND-CHAIN-after-an-error-in-a-non-chai.patch
Description: Binary data
