PostgreSQL 42P11 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P11: Invalid Cursor Definition

PostgreSQL error code 42P11 indicates an invalid cursor definition, meaning the cursor you declared contains a query or option combination that PostgreSQL cannot accept. This typically occurs inside PL/pgSQL functions or procedures when the cursor's associated query violates PostgreSQL's cursor rules. Understanding the exact constraints on cursor declarations will save you significant debugging time.

---

Top 3 Causes

1. Using FOR UPDATE with Non-Updatable Queries

PostgreSQL requires that a cursor declared with FOR UPDATE maps directly to rows in a single base table. Using GROUP BY, DISTINCT, UNION, or aggregate functions violates this rule.

-- BAD: Aggregate query with FOR UPDATE
DO $$
DECLARE
    cur CURSOR FOR
        SELECT department_id, COUNT(*) AS cnt
        FROM employees
        GROUP BY department_id
        FOR UPDATE;  -- ERROR: 42P11
BEGIN
    OPEN cur;
END;
$$;

-- GOOD: Remove FOR UPDATE from aggregate queries
DO $$
DECLARE
    cur CURSOR FOR
        SELECT department_id, COUNT(*) AS cnt
        FROM employees
        GROUP BY department_id;  -- Read-only, no FOR UPDATE
    rec RECORD;
BEGIN
    FOR rec IN cur LOOP
        RAISE NOTICE 'Dept: %, Count: %', rec.department_id, rec.cnt;
    END LOOP;
END;
$$;

2. Incorrect Cursor Declaration Syntax in PL/pgSQL

Misordering keywords or using wrong syntax in the DECLARE block will immediately trigger 42P11. The correct pattern is strictly cursor_name [SCROLL] CURSOR FOR query.

-- BAD: Wrong keyword order
DO $$
DECLARE
    my_cur FOR SELECT id, name FROM users;  -- ERROR: 42P11
BEGIN
    OPEN my_cur;
END;
$$;

-- GOOD: Correct PL/pgSQL cursor declaration
DO $$
DECLARE
    my_cur CURSOR FOR SELECT id, name FROM users WHERE active = true;
    rec RECORD;
BEGIN
    FOR rec IN my_cur LOOP
        RAISE NOTICE 'User: %', rec.name;
    END LOOP;
END;
$$;

3. Combining SCROLL and FOR UPDATE

SCROLL and FOR UPDATE are mutually exclusive in PostgreSQL. A scrollable cursor allows backward navigation, which is fundamentally incompatible with row-level locking for updates.

-- BAD: SCROLL + FOR UPDATE together
DO $$
DECLARE
    cur SCROLL CURSOR FOR
        SELECT id, salary FROM employees
        FOR UPDATE;  -- ERROR: 42P11
BEGIN
    OPEN cur;
END;
$$;

-- GOOD: Choose one based on your need
-- Option A: Scrollable read-only cursor
DO $$
DECLARE
    cur SCROLL CURSOR FOR
        SELECT id, salary FROM employees ORDER BY id;
    rec RECORD;
BEGIN
    OPEN cur;
    FETCH LAST FROM cur INTO rec;
    RAISE NOTICE 'Last record: %', rec.id;
    CLOSE cur;
END;
$$;

-- Option B: Updatable non-scroll cursor
DO $$
DECLARE
    cur NO SCROLL CURSOR FOR
        SELECT id, salary FROM employees
        WHERE department_id = 10
        FOR UPDATE;
    rec RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        UPDATE employees SET salary = rec.salary * 1.1
        WHERE CURRENT OF cur;
    END LOOP;
    CLOSE cur;
END;
$$;

---

Quick Fix Checklist

Situation	Fix
Aggregate query + FOR UPDATE	Remove FOR UPDATE; use a separate UPDATE statement
Wrong syntax in DECLARE	Use name CURSOR FOR query strictly
SCROLL + FOR UPDATE	Pick one; they cannot coexist
Need to update via cursor	Ensure query targets a single table, no joins/aggregates

---

Prevention Tips

1. Test your query standalone before wrapping it in a cursor.
Run the SELECT statement directly, and if using FOR UPDATE, verify it executes without aggregates or set operations. Use EXPLAIN to confirm the query plan is straightforward.

-- Validate before adding to cursor
EXPLAIN SELECT id, salary FROM employees
WHERE department_id = 10
FOR UPDATE;

2. Always explicitly declare SCROLL / NO SCROLL and update intent.
Relying on defaults leads to subtle bugs. Make your cursor's purpose clear in code: read-only scrollable cursors use SCROLL, updatable cursors use NO SCROLL with FOR UPDATE. This enforces intent and prevents accidental misuse.

-- Recommended: explicit options
DECLARE
    report_cur SCROLL CURSOR FOR
        SELECT * FROM monthly_report ORDER BY report_date;

    update_cur NO SCROLL CURSOR FOR
        SELECT id, status FROM orders
        WHERE status = 'pending'
        FOR UPDATE OF status;

---

Related Errors

• 42601 (syntax_error): Basic SQL syntax errors inside cursor query definitions.
• 34000 (invalid_cursor_name): Referencing a cursor name that doesn't exist or wasn't opened.
• 55000 (object_not_in_prerequisite_state): Attempting to open an already-open cursor or fetch from a closed one.
• 24000 (invalid_transaction_state): Using a cursor outside its valid transaction scope without WITH HOLD.

---

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