PostgreSQL 25008 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 25008: held cursor requires same isolation level

PostgreSQL error code 25008 occurs when a WITH HOLD cursor — one that persists beyond a transaction boundary — is accessed within a new transaction that uses a different isolation level than the one in which the cursor was originally declared. Since holdable cursors retain snapshot information from their creation context, PostgreSQL enforces consistency by requiring that any subsequent transaction fetching from that cursor must match the original isolation level.

Top 3 Causes

1. Mismatched Isolation Level When Fetching from a WITH HOLD Cursor

The most common cause: the transaction that opens the cursor uses one isolation level (e.g., SERIALIZABLE), but the fetching transaction uses a different one (e.g., READ COMMITTED).

-- Step 1: Create WITH HOLD cursor under SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE my_cursor CURSOR WITH HOLD FOR
    SELECT id, name FROM orders ORDER BY id;
COMMIT;

-- Step 2: Attempt FETCH under default READ COMMITTED -> ERROR 25008
BEGIN;
FETCH 10 FROM my_cursor; -- ERROR: held cursor requires same isolation level

-- Fix: Match isolation level to cursor creation
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
FETCH 10 FROM my_cursor; -- Works correctly
COMMIT;

CLOSE my_cursor;

2. Connection Pool Reuse with Lingering Cursors

In pooled environments (PgBouncer, pgpool-II), a session with an open WITH HOLD cursor may be returned to the pool after COMMIT. When a new client reuses that connection with a different isolation level, error 25008 is triggered.

-- Always close WITH HOLD cursors before returning connection to pool
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE batch_cursor CURSOR WITH HOLD FOR
    SELECT id, payload FROM events WHERE processed = FALSE;
COMMIT;

-- Process records
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
FETCH 100 FROM batch_cursor;
COMMIT;

-- IMPORTANT: Close before connection is returned to pool
CLOSE batch_cursor;

-- Verify no holdable cursors remain open
SELECT name, is_holdable, creation_time
FROM pg_cursors
WHERE is_holdable = TRUE;

3. Dynamic Isolation Level Changes in Stored Procedures

Functions or procedures that alter the transaction isolation level dynamically — or are called from sessions with varying isolation settings — can unexpectedly trigger this error.

-- Problematic pattern: inconsistent isolation inside a procedure
CREATE OR REPLACE PROCEDURE fetch_from_held_cursor(cur_name TEXT)
LANGUAGE plpgsql AS $$
DECLARE
    v_row RECORD;
BEGIN
    -- If caller's isolation level differs from cursor creation level,
    -- this will raise 25008
    EXECUTE format('FETCH 1 FROM %I', cur_name) INTO v_row;
    RAISE NOTICE 'Fetched: %', v_row;
END;
$$;

-- Safe pattern: enforce consistent isolation level
CREATE OR REPLACE PROCEDURE safe_batch_process()
LANGUAGE plpgsql AS $$
DECLARE
    v_id INTEGER;
BEGIN
    -- Use consistent isolation level throughout
    FOR v_id IN
        SELECT id FROM tasks WHERE status = 'PENDING' ORDER BY id
    LOOP
        UPDATE tasks SET status = 'DONE' WHERE id = v_id;
        COMMIT; -- Autonomous commit between iterations if needed
    END LOOP;
END;
$$;

Quick Fix Solutions

-- Check current transaction isolation level
SHOW transaction_isolation;
SELECT current_setting('transaction_isolation');

-- Set default isolation level per role to ensure consistency
ALTER ROLE app_user SET default_transaction_isolation = 'read committed';

-- Monitor open holdable cursors
SELECT name, statement, is_holdable, creation_time,
       NOW() - creation_time AS age
FROM pg_cursors
WHERE is_holdable = TRUE
ORDER BY creation_time;

-- Force close a specific cursor (within the same session)
CLOSE my_cursor;

Prevention Tips

Always explicitly set and document isolation levels when using WITH HOLD cursors. Establish a team coding convention that every transaction touching a holdable cursor must declare its isolation level explicitly — never rely on session defaults that may differ across connection pool clients.
Close WITH HOLD cursors as soon as they are no longer needed, especially before returning connections to a pool. Add cursor cleanup logic in exception handlers and application-level finally blocks to guarantee cursors are always closed, preventing both 25008 errors and unnecessary memory consumption on the server.

-- Example: Safe cleanup pattern
DO $$
BEGIN
    -- ... use cursor ...
    CLOSE my_cursor;
EXCEPTION
    WHEN OTHERS THEN
        CLOSE my_cursor;
        RAISE;
END;
$$;

Related Errors

25001 (active_sql_transaction): Raised when attempting to change isolation level inside an already-active transaction.
25006 (read_only_sql_transaction): Triggered by write operations in a read-only transaction; same error family as 25008.
34000 (invalid_cursor_name): Occurs when referencing a cursor that has already been closed or never existed.

📖 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