PL/pgSQL - why it does not allow commit inside a subtransaction

Every once in a while the question of why transactions in PL/pgSQL can't be committed when inside a subtransaction comes up. In a different way, the question is why the following error message exists:

ERROR: cannot commit while a subtransaction is active

Such a message comes up easily in procedures that attempt to COMMIT inside of exception blocks, such as:

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- DML
EXCEPTION
WHEN division_by_zero THEN
-- exception block
END;
END;

The question is common when coming from from PL/SQL, where such a code is allowed. To ease migrations, EPAS provides EDB-SPL, which also allows that syntax.

But the question remains on why PL/pgSQL does not allow committing inside exception blocks, like both PL/SQL and EDB-SPL allow.

Imagine the transaction can contain subtransactions just as a stack contains items. You push subtransactions to the top of the stack and you can pop subtransactions from the top until you reach a named item somewhere in the stack. A transaction with no subtransactions is an empty stack identical to all other empty stacks. But a transaction with a subtransaction "X" is not the same as a transaction with a subtransaction "Y". In fact, even two transactions that contain a subtransaction with the same name "X" are still not identical, since each contains a different set of changes inside and underneath them.

A transaction begins empty, accumulates data changes (inserts, updates, deletes, represented as + and -), gets committed or rolled back, leaving a new, empty transaction in its place:

BEGIN
-- DML
COMMIT;
-- DML
END;

│ │→│…++--+…│→│ │→│…-++++…│→│ │
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘

When a BEGIN/END block appears and it contains a EXCEPTION clause, the BEGIN implicitly creates a named savepoint that pushes a subtransaction on the stack, and that subtransaction contains all data changed before it (until the previous subtransaction or the beginning of the transaction). When an error is thrown, it searches the stack downwards until it finds an item that matches the condition (i.e, the innermost EXCEPTION block that handles that specific error), then rolls back to that subtransaction and continues executing the THEN block within the EXCEPTION definition of the level where it was found. Otherwise, if an error is not thrown and the EXCEPTION keyword is reached, it searches the stack until it finds the named savepoint that was created for the same level, then releases all subtransactions up until that point and continues executing after the END clause. Note that this automatic release/rollback is significantly different than the behavior defined in PL/SQL and mimicked by EDB-SPL.

Remember that functions and even procedures can call one another. A function that is called outside of a transaction automatically creates a transaction with the duration of its execution, but cannot control it. A procedure in the same situation automatically creates a transaction as well, but can control it somewhat. But a procedure that is called from within a transaction does not create a new transaction. Instead, it may at most create a subtransaction and use it, which happens implicitly if the EXCEPTION clause is present.

A transaction that contains subtransactions can be rolled back to them, which happens when an error is thrown in a context and caught by an exception block:

BEGIN
-- DML
BEGIN
-- DML that gets rolled back
-- DML that errors
-- DML that never got to execute
EXCEPTION
WHEN ... THEN
-- exception block
-- the subtransaction was rolled back before starting it
END;
END;
│!ERROR!│
│…+-+++…│ │…+-+++…│
├subxact┤ ├subxact┤ ├subxact┤
│ │→│…++--+…│→│…++--+…│→│…++--+…│→│…++--+…│→│…++--+…│→…
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML ERROR ROLLBACK

Or the subtransaction can be released, so the changes are commited when the surrounding transaction (or next subtransaction) commits:

BEGIN
-- DML
BEGIN
-- DML that gets added to the subtransaction
-- DML that does not error
-- DML that executes before the subtransaction gets released
EXCEPTION
WHEN ... THEN
-- exception block
-- unreached
END;
END;
│…+-+++…│ │…+-+++…│
├subxact┤ ├subxact┤ ├subxact┤ │…+-+++…│
│ │→│…++--+…│→│…++--+…│→│…++--+…│→│…++--+…│→│…++--+…│→…
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML END RELEASE

When dealing with simple scenarios, like the first one, where a COMMIT is placed outside of all nested blocks, then the stack is empty at the that time. So we can end a transaction and then start a subsequent one that is identical, then the code after that continues as if the code block had just started. The stack did receive a push at a moment, but it also got the same number of pops, so it was empty when the transaction was asked to commit. So when the transaction ended and a new one was implicitly started, the new one behaved exactly the same way as the previous one would:

│…+I2++…│ │…+I2++…│ │…+I3++…│
├subxact┤ ├subxact┤ ├subxact┤ │…+I2++…│ │…+I2++…│
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│ │→│…+I4++…│→│ │
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN INSERT BEGIN INSERT END RELEASE INSERT COMMIT INSERT COMMIT

But when you attempt to COMMIT in a moment when the transaction stack is not empty, such as in the case where a COMMIT is inside an EXCEPTION block, you end up in a state that is not adequate for any subsequent usage:

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- ???
EXCEPTION
WHEN ... THEN
-- ???
-- exception block
END;
END;
│COMMIT │
│…+I2++…│ │…+I2++…│
├subxact┤ ├subxact┤ ├subxact┤
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│???????│
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN INSERT BEGIN INSERT COMMIT

1. Consider that if the code after the COMMIT executes correctly, then it reaches the EXCEPTION clause successfully. By the established semantics, we should RELEASE the savepoint, so we search inside the stack for the subtransaction subxact that was opened by this block, which is then not found, which would lead to a different error ("savepoint does not exist").

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- DML
EXCEPTION
WHEN division_by_zero THEN
-- exception block
END;
END;
│COMMIT │
│…+I2++…│ │…+I2++…│
├subxact┤ ├subxact┤ ├subxact┤
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│ │→│!ERROR!│
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML COMMIT DML RELEASE

One could imagine silencing that newly found error message, but that will make us fail to identify errors in other cases, so that is not a possible path.

2. Also consider that if the code after the COMMIT causes an error, then by the established semantics, we search inside the stack for an exception block that catches the error, so we can ROLLBACK to the savepoint of that level and execute the block under the THEN clause. But like in the previous case, no such subtransaction exists to rollback to.

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- DML
PERFORM 1/0;
-- DML
EXCEPTION
WHEN division_by_zero THEN
-- exception block
END;
END;
│COMMIT │
│…+I2++…│ │…+I2++…│
├subxact┤ ├subxact┤ ├subxact┤
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│ │→│!ERROR!│
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML COMMIT PERFORM ROLLBACK

Just as in the previous case, silently ignoring the subsequent errors is not an option, since it would hide problems in user code that are currently detected.

3. And consider yet a third scenario in which a COMMIT effectively commits, but re-creates a transaction with the same structure of subtransactions as before, so the exception blocks will work, only with no data, since the data was COMMITed.

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- DML
PERFORM 1/0;
-- DML
EXCEPTION
WHEN division_by_zero THEN
-- exception block
END;
END;
│COMMIT │
│…+I2++…│ │…+I2++…│
├subxact┤ ├subxact┤ ├subxact┤ │…+I3++…│
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→├subxact┤→├subxact┤
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML COMMIT PERFORM ROLLBACK

In this case, when the exception is thrown and caught, the handler can find the relevant subtransaction to roll back to. However, it cannot roll back all data contained in the exception block, which would be I2+I3.

4. Alternatively, we could imagine a scenario in which a COMMIT doesn't commit at all, but maintains the transaction with the same structure of subtransactions as before, including the data, so it can honor the future releases and rollbacks.

BEGIN
-- DML
BEGIN
-- DML
COMMIT;
-- DML
PERFORM 1/0;
-- DML
EXCEPTION
WHEN division_by_zero THEN
-- exception block
END;
END;
│COMMIT │ │…+I3++…│
│…+I2++…│ │…+I2++…│ │…+I2++…│
├subxact┤ ├subxact┤ ├subxact┤ ├subxact┤
│ │→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→│…+I1++…│→…
└───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘ └───────┘
BEGIN DML BEGIN DML COMMIT PERFORM ROLLBACK

This effectively preserves the behavior of the exception block, but it turns the COMMIT into a no-op, which is certainly not the expected behavior for a COMMIT.

So there are many more scenarios that we can concoct, but they all violate the semantics of one basic feature or another, especially when an inner procedure would ruin the semantics expected by an outer procedure, since they can be provided by third parties that are not aware of the broken semantics at the time of writing.

The reason why EDB-SPL is capable of providing the behavior of PL/SQL is that this different behavior was defined for it since the beginning, and that pertains to when subtransactions are released or rolled back, as well as what remains after a commit happens. So if you need PL/SQL compatibility regarding committing transactions inside exception blocks, use EDB-SPL, which brings the expected semantics for the compatibility use case.

In summary, the error message ERROR: cannot commit while a subtransaction is active exists to preserve the semantics of the SQL and PL/pgSQL languages that have existed since before procedures were introduced.