// PostgreSQL error reference

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
Error message
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 COMMIT inside a function body rather than a procedure.
  • A COMMIT inside a block that has an EXCEPTION clause, which runs as a subtransaction.

How to fix it

  1. Call the procedure on its own, with autocommit on.

    CALL archive_old_orders();
  2. If you need transaction control, make it a procedure, not a function, and keep COMMIT out 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

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.

// fewer errors, faster fixes

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.