PostgreSQL Invalid Cursor Definition (SQLSTATE 42P11)

PostgreSQL raises ERROR: invalid cursor definition (SQLSTATE 42P11, condition name invalid_cursor_definition) when a cursor is declared with a configuration that is structurally invalid or violates the rules PostgreSQL enforces on cursor declarations. The error belongs to error class 42 — Syntax Error or Access Rule Violation — and is most commonly encountered in PL/pgSQL stored procedures and functions.

What This Error Means

SQLSTATE class 42 covers errors related to syntax violations and access rule violations at parse or planning time. Code 42P11 is specific to cursor definitions that are syntactically or semantically malformed — for example, a cursor declared with WITH HOLD in a context that does not support it, or a cursor whose query contains constructs that are disallowed for cursors.

PostgreSQL evaluates cursor declarations at the time the DECLARE statement is executed (or when the function body containing the declaration is compiled). If the cursor's definition is structurally invalid, PostgreSQL raises 42P11 before any rows are fetched. The transaction is not automatically aborted by this error, but the cursor object is never created, so any subsequent FETCH or CLOSE against that cursor name will fail.

The condition is rare in plain SQL sessions because most cursor issues in interactive sessions surface as different errors (e.g., 42601 syntax error). It is more commonly seen in PL/pgSQL, where the rules around cursor variables, refcursor arguments, and bound vs. unbound cursors add additional constraints that can be violated programmatically.

Common Causes

1. Declaring a WITH HOLD cursor inside a function or procedure where it is not supported. WITH HOLD cursors must survive transaction boundaries; certain execution contexts (e.g., inside a PL/pgSQL block that does not commit) conflict with this.

2. Using a cursor variable bound to a query that contains FOR UPDATE or FOR SHARE with WITH HOLD. PostgreSQL does not allow holdable cursors to lock rows, because the rows would need to be held across commit boundaries.

3. Declaring a PL/pgSQL cursor with both a bound query and SCROLL or NO SCROLL in a way that conflicts with the query's plan. For example, explicitly marking a cursor SCROLL when the query uses a plan that cannot scroll backwards, in a context where PostgreSQL validates this eagerly.

4. Passing an already-open refcursor as an OUT parameter while trying to re-declare it. If the cursor name is already open within the same session, re-declaring it without first closing it can produce definition errors.

5. Using a cursor declaration inside a PL/pgSQL block that inherits an invalid cursor variable from an outer scope, causing the inner declaration to conflict with the inherited binding.

How to Fix invalid_cursor_definition

1. Remove WITH HOLD from cursors that do not need to survive commits. If you only need the cursor within a single transaction, declare it without WITH HOLD:

   -- Instead of:
   DECLARE my_cursor WITH HOLD FOR SELECT id FROM orders;
   
   -- Use:
   DECLARE my_cursor CURSOR FOR SELECT id FROM orders;
   

2. Do not combine WITH HOLD and row-locking clauses. If you need to lock rows, consume the cursor within the same transaction and drop WITH HOLD:

   -- This is invalid:
   DECLARE locking_cursor WITH HOLD FOR SELECT id FROM orders FOR UPDATE;
   
   -- Fix: remove WITH HOLD
   DECLARE locking_cursor CURSOR FOR SELECT id FROM orders FOR UPDATE;
   

3. Close the cursor before re-opening it. In PL/pgSQL, if you loop or call a function multiple times, ensure any cursor opened in a previous iteration is closed before the next declaration:

   DO $$
   DECLARE
     cur refcursor;
   BEGIN
     OPEN cur FOR SELECT 1;
     -- ... use cur ...
     CLOSE cur;   -- required before re-opening
     OPEN cur FOR SELECT 2;
     CLOSE cur;
   END;
   $$;
   

4. Be explicit about SCROLL / NO SCROLL only when you need backwards fetch. Omitting the keyword lets PostgreSQL choose the appropriate scrollability based on the query plan, avoiding conflicts:

   -- Let PostgreSQL decide scrollability:
   DECLARE my_cursor CURSOR FOR SELECT * FROM large_table;
   
   -- Only use SCROLL if you actually call FETCH BACKWARD:
   DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM large_table;
   

5. In PL/pgSQL, prefer FOR loop cursors for simple iteration rather than manually declaring and opening cursors, which eliminates most definition pitfalls:

   CREATE OR REPLACE FUNCTION process_orders() RETURNS void AS $$
   DECLARE
     rec orders%ROWTYPE;
   BEGIN
     FOR rec IN SELECT * FROM orders WHERE status = 'pending' LOOP
       -- process rec
     END LOOP;
   END;
   $$ LANGUAGE plpgsql;
   

Additional Information

• SQLSTATE 42P11 is a PostgreSQL-specific extension code (P prefix denotes a PostgreSQL-specific code within the class).
• Related codes in class 42 include:
  • 42601 — syntax_error: general SQL syntax errors
  • 42P01 — undefined_table: reference to a non-existent table
  • 42P12 — invalid_database_definition: invalid database object definition
  • 42P13 — invalid_function_definition: invalid function definition
• Most PostgreSQL client drivers (libpq, JDBC, psycopg2, node-postgres) surface this as a ProgrammingError or equivalent, with the SQLSTATE and message included in the exception.
• This error is not related to query performance and has no operational impact beyond the failed cursor declaration itself.
• The error was present in PostgreSQL since cursor support was introduced (PostgreSQL 7.x); the WITH HOLD restriction with row-locking is a long-standing design constraint.

Frequently Asked Questions

Why does WITH HOLD cause problems with FOR UPDATE? A WITH HOLD cursor keeps its result set accessible after the transaction commits. FOR UPDATE places locks on the rows. PostgreSQL cannot hold row locks across a commit boundary (that would block other transactions indefinitely), so the combination is explicitly forbidden and raises 42P11.

Can I use WITH HOLD cursors inside PL/pgSQL functions? Yes, but only in functions that issue explicit COMMIT statements (i.e., procedures called with CALL, not plain functions). In a regular PL/pgSQL function, which runs entirely within a single transaction from the caller's perspective, WITH HOLD is allowed syntactically but the cursor is automatically closed when the function returns and the transaction ends normally.

Is SQLSTATE 42P11 ever raised by an ORM like SQLAlchemy or ActiveRecord? Rarely directly — ORMs do not typically declare SQL-level cursors. However, if you use SQLAlchemy's connection.execute() with raw SQL containing a DECLARE statement, or invoke a stored procedure that has a faulty cursor declaration, the driver will propagate the 42P11 error as a ProgrammingError with the original PostgreSQL message intact.

How do I find which cursor declaration is causing the error? The PostgreSQL error message typically includes context like PL/pgSQL function my_func() line 12 at OPEN. Enable log_min_messages = DEBUG1 in postgresql.conf or check pg_last_error_context() immediately after catching the exception in PL/pgSQL to identify the exact declaration site.