PostgreSQL 2D000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 2D000: Invalid Transaction Termination

The 2D000 invalid transaction termination error occurs when PostgreSQL encounters a COMMIT or ROLLBACK statement in a context where transaction control is not permitted. The most common scenario is attempting to call these commands inside a regular PL/pgSQL function or a trigger function. PostgreSQL enforces strict rules about where transactions can be terminated to preserve data integrity and consistency.

---

Top 3 Causes

1. Using COMMIT/ROLLBACK Inside a Regular Function

Before PostgreSQL 11, no functions could issue transaction control commands. Even in modern versions, only PROCEDURE (not FUNCTION) is allowed to use COMMIT or ROLLBACK.

Broken code:

-- This triggers 2D000
CREATE OR REPLACE FUNCTION bad_function()
RETURNS void AS $$
BEGIN
    UPDATE orders SET status = 'done' WHERE status = 'pending';
    COMMIT;  -- ❌ Not allowed inside a FUNCTION
END;
$$ LANGUAGE plpgsql;

Fixed code — use PROCEDURE instead:

CREATE OR REPLACE PROCEDURE good_procedure()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders SET status = 'done' WHERE status = 'pending';
    COMMIT;  -- ✅ Allowed inside a PROCEDURE
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- Call with CALL, not SELECT
CALL good_procedure();

---

2. Attempting Transaction Control Inside a Trigger

Triggers always execute within the context of an existing transaction. Issuing COMMIT or ROLLBACK inside a trigger function directly conflicts with that model and always raises 2D000.

Broken code:

CREATE OR REPLACE FUNCTION bad_trigger_fn()
RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log(action) VALUES (TG_OP);
    COMMIT;  -- ❌ Absolutely forbidden in triggers
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Fixed code:

CREATE OR REPLACE FUNCTION good_trigger_fn()
RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log(action, logged_at)
    VALUES (TG_OP, NOW());
    -- ✅ No COMMIT needed — the caller's transaction handles it
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION good_trigger_fn();

---

3. Misusing SAVEPOINT and ROLLBACK TO SAVEPOINT

Using ROLLBACK TO SAVEPOINT after a RELEASE SAVEPOINT, or mixing COMMIT with savepoint logic incorrectly, can lead to invalid transaction state errors closely related to 2D000.

Broken code:

BEGIN;
    SAVEPOINT sp1;
    INSERT INTO accounts(name) VALUES ('Alice');
    RELEASE SAVEPOINT sp1;
    ROLLBACK TO SAVEPOINT sp1;  -- ❌ sp1 no longer exists
COMMIT;

Fixed code:

BEGIN;
    SAVEPOINT sp1;
    INSERT INTO accounts(name) VALUES ('Alice');

    -- Roll back only if needed, BEFORE releasing
    ROLLBACK TO SAVEPOINT sp1;  -- ✅ Valid, sp1 still exists

    INSERT INTO accounts(name) VALUES ('Bob');
    RELEASE SAVEPOINT sp1;  -- ✅ Release after you're done
COMMIT;

---

Quick Fix Solutions

Situation	Fix
COMMIT in a FUNCTION	Convert to PROCEDURE and call with CALL
COMMIT in a trigger	Remove it — let the outer transaction manage state
Bad SAVEPOINT order	Release savepoints only after all rollbacks are done

---

Prevention Tips

Use plpgsql_check in your CI pipeline. This open-source extension statically analyzes PL/pgSQL code and catches illegal transaction control usage before it ever reaches production.

-- Install and run plpgsql_check on a function
CREATE EXTENSION plpgsql_check;
SELECT * FROM plpgsql_check_function('your_function_name()');

Establish a clear team convention: any database routine that needs to issue COMMIT or ROLLBACK must be written as a PROCEDURE, not a FUNCTION. Document this rule in your team's SQL style guide and enforce it during code review. This single rule eliminates the vast majority of 2D000 errors in production environments.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.