PostgreSQL 0B000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 0B000: invalid transaction initiation

PostgreSQL error code 0B000 invalid transaction initiation occurs when a transaction is started in a context where it is not allowed or when it conflicts with an already-active transaction state. This commonly happens when a BEGIN statement is issued inside an existing transaction block, inside a trigger function, or when application-level connection pooling leaves transactions in an inconsistent state. Understanding PostgreSQL's transaction model is key to resolving this error quickly.

---

Top 3 Causes and Fixes

1. Nested BEGIN Inside an Active Transaction

PostgreSQL does not support nesting BEGIN statements directly. Calling BEGIN when you are already inside a transaction block triggers this error. Use SAVEPOINT instead to achieve logical nesting.

-- BAD: Causes 0B000
BEGIN;
  INSERT INTO orders (customer_id, amount) VALUES (1, 150.00);
  BEGIN; -- ERROR: there is already a transaction in progress
COMMIT;

-- GOOD: Use SAVEPOINT for logical nesting
BEGIN;
  INSERT INTO orders (customer_id, amount) VALUES (1, 150.00);
  SAVEPOINT my_savepoint;

  INSERT INTO order_items (order_id, product_id) VALUES (1, 10);
  -- On error: ROLLBACK TO SAVEPOINT my_savepoint;

  RELEASE SAVEPOINT my_savepoint;
COMMIT;

2. Transaction Control Commands Inside Triggers or Functions

Triggers run within the context of an existing transaction. Using BEGIN, COMMIT, or ROLLBACK inside a trigger function is not allowed and will raise 0B000. Remove all transaction control statements from trigger bodies. If you need autonomous transaction behavior, use a PostgreSQL PROCEDURE (PostgreSQL 11+).

-- BAD: Transaction control in a trigger
CREATE OR REPLACE FUNCTION bad_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    BEGIN;   -- ERROR: not allowed inside trigger
    INSERT INTO audit_log (action) VALUES (TG_OP);
    COMMIT;  -- ERROR
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- GOOD: No transaction control inside trigger
CREATE OR REPLACE FUNCTION good_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, logged_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- GOOD: Use PROCEDURE for autonomous-style transactions (PG 11+)
CREATE OR REPLACE PROCEDURE write_audit_log(p_action TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (action, logged_at) VALUES (p_action, NOW());
    COMMIT; -- Allowed inside PROCEDURE
END;
$$;

3. Dirty Transaction State from Connection Pooling

Connection poolers (PgBouncer, HikariCP, etc.) may recycle connections that still have an open or aborted transaction. When application code then issues a BEGIN, it collides with the leftover state.

-- Check for sessions stuck in transaction
SELECT pid, usename, state, state_change, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY state_change;

-- Terminate long-running idle transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

-- Always reset connection state before reuse
ROLLBACK; -- Clear any leftover transaction state
BEGIN;
  -- your actual work here
COMMIT;

---

Quick Fix Checklist

• Never call BEGIN inside a trigger or PL/pgSQL function body
• Always use SAVEPOINT / ROLLBACK TO SAVEPOINT for logical nesting
• Ensure connection pools are configured to reset session state on connection return
• Use ORM transaction management APIs instead of raw BEGIN/COMMIT strings

---

Prevention Tips

Set idle transaction timeout to automatically kill stale transactions and prevent recycled connections from carrying bad state:

-- Apply at database level
ALTER DATABASE myapp SET idle_in_transaction_session_timeout = '5min';

-- Verify the setting
SHOW idle_in_transaction_session_timeout;

Use pg_stat_activity monitoring to proactively detect stuck transactions before they cause application errors. Integrate this query into your monitoring system and alert when idle in transaction sessions exceed a defined threshold.

---

Related Error Codes

Code	Name	Notes
25000	invalid_transaction_state	Parent class of transaction state errors
25001	active_sql_transaction	Command not allowed in an active transaction
25P02	in_failed_sql_transaction	Executing commands after a transaction error without ROLLBACK
2D000	invalid_transaction_termination	Improper COMMIT/ROLLBACK usage in procedures

---

📖 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.