PostgreSQL 25008 Error: Causes and Solutions Complete Guide

-- 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;

-- 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;

-- 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;
$$;

-- 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;

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