PostgreSQL 3B001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 3B001: invalid savepoint specification

The 3B001 invalid savepoint specification error occurs in PostgreSQL when you attempt to reference a savepoint that does not exist within the current transaction using ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. This typically means the savepoint was never created, has already been released, or was implicitly invalidated by a prior rollback. Understanding savepoint lifecycle management is essential for building robust transactional logic.

Top 3 Causes

1. Referencing a Savepoint That Was Never Created

The most common cause is a simple typo or logic error where the savepoint name used in ROLLBACK TO SAVEPOINT doesn't match any previously declared savepoint.

-- ❌ Incorrect: referencing a savepoint that was never created
BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (101, 5000);
ROLLBACK TO SAVEPOINT sp_order;  -- ERROR 3B001: savepoint "sp_order" does not exist
COMMIT;

-- ✅ Correct: always declare the savepoint first
BEGIN;
SAVEPOINT sp_order;
INSERT INTO orders (customer_id, amount) VALUES (101, 5000);

SAVEPOINT sp_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 201, 3);

-- Rollback only the item insert, keeping the order header
ROLLBACK TO SAVEPOINT sp_items;
COMMIT;

2. Referencing an Already Released Savepoint

Once you call RELEASE SAVEPOINT, that savepoint and all savepoints created after it are destroyed. Any subsequent reference to a released savepoint triggers 3B001.

-- ❌ Incorrect: referencing a savepoint after releasing it
BEGIN;
SAVEPOINT sp_first;
INSERT INTO products (name, price) VALUES ('Widget', 9900);

SAVEPOINT sp_second;
INSERT INTO inventory (product_id, stock) VALUES (1, 100);

RELEASE SAVEPOINT sp_second;  -- sp_second is now gone

ROLLBACK TO SAVEPOINT sp_second;  -- ERROR 3B001: already released
COMMIT;

-- ✅ Correct: only reference savepoints that are still active
BEGIN;
SAVEPOINT sp_first;
INSERT INTO products (name, price) VALUES ('Widget', 9900);

SAVEPOINT sp_second;
INSERT INTO inventory (product_id, stock) VALUES (1, 100);

-- Roll back to sp_first (sp_second is automatically invalidated)
ROLLBACK TO SAVEPOINT sp_first;

-- Do NOT reference sp_second anymore — it no longer exists
INSERT INTO products (name, price) VALUES ('Gadget', 14900);
COMMIT;

3. Savepoints Invalidated by a Prior Rollback

When you execute ROLLBACK TO SAVEPOINT sp_x, all savepoints created after sp_x are automatically removed. Referencing those invalidated savepoints afterward causes 3B001.

-- ❌ Incorrect: referencing a savepoint invalidated by a prior rollback
BEGIN;
SAVEPOINT sp_a;
INSERT INTO logs (msg) VALUES ('step A');

SAVEPOINT sp_b;
INSERT INTO logs (msg) VALUES ('step B');

SAVEPOINT sp_c;
INSERT INTO logs (msg) VALUES ('step C');

ROLLBACK TO SAVEPOINT sp_a;  -- sp_b and sp_c are now invalid

ROLLBACK TO SAVEPOINT sp_b;  -- ERROR 3B001: sp_b no longer exists
COMMIT;

-- ✅ Correct: use PL/pgSQL exception handling for nested savepoint logic
DO $$
BEGIN
    INSERT INTO logs (msg) VALUES ('step A');
    SAVEPOINT sp_a;

    BEGIN
        INSERT INTO logs (msg) VALUES ('step B');
        SAVEPOINT sp_b;

        INSERT INTO logs (msg) VALUES ('step C - may fail');

    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp_a;
        INSERT INTO logs (msg) VALUES ('recovered at sp_a');
    END;
END;
$$;

Quick Fix Solutions

Verify savepoint names match exactly: Savepoint names in PostgreSQL are case-folded to lowercase unless quoted. SAVEPOINT MyPoint and ROLLBACK TO SAVEPOINT mypoint are equivalent, but ROLLBACK TO SAVEPOINT "MyPoint" is not.
Track savepoint lifecycle: Maintain a stack-like structure in your application to track which savepoints are currently active before referencing them.
Use PL/pgSQL EXCEPTION blocks: Instead of manually managing ROLLBACK TO SAVEPOINT, let PL/pgSQL's built-in exception handling manage implicit savepoints for you.

-- Safe pattern using PL/pgSQL exception handling
CREATE OR REPLACE FUNCTION process_with_savepoint() RETURNS VOID AS $$
BEGIN
    INSERT INTO audit (event) VALUES ('started');
    SAVEPOINT sp_main;

    BEGIN
        -- Risky operation
        INSERT INTO transactions (amount) VALUES (-99999);
        -- Additional validation
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Insert failed';
        END IF;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp_main;
        INSERT INTO audit (event) VALUES ('rolled back: ' || SQLERRM);
    END;

    RELEASE SAVEPOINT sp_main;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

1. Centralize savepoint name management: Define savepoint names as constants in your application layer or PL/pgSQL functions. Never scatter raw string literals for savepoint names across multiple code paths — this is the fastest route to a typo-induced 3B001.

2. Prefer PL/pgSQL exception blocks over manual savepoints: PostgreSQL's BEGIN...EXCEPTION...END block implicitly creates and manages savepoints for you. This eliminates the risk of referencing a stale or non-existent savepoint entirely, and is the recommended approach for most transactional stored procedures.

Related Errors

25P01 no_active_sql_transaction: Fired when SAVEPOINT is used outside a BEGIN block.
25P02 in_failed_sql_transaction: Occurs when any command other than ROLLBACK (or ROLLBACK TO SAVEPOINT) is executed in an aborted transaction.
3B000 invalid_savepoint_specification: The parent error class of 3B001, covering all savepoint specification violations.

📖 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