Let’s fix the 2D000 invalid transaction termination error in PostgreSQL. Bobcares, as a part of our PostgreSQL Support offers solutions to every query that comes our way.

Fixing the 2D000 Invalid Transaction Termination Error in PostgreSQL

In PostgreSQL, the 2D000 error code refers to a “invalid transaction termination,” which happens when attempting to commit or roll back a transaction in the wrong context. There are multiple BATCH JOBS in Oracle that user commit after processing a few records. These batch jobs process subsets and run transaction control statements COMMIT in the case of success and ROLLBACK in the event of failure.

When converting to PostgreSQL, even though users kept the same structure but encountered the below error:

In PostgreSQL, transaction control commands such as COMMIT and ROLLBACK aren’t allowed within PL/pgSQL functions, like in Oracle. PostgreSQL handles nested transactions differently, and therefore does not support COMMIT within a subtransaction initiated by an EXCEPTION block. The problem arises because PostgreSQL does not support transaction control calls (COMMIT, ROLLBACK) within PL/pgSQL functions that are nested or part of an EXCEPTION block.

Fix

The issue is here:

BEGIN
[...]
commit;
EXCEPTION
WHEN others THEN
[...]
END;

When we enter a BEGIN / END block that has an EXCEPTION handler, PostgreSQL starts a subtransaction. COMMIT would close a subtransaction, so we cannot commit there. If we think about it, it is the only way to do that correctly, since everything after the BEGIN needs to be rolled back if we enter the exception handler. Oracle does not care about transactional integrity in such a case, but PostgreSQL does.

Here, it is best to remove the COMMIT statements. It doesn’t look like we really need them. Otherwise, we have to move the outer loop to client code and commit there.

[Want to learn more? Click here to reach us.]

Conclusion

The article explains about the 2D000 invalid transaction termination error in PostgreSQL, along with a simple solution from our Techs.