PostgreSQL Invalid Cursor State (SQLSTATE 24000)

PostgreSQL raises ERROR: cursor "<name>" does not exist or ERROR: portal "<name>" cannot be run with SQLSTATE 24000 (invalid_cursor_state) when a cursor operation such as FETCH, MOVE, or CLOSE is attempted on a cursor that is not open, does not exist in the current transaction scope, or is in a state that does not permit the requested operation. The condition name is invalid_cursor_state, belonging to error class 24 (Invalid Cursor State).

What This Error Means

Error class 24 covers all conditions related to cursor lifecycle violations. A cursor in PostgreSQL is a named portal — a server-side construct that holds a query's result set and a position pointer for iterating through rows. Cursors must be explicitly declared and opened before any FETCH, MOVE, or CLOSE can be issued against them. SQLSTATE 24000 is raised when those lifecycle rules are violated.

The most common trigger is attempting to use a cursor after its transaction has ended. In PostgreSQL, cursors declared without the WITH HOLD clause are tied to their enclosing transaction and are automatically destroyed when the transaction commits or rolls back. Any subsequent FETCH or CLOSE against such a cursor after the transaction ends produces this error.

This error also appears in PL/pgSQL procedures and functions when cursor variables are used before being opened, or when the same cursor variable is opened twice without being closed in between. Unlike some other database engines, PostgreSQL's cursor state is strictly enforced at the server level — there is no "auto-reopen" behavior.

Common Causes

1. Using a cursor after the transaction commits. A cursor declared without WITH HOLD is destroyed when the transaction ends. Calling FETCH outside the original transaction produces invalid_cursor_state.

2. Declaring a cursor but forgetting to OPEN it in PL/pgSQL. PL/pgSQL requires an explicit OPEN cursor_name FOR ... statement before FETCH can be called. Skipping the OPEN step results in this error.

3. Calling FETCH or CLOSE on an already-closed cursor. Once CLOSE cursor_name is issued, the portal is destroyed. Issuing a second CLOSE or any FETCH against it afterward triggers 24000.

4. Scrollable cursor direction violations. Using FETCH PRIOR, FETCH ABSOLUTE, or FETCH RELATIVE on a cursor that was not declared as SCROLL causes a related cursor-state error.

5. Cursor name collision or typo. A FETCH referencing a mistyped cursor name will fail with a "cursor does not exist" variant of 24000.

6. Using cursors across connection pool boundaries. Application-level or ORM-managed connection pools may return connections where the cursor no longer exists, because cursors are session-scoped and not persisted across pooled connections.

How to Fix invalid_cursor_state

1. Keep cursor operations within the same transaction. Declare, open, use, and close the cursor before issuing COMMIT or ROLLBACK. If you need a cursor to survive a commit, declare it WITH HOLD:

   BEGIN;
   DECLARE my_cursor CURSOR WITH HOLD FOR SELECT id, name FROM users ORDER BY id;
   COMMIT;  -- cursor survives because of WITH HOLD
   
   FETCH 10 FROM my_cursor;
   CLOSE my_cursor;
   

2. Always OPEN cursor variables in PL/pgSQL before FETCH. Cursor variables in PL/pgSQL functions must be explicitly opened:

   DO $$
   DECLARE
     cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
     rec orders%ROWTYPE;
   BEGIN
     OPEN cur;          -- required before FETCH
     FETCH cur INTO rec;
     RAISE NOTICE 'id: %', rec.id;
     CLOSE cur;
   END;
   $$;
   

3. Guard against double-close with an ISOPEN check (PL/pgSQL). Use the %ISOPEN cursor attribute to avoid closing an already-closed cursor:

   IF cur%ISOPEN THEN
     CLOSE cur;
   END IF;
   

4. Declare scrollable cursors explicitly when using non-sequential fetch directions. If you need FETCH PRIOR or absolute positioning, declare the cursor with SCROLL:

   DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM products ORDER BY id;
   

5. Avoid sharing cursors across connection pool checkouts. Cursors are scoped to a database session. If your application uses a connection pool, never store a cursor name and reuse it on a connection obtained from the pool in a later request. Either complete all cursor operations within a single pool checkout, or use WITH HOLD carefully with awareness of the memory implications.

6. Check for cursor existence before operating on it. In dynamic PL/pgSQL code, you can query pg_cursors to verify a cursor exists before attempting to use it:

   IF EXISTS (SELECT 1 FROM pg_cursors WHERE name = 'my_cursor') THEN
     FETCH my_cursor INTO rec;
   END IF;
   

Additional Information

• WITH HOLD cursors consume server memory proportional to their result set size, because PostgreSQL materializes the entire result when the transaction commits. Use them judiciously on large result sets.
• Related SQLSTATE codes in class 24: there are no other standard sub-codes under class 24 in PostgreSQL's default error vocabulary — 24000 is the sole condition. However, some cursor direction errors may surface as 42000 (syntax error) or 0A000 (feature not supported) depending on the specific violation.
• The pg_cursors system view lists all open cursors in the current session and is useful for debugging cursor lifecycle issues.
• JDBC and libpq-based drivers expose this error as PSQLException (Java) or a PGresult with PGRES_FATAL_ERROR status. The SQLSTATE 24000 is available via getSQLState() in JDBC.
• SQLAlchemy and Django ORM rarely expose raw cursors, so this error is most common in code using server-side cursors explicitly (e.g., psycopg2's server_side_cursor or named cursors).
• This error has existed since early PostgreSQL versions; no version-specific behavioral changes are known for 24000 itself.

Frequently Asked Questions

Why does my cursor stop working after COMMIT? Unless declared WITH HOLD, PostgreSQL cursors are tied to their transaction. The moment a COMMIT or ROLLBACK is issued, all non-holdable cursors in that transaction are automatically closed. To keep a cursor alive across a commit, declare it as CURSOR WITH HOLD FOR ....

Can I reopen a cursor after closing it? No. Once a cursor is closed with CLOSE cursor_name, its portal is destroyed. To iterate again, you must declare (and open, for PL/pgSQL refcursors) a new cursor. You can reuse the same cursor variable name by declaring a new cursor under the same name in a new transaction or PL/pgSQL block.

How do I list all currently open cursors in my session? Query the pg_cursors view: SELECT name, statement, is_holdable, is_scrollable FROM pg_cursors;. This shows every open portal in the current session, which is useful when debugging cursor-state errors in long-running or complex PL/pgSQL procedures.

Does this error affect the transaction or connection state? invalid_cursor_state is an error-level condition, meaning it aborts the current statement and places the transaction in an error state (requiring ROLLBACK or ROLLBACK TO SAVEPOINT before further work can proceed). The connection itself remains intact — only the ongoing transaction is affected.