How to fix 25005: no_active_sql_transaction_for_branch_transaction in PostgreSQL

-- Correct: SAVEPOINT inside transaction
BEGIN;
INSERT INTO users (name) VALUES ('test');
SAVEPOINT my_savepoint; -- This works
INSERT INTO users (name) VALUES ('test2');
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

-- Incorrect: SAVEPOINT without transaction
SAVEPOINT my_savepoint; -- Error 25005: no_active_sql_transaction_for_branch_transaction

-- Check if we're in a transaction
SELECT pg_backend_pid();

-- In application code, track transaction state explicitly
-- Example Python/psycopg2 pattern:
import psycopg2

conn = psycopg2.connect(...)
cursor = conn.cursor()

# Start transaction explicitly
try:
    cursor.execute("BEGIN;")
    # Now safe to create savepoints
    cursor.execute("SAVEPOINT checkpoint;")
    # ... more operations ...
    cursor.execute("COMMIT;")
except Exception as e:
    cursor.execute("ROLLBACK;")
    raise

-- Procedure that uses savepoints
CREATE OR REPLACE PROCEDURE process_order(order_id int)
LANGUAGE plpgsql
AS $$
BEGIN
    -- This assumes caller started a transaction
    SAVEPOINT order_processing;
    
    UPDATE orders SET status = 'processing' WHERE id = order_id;
    INSERT INTO order_logs (order_id, action) VALUES (order_id, 'started');
    
    -- If error occurs, rollback just the savepoint
    -- ROLLBACK TO SAVEPOINT order_processing;
    
    -- Otherwise, release the savepoint
    -- RELEASE SAVEPOINT order_processing;
END;
$$;

-- Call it correctly:
BEGIN;
CALL process_order(123);
COMMIT;

-- Calling without transaction causes 25005:
CALL process_order(123); -- Error!

@Transactional
public void processOrder(Long orderId) {
    // Framework manages transaction
    orderRepository.updateStatus(orderId, "PROCESSING");
    
    // Manual savepoint - may cause 25005 if framework transaction ended
    entityManager.unwrap(Session.class).createSQLQuery("SAVEPOINT checkpoint").executeUpdate();
}

@Transactional
public void processOrder(Long orderId) {
    TransactionStatus status = TransactionAspectSupport.currentTransactionStatus();
    Object savepoint = status.createSavepoint(); // Framework-managed savepoint
    
    try {
        orderRepository.updateStatus(orderId, "PROCESSING");
        status.releaseSavepoint(savepoint);
    } catch (Exception e) {
        status.rollbackToSavepoint(savepoint);
        throw e;
    }
}

-- Pattern for robust transaction handling
DO $$
DECLARE
    savepoint_name TEXT := 'my_savepoint';
BEGIN
    -- Start transaction if not already in one
    IF NOT pg_transaction_status() IN ('IDLE', 'INERROR') THEN
        BEGIN;
    END IF;
    
    -- Create savepoint
    EXECUTE 'SAVEPOINT ' || savepoint_name;
    
    -- Your operations here
    INSERT INTO audit_log (message) VALUES ('Operation started');
    
    -- If success, release savepoint
    EXECUTE 'RELEASE SAVEPOINT ' || savepoint_name;
    
    -- If we started the transaction, commit it
    IF pg_transaction_status() = 'INTRANS' THEN
        COMMIT;
    END IF;
    
EXCEPTION WHEN OTHERS THEN
    -- Rollback to savepoint if it exists
    BEGIN
        EXECUTE 'ROLLBACK TO SAVEPOINT ' || savepoint_name;
    EXCEPTION WHEN invalid_savepoint_specification THEN
        -- Savepoint doesn't exist, rollback entire transaction
        ROLLBACK;
    END;
    RAISE;
END;
$$;

-- Check current transaction status
SELECT pg_transaction_status();
-- Returns: 'idle', 'active', 'intrans', 'inerror', or 'unknown'

-- Check if savepoints exist in current transaction
SELECT count(*) FROM pg_catalog.pg_savepoints;

-- View current transaction ID
SELECT pg_current_xact_id();

-- Check for idle in transaction connections
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE state = 'idle in transaction';

-- Set timeout for idle transactions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();