Bug report and fix for: COMMIT/ROLLBACK AND CHAIN after an error drops the isolation level
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.
Attachments:
t254583_10002-Keep-the-characteristics-of-a-failed-transaction-blo.patchapplication/octet-stream; name=0002-Keep-the-characteristics-of-a-failed-transaction-blo.patchDownload+4-1
0001-Add-tests-for-AND-CHAIN-after-an-error-in-a-non-chai.patchapplication/octet-stream; name=0001-Add-tests-for-AND-CHAIN-after-an-error-in-a-non-chai.patchDownload+72-1
On Thu, Nov 05, 2026 at 10:40:40PM -0500, Narek Galstyan wrote:
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.
Sounds like a valid complaint to me.
@@ -3547,6 +3547,7 @@ static bool
AbortCurrentTransactionInternal(void)
{
TransactionState s = CurrentTransactionState;
+ SavedTransactionCharacteristics savetc;
switch (s->blockState)
{
@@ -3603,7 +3604,10 @@ AbortCurrentTransactionInternal(void)
*/
case TBLOCK_INPROGRESS:
case TBLOCK_PARALLEL_INPROGRESS:
+ /* Restore characteristics in case AND CHAIN is coming */
+ SaveTransactionCharacteristics(&savetc);
AbortTransaction();
+ RestoreTransactionCharacteristics(&savetc);
s->blockState = TBLOCK_ABORT;
/* CleanupTransaction happens when we exit TBLOCK_ABORT_END */
break;
How about being consistent with CommitTransactionCommandInternal(),
where the state is saved at the top of the function and then restored
in the blocks required?
Do we have other blocks where we may want to case about a restore of
the state and some of the characteristics? I have not double-checked,
but...
--
Michael
On Wed, Nov 11, 2026 at 04:48:31PM +0900, Michael Paquier wrote:
Do we have other blocks where we may want to case about a restore of
the state and some of the characteristics? I have not double-checked,
but...
There was a second problem here. See this sequence:
START TRANSACTION READ WRITE;
SAVEPOINT x;
SET TRANSACTION READ ONLY;
COMMIT AND CHAIN;
-- true on HEAD, should be false.
SHOW transaction_read_only;
When chaining to the second transaction with the chain, the second
transaction inherited the READ ONLY state coming from the
subtransaction created in the first transaction instead of using READ
WRITE from the first top transaction. Among deferrable, read_only and
isolation, read_only is the only one that a savepoint can change, as
far as I can see.
A couple of edits later, I finish with the attached.
Thoughts or comments?
--
Michael