PostgreSQL 42P11 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P11: Invalid Cursor Definition

PostgreSQL error code 42P11 is raised when a cursor is declared with an invalid or unsupported definition. This typically happens when cursor options conflict, the syntax in a PL/pgSQL block is malformed, or the cursor is opened outside of a valid transaction context. Understanding the root cause is straightforward once you know the three most common triggers.

---

Top 3 Causes and Fixes

1. Conflicting Cursor Options (SCROLL / WITH HOLD)

PostgreSQL cursors support SCROLL, NO SCROLL, WITH HOLD, and WITHOUT HOLD. Combining these incorrectly causes a 42P11 error.

Problematic:

-- Ambiguous or conflicting option combination
BEGIN;
DECLARE bad_cursor NO SCROLL WITH HOLD CURSOR FOR
    SELECT id, name FROM employees;
-- Logical conflict depending on PostgreSQL version/context

Fixed:

-- Use SCROLL for bidirectional navigation
BEGIN;
DECLARE scroll_cursor SCROLL CURSOR FOR
    SELECT id, name FROM employees ORDER BY id;

FETCH NEXT FROM scroll_cursor;
FETCH PRIOR FROM scroll_cursor;
CLOSE scroll_cursor;
COMMIT;

-- Use WITH HOLD to keep cursor alive after COMMIT
BEGIN;
DECLARE hold_cursor WITH HOLD CURSOR FOR
    SELECT id, name FROM employees;
COMMIT;

FETCH NEXT FROM hold_cursor;  -- Still works after COMMIT
CLOSE hold_cursor;

---

2. Incorrect Cursor Declaration in PL/pgSQL

A very common mistake is misspelling or misplacing the CURSOR FOR syntax, especially when using parameterized cursors.

Problematic:

CREATE OR REPLACE FUNCTION bad_cursor_func()
RETURNS void AS $$
DECLARE
    -- Missing CURSOR FOR keyword
    emp_cursor := SELECT * FROM employees;
BEGIN
    OPEN emp_cursor;
END;
$$ LANGUAGE plpgsql;

Fixed:

-- Simple cursor
CREATE OR REPLACE FUNCTION simple_cursor_func()
RETURNS void AS $$
DECLARE
    emp_cursor CURSOR FOR
        SELECT id, name FROM employees WHERE active = true;
    rec RECORD;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Employee: %', rec.name;
    END LOOP;
    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

-- Parameterized cursor
CREATE OR REPLACE FUNCTION dept_cursor_func(p_dept INT)
RETURNS void AS $$
DECLARE
    dept_cursor CURSOR (dept_id INT) FOR
        SELECT id, name FROM employees WHERE department_id = dept_id;
    rec RECORD;
BEGIN
    OPEN dept_cursor(p_dept);
    LOOP
        FETCH dept_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Found: %', rec.name;
    END LOOP;
    CLOSE dept_cursor;
END;
$$ LANGUAGE plpgsql;

---

3. Opening a Cursor Outside a Transaction Block

Standard cursors (without WITH HOLD) require an active transaction. Declaring one outside a BEGIN/COMMIT block in autocommit mode leads to an invalid cursor definition.

Problematic:

-- In autocommit mode, no explicit transaction = instant commit = invalid cursor
DECLARE orphan_cursor CURSOR FOR SELECT * FROM employees;
FETCH ALL FROM orphan_cursor;  -- 42P11 or 34000 error

Fixed:

-- Always wrap in an explicit transaction
BEGIN;

DECLARE safe_cursor CURSOR FOR
    SELECT id, name, salary FROM employees
    WHERE salary > 50000
    ORDER BY salary DESC;

FETCH 5 FROM safe_cursor;
CLOSE safe_cursor;

COMMIT;

-- Or use a REFCURSOR-returning function for flexibility
CREATE OR REPLACE FUNCTION get_cursor(p_min_salary NUMERIC)
RETURNS refcursor AS $$
DECLARE
    ref refcursor := 'emp_ref';
BEGIN
    OPEN ref FOR
        SELECT id, name, salary FROM employees
        WHERE salary >= p_min_salary;
    RETURN ref;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT get_cursor(60000);
FETCH ALL FROM emp_ref;
CLOSE emp_ref;
COMMIT;

---

Prevention Tips

1. Always follow the DECLARE → OPEN → FETCH → CLOSE lifecycle. Make this a mandatory code review checklist item. Monitor open cursors regularly using the pg_cursors system view to catch unclosed cursors before they cause resource leaks.

-- Monitor open cursors
SELECT name, statement, is_holdable, is_scrollable, creation_time
FROM pg_cursors
ORDER BY creation_time;

1. Prefer REFCURSOR for application-level cursor handling. It is more flexible, easier to test, and avoids many of the declaration pitfalls that trigger 42P11. Always close WITH HOLD cursors explicitly after use, even in error-handling paths.

---

Related Errors

Code	Name	When It Occurs
34000	invalid cursor name	Referencing a cursor that doesn't exist
24000	invalid transaction state	Using a cursor outside a valid transaction
42601	syntax error	Completely malformed cursor syntax

---

📖 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.