PostgreSQL 2D000: invalid transaction termination
COMMIT or ROLLBACK ran inside a procedure or DO block that can't control transactions, for example one called inside an explicit transaction or from a function.
- SQLSTATE
2D000- Name
invalid_transaction_termination- Category
- Invalid Transaction Termination
What it means
Since PostgreSQL 11, procedures (CALL) and DO blocks may run COMMIT and ROLLBACK. That only works when they are called at the top level, not inside a transaction the caller opened. If a procedure is called after BEGIN, from a function, from a trigger, or through a path that wraps it in a transaction, its COMMIT fails with 2D000.
Functions can never commit or roll back; their work is always part of the caller's transaction.
Common causes
- Calling the procedure inside
BEGIN ... COMMIT, or from a client with autocommit off. - Calling it from a function, a trigger or a
SELECT. - A
COMMITinside a function body rather than a procedure. - A
COMMITinside a block that has anEXCEPTIONclause, which runs as a subtransaction.
How to fix it
-
Call the procedure on its own, with autocommit on.
CALL archive_old_orders();
-
If you need transaction control, make it a procedure, not a function, and keep
COMMITout of blocks with exception handlers.CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $$ BEGIN LOOP DELETE FROM orders WHERE id IN ( SELECT id FROM orders WHERE created_at < now() - interval '2 years' LIMIT 1000); EXIT WHEN NOT FOUND; COMMIT; END LOOP; END; $$;
Related errors
25001cannot run inside a transaction block25P02current transaction is aborted3B001invalid savepoint specification
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.