DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 3B001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 3B001: Invalid Savepoint Specification

PostgreSQL error 3B001 invalid_savepoint_specification occurs when you reference a savepoint that doesn't exist or has already been released within a transaction. Savepoints are powerful mid-transaction checkpoints that allow partial rollbacks without aborting the entire transaction. This error is a subclass of 3B000 savepoint_exception and is typically caused by naming mistakes or improper savepoint lifecycle management.

Top 3 Causes

1. Referencing a Non-Existent Savepoint Name

Typos or incorrect savepoint names when issuing ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT are the most common cause of this error.

-- Incorrect: typo in savepoint name
BEGIN;
SAVEPOINT my_savepoint;
INSERT INTO orders (customer_id, amount) VALUES (1, 100.00);

-- Typo: "my_savepint" does not exist -> ERROR 3B001
ROLLBACK TO SAVEPOINT my_savepint;

-- Correct: use the exact savepoint name
BEGIN;
SAVEPOINT my_savepoint;
INSERT INTO orders (customer_id, amount) VALUES (1, 100.00);

ROLLBACK TO SAVEPOINT my_savepoint;  -- Works correctly
COMMIT;
Enter fullscreen mode Exit fullscreen mode

2. Using a Savepoint After It Has Been Released

Once you RELEASE a savepoint, it no longer exists. Attempting to roll back to it afterward triggers error 3B001.

-- Incorrect: attempting to rollback after RELEASE
BEGIN;
SAVEPOINT step_one;
INSERT INTO audit_log (event) VALUES ('action started');
RELEASE SAVEPOINT step_one;  -- savepoint is now gone

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

-- Correct: re-create the savepoint if needed in a loop
BEGIN;
SAVEPOINT step_one;
INSERT INTO audit_log (event) VALUES ('action started');
RELEASE SAVEPOINT step_one;

-- Create a fresh savepoint with the same name after releasing
SAVEPOINT step_one;
INSERT INTO audit_log (event) VALUES ('action completed');
RELEASE SAVEPOINT step_one;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Executing Savepoint Commands Outside a Transaction Block

Savepoints are only valid inside an active transaction. Running savepoint commands without BEGIN — common in autocommit environments or misconfigured ORM setups — can trigger this error.

-- Incorrect: no explicit transaction block
SAVEPOINT orphan_point;  -- May raise an error or warning

-- Correct: always wrap savepoints in an explicit transaction
BEGIN;

SAVEPOINT balance_check;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

DO $$
DECLARE v_bal NUMERIC;
BEGIN
    SELECT balance INTO v_bal FROM accounts WHERE account_id = 1;
    IF v_bal < 0 THEN
        ROLLBACK TO SAVEPOINT balance_check;
        RAISE EXCEPTION 'Insufficient balance';
    END IF;
END;
$$;

RELEASE SAVEPOINT balance_check;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Double-check savepoint names — ensure the name in ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT exactly matches the one used in SAVEPOINT.
  2. Track savepoint state — use a boolean flag in PL/pgSQL to know whether a savepoint is currently active before referencing it.
  3. Always use explicit BEGIN — never rely on implicit transaction handling when using savepoints. 
-- Safe savepoint pattern with exception handling
CREATE OR REPLACE FUNCTION safe_insert(p_data TEXT) RETURNS VOID AS $$
DECLARE
    v_sp TEXT := 'sp_safe_insert';
    v_active BOOLEAN := FALSE;
BEGIN
    EXECUTE format('SAVEPOINT %I', v_sp);
    v_active := TRUE;

    INSERT INTO data_table (payload) VALUES (p_data);

    EXECUTE format('RELEASE SAVEPOINT %I', v_sp);
    v_active := FALSE;
EXCEPTION WHEN OTHERS THEN
    IF v_active THEN
        EXECUTE format('ROLLBACK TO SAVEPOINT %I', v_sp);
    END IF;
    RAISE;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Centralize savepoint names — define savepoint names as constants in your application layer or as PL/pgSQL variables. Avoid constructing savepoint names dynamically with unvalidated string concatenation.
  • Always pair SAVEPOINT with exception handling — every savepoint should be accompanied by an EXCEPTION WHEN OTHERS block that guarantees proper ROLLBACK TO or RELEASE execution, preventing orphaned or double-referenced savepoints.

Related Errors

  • 3B000 — savepoint_exception: Parent error class for all savepoint-related errors.
  • 25P01 — no_active_sql_transaction: Raised when savepoint commands run outside any active transaction.
  • 25000 — invalid_transaction_state: General invalid transaction state, often seen alongside savepoint misuse.

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

Top comments (0)