PostgreSQL 3B000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 3B000: Savepoint Exception — What It Means and How to Fix It

PostgreSQL error code 3B000 savepoint_exception occurs when your code attempts to reference, release, or roll back to a savepoint that doesn't exist or is being used outside a valid transaction block. Savepoints are intermediate markers within a transaction that allow partial rollbacks without aborting the entire transaction. This error most commonly appears in ORM-heavy applications, connection-pooled environments, or complex batch processing logic where transaction lifecycle management is automated or poorly controlled.

Top 3 Causes

1. Referencing a Savepoint That No Longer Exists

Once a savepoint is released with RELEASE SAVEPOINT or implicitly discarded after a full rollback, any further reference to it triggers 3B000. This is the single most common cause in production environments.

-- BROKEN: Attempting to rollback to an already-released savepoint
BEGIN;

SAVEPOINT my_sp;
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
RELEASE SAVEPOINT my_sp;   -- savepoint is now gone

-- This will throw ERROR 3B000
ROLLBACK TO SAVEPOINT my_sp;

COMMIT;

-- FIXED: Decide rollback vs release BEFORE releasing
BEGIN;

SAVEPOINT my_sp;

-- Check condition first, then act
-- On success:
RELEASE SAVEPOINT my_sp;

-- On failure (use one OR the other, not both):
-- ROLLBACK TO SAVEPOINT my_sp;
-- RELEASE SAVEPOINT my_sp;   -- always release after rollback

COMMIT;

2. Using SAVEPOINT Outside a Transaction Block

SAVEPOINT is only valid inside an explicit transaction block started with BEGIN. In autocommit mode or after a transaction has already been committed or rolled back, attempting to use a savepoint raises 3B000.

-- BROKEN: No BEGIN, autocommit mode is on
SAVEPOINT sp1;  -- ERROR 3B000: no active transaction

-- FIXED: Always wrap savepoints inside an explicit transaction
BEGIN;

SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('alice');
RELEASE SAVEPOINT sp1;

COMMIT;

-- Check current transaction state
SELECT
    txid_current() AS txid,
    current_setting('transaction_isolation') AS isolation;

3. ORM / Connection Pool Savepoint Conflicts

Frameworks like Django, SQLAlchemy, and Hibernate silently create and manage savepoints internally. When application code manually controls transactions on top of ORM-managed sessions, or when a connection pool recycles a session with a dirty transaction state, the expected savepoint structure no longer matches the actual database state — triggering 3B000.

-- Simulating what Django's atomic() does internally
BEGIN;

SAVEPOINT "django_savepoint_1";
INSERT INTO app_order (product_id, qty) VALUES (10, 2);
RELEASE SAVEPOINT "django_savepoint_1";

-- If manual code interferes here and issues ROLLBACK,
-- the ORM's next RELEASE will raise 3B000
-- because the savepoint was wiped by the full rollback

COMMIT;

-- Safe batch processing pattern with unique savepoint names
BEGIN;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id FROM pending_jobs LOOP
        EXECUTE format('SAVEPOINT sp_%s', rec.id);
        BEGIN
            UPDATE pending_jobs SET status = 'done' WHERE id = rec.id;
            EXECUTE format('RELEASE SAVEPOINT sp_%s', rec.id);
        EXCEPTION WHEN OTHERS THEN
            EXECUTE format('ROLLBACK TO SAVEPOINT sp_%s', rec.id);
            EXECUTE format('RELEASE SAVEPOINT sp_%s', rec.id);
            RAISE NOTICE 'Job % failed: %', rec.id, SQLERRM;
        END;
    END LOOP;
END;
$$;

COMMIT;

Quick Fix Solutions

Always release after rollback: After ROLLBACK TO SAVEPOINT, the savepoint still exists — release it explicitly before creating a new one with the same name.
Wrap in exception handlers: Use PL/pgSQL BEGIN ... EXCEPTION WHEN OTHERS blocks to ensure savepoints are always properly cleaned up.
Check autocommit settings: Verify your driver's autocommit mode (connection.autocommit = False in psycopg2) before using savepoints.
Avoid PgBouncer transaction mode with savepoints: Use pool_mode = session when your application relies on savepoints.

Prevention Tips

1. Use a helper procedure to encapsulate savepoint logic:

CREATE OR REPLACE PROCEDURE run_with_savepoint(sp_name TEXT, sql_cmd TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('SAVEPOINT %I', sp_name);
    BEGIN
        EXECUTE sql_cmd;
        EXECUTE format('RELEASE SAVEPOINT %I', sp_name);
    EXCEPTION WHEN OTHERS THEN
        EXECUTE format('ROLLBACK TO SAVEPOINT %I', sp_name);
        EXECUTE format('RELEASE SAVEPOINT %I', sp_name);
        RAISE;
    END;
END;
$$;

2. Monitor idle-in-transaction sessions to catch savepoint leaks early:

SELECT pid, usename, state, query, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

Related Errors

Code	Name	Notes
`3B001`	`invalid_savepoint_specification`	Subcode of 3B000; invalid or non-existent savepoint name
`25P02`	`in_failed_sql_transaction`	Occurs when SQL runs after an unhandled error without rollback
`40001`	`serialization_failure`	Forces transaction abort; must be handled alongside savepoint retry logic
`25000`	`invalid_transaction_state`	Triggered when transaction state is invalid for the requested operation

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

DEV Community