PostgreSQL 24000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 24000: Invalid Cursor State

PostgreSQL error code 24000, invalid_cursor_state, occurs when a cursor operation is attempted on a cursor that is not in a valid state for that operation. This typically means you're trying to FETCH from a cursor that hasn't been opened, reusing a cursor after a transaction has ended, or navigating a non-scrollable cursor in an unsupported direction. Understanding cursor lifecycle management is key to resolving and preventing this error.

Top 3 Causes

1. FETCH or CLOSE on an Unopened Cursor

Attempting to FETCH data from a cursor that was never opened with the OPEN statement is the most common cause.

-- BAD: FETCH without OPEN
DO $$
DECLARE
    cur CURSOR FOR SELECT id, name FROM employees;
    rec RECORD;
BEGIN
    FETCH cur INTO rec;  -- ERROR 24000: cursor not open
    RAISE NOTICE '%', rec.name;
END;
$$;

-- GOOD: Always OPEN before FETCH
DO $$
DECLARE
    cur CURSOR FOR SELECT id, name FROM employees;
    rec RECORD;
BEGIN
    OPEN cur;           -- Open first

    FETCH cur INTO rec;
    IF FOUND THEN
        RAISE NOTICE 'Name: %', rec.name;
    END IF;

    CLOSE cur;          -- Always close when done
END;
$$;

2. Reusing a Cursor After Transaction End

By default, PostgreSQL cursors declared WITHOUT HOLD are automatically closed when the transaction ends. Trying to use such a cursor after a COMMIT or ROLLBACK triggers error 24000.

-- BAD: Accessing cursor after COMMIT
BEGIN;
DECLARE my_cur CURSOR FOR SELECT id FROM employees;
COMMIT;  -- Cursor is now closed automatically

FETCH my_cur;  -- ERROR 24000: cursor does not exist

-- GOOD: Use WITH HOLD to persist cursor beyond transaction
BEGIN;
DECLARE my_cur CURSOR WITH HOLD FOR
    SELECT id, name FROM employees ORDER BY id;
COMMIT;  -- Transaction ends, but cursor remains open

FETCH 10 FROM my_cur;  -- Works fine after commit
CLOSE my_cur;          -- Must close explicitly

3. Invalid Scroll Direction on a NO SCROLL Cursor

Using backward navigation commands (FETCH PRIOR, FETCH ABSOLUTE, FETCH RELATIVE) on a cursor declared as NO SCROLL will raise this error.

-- BAD: Backward fetch on NO SCROLL cursor
BEGIN;
DECLARE cur NO SCROLL CURSOR FOR
    SELECT id, name FROM employees ORDER BY id;

FETCH NEXT FROM cur;   -- OK
FETCH PRIOR FROM cur;  -- ERROR 24000: cursor can only scan forward

CLOSE cur;
COMMIT;

-- GOOD: Declare SCROLL cursor for bidirectional navigation
BEGIN;
DECLARE cur SCROLL CURSOR FOR
    SELECT id, name FROM employees ORDER BY id;

FETCH NEXT FROM cur;        -- Forward
FETCH PRIOR FROM cur;       -- Backward - now valid
FETCH ABSOLUTE 1 FROM cur;  -- Jump to first row
FETCH RELATIVE 3 FROM cur;  -- Move 3 rows forward

CLOSE cur;
COMMIT;

Quick Fix Solutions

Always follow OPEN → FETCH → CLOSE order in PL/pgSQL blocks.
Use WITH HOLD when the cursor needs to survive a transaction boundary.
Declare SCROLL explicitly when bidirectional navigation is required.
Wrap cursor logic in EXCEPTION blocks to ensure cleanup on errors:

DO $$
DECLARE
    cur CURSOR FOR SELECT id FROM employees;
    rec RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'ID: %', rec.id;
    END LOOP;
    CLOSE cur;
EXCEPTION
    WHEN invalid_cursor_state THEN
        RAISE WARNING 'Cursor error caught. Cleaning up.';
        BEGIN
            CLOSE cur;
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
END;
$$;

Prevention Tips

Enforce cursor lifecycle discipline: Always pair every OPEN with a CLOSE, and validate cursor state before performing fetch operations. Use EXCEPTION blocks to guarantee resource cleanup even when errors occur mid-execution.
Keep cursor scope within transaction boundaries: Design your stored procedures so that cursor usage stays within a single transaction wherever possible. Reserve WITH HOLD cursors only for cases where cross-transaction cursor access is genuinely necessary, as they consume additional server resources by materializing the result set.

Related Errors

Code	Name	Brief Description
`34000`	invalid_cursor_name	Referenced cursor name does not exist
`25001`	active_sql_transaction	Transaction state conflicts with cursor operation
`55000`	object_not_in_prerequisite_state	Object not in the required state for the operation

📖 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