DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 3B000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 3B000: Savepoint Exception — Causes, Fixes & Prevention

PostgreSQL error 3B000: savepoint exception occurs when a savepoint-related command is used incorrectly within a transaction. This typically happens when referencing a savepoint name that doesn't exist, has already been released, or when savepoint commands are executed outside of an explicit transaction block. Understanding the savepoint lifecycle is essential to avoiding this error in production environments.

Top 3 Causes

1. Referencing a Non-Existent or Misspelled Savepoint Name

The most common cause is a typo or incorrect reference to a savepoint name that was never created or has already been released.

-- Bad: Typo in savepoint name
BEGIN;
SAVEPOINT my_savepoint;
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
ROLLBACK TO SAVEPOINT my_savepint;  -- Typo causes 3B000!
COMMIT;

-- Good: Correct savepoint name reference
BEGIN;
SAVEPOINT my_savepoint;
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
ROLLBACK TO SAVEPOINT my_savepoint;  -- Correct
INSERT INTO orders (product_id, quantity) VALUES (2, 5);
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Fix: Always define savepoint names as constants in your application code to eliminate typos.

2. Using Savepoints Outside a Transaction Block

Savepoint commands (SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT) are only valid inside an explicit transaction block started with BEGIN. Running them in autocommit mode will immediately trigger 3B000.

-- Bad: No transaction block (autocommit mode)
SAVEPOINT sp1;  -- ERROR: 3B000

-- Good: Wrap in explicit transaction
BEGIN;
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Rollback to savepoint if something goes wrong
ROLLBACK TO SAVEPOINT sp1;

-- Retry with corrected values
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Fix: Always ensure autocommit = False in your database driver (psycopg2, JDBC, etc.) before using savepoints.

3. Referencing a Released Savepoint

RELEASE SAVEPOINT destroys the savepoint and all savepoints created after it. Attempting to roll back to a released savepoint causes 3B000.

-- Bad: Referencing a released savepoint
BEGIN;
SAVEPOINT sp1;
INSERT INTO log_table (msg) VALUES ('step 1');

SAVEPOINT sp2;
INSERT INTO log_table (msg) VALUES ('step 2');

RELEASE SAVEPOINT sp2;         -- sp2 is now gone
ROLLBACK TO SAVEPOINT sp2;     -- ERROR: 3B000, sp2 no longer exists!
COMMIT;

-- Good: Track which savepoints are still active
BEGIN;
SAVEPOINT sp1;
INSERT INTO log_table (msg) VALUES ('step 1');

SAVEPOINT sp2;
INSERT INTO log_table (msg) VALUES ('step 2');

RELEASE SAVEPOINT sp2;         -- sp2 released

-- Roll back to sp1 which is still valid
ROLLBACK TO SAVEPOINT sp1;

SAVEPOINT sp2_retry;           -- Create a fresh savepoint
INSERT INTO log_table (msg) VALUES ('step 2 retry');

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Fix: Track your savepoint stack explicitly and never reference a savepoint after calling RELEASE on it.

Quick Fix Solutions

Use this safe pattern in PL/pgSQL to handle savepoints defensively:

CREATE OR REPLACE FUNCTION safe_upsert(p_id INT, p_value TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    SAVEPOINT upsert_point;

    INSERT INTO my_table (id, value) VALUES (p_id, p_value)
    ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value;

    RELEASE SAVEPOINT upsert_point;
    RETURN TRUE;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT upsert_point;
        RAISE NOTICE 'Upsert failed: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Centralize savepoint name management: Define savepoint names as constants or enums in your application layer. Dynamic names generated from timestamps or UUIDs can also help ensure uniqueness.

-- Use unique, descriptive savepoint names
BEGIN;
SAVEPOINT sp_user_insert_001;
-- ... operations ...
RELEASE SAVEPOINT sp_user_insert_001;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

2. Always pair SAVEPOINT with exception handling: Never use a savepoint without a corresponding EXCEPTION or try-catch block. This guarantees the savepoint is either properly released on success or rolled back on failure, preventing stale savepoint references from accumulating.

Related Errors

  • 3B001: invalid_savepoint_specification — The specific sub-error of 3B000 most commonly seen in practice; raised when a named savepoint cannot be found.
  • 25P01: no_active_sql_transaction — Raised when savepoint commands are used with no active transaction (autocommit mode).
  • 25000: invalid_transaction_state — General transaction state errors that may accompany 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)