DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42P03 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P03: duplicate cursor

PostgreSQL error code 42P03 occurs when you attempt to open a cursor using a name that already exists and is currently open within the same transaction or session. Since PostgreSQL tracks cursor names as unique identifiers within a session, declaring or opening a cursor with a duplicate name immediately raises this error. This is most commonly seen in PL/pgSQL functions, stored procedures, or explicit transaction blocks that manage cursors manually.

Top 3 Causes

1. Opening a Cursor Without Closing It First

The most frequent cause: a cursor is opened, used, but never explicitly closed. On the next invocation—especially inside a loop or repeated function call—PostgreSQL finds the cursor still open and raises 42P03.

-- ❌ Problematic code
DO $$
DECLARE
    my_cursor CURSOR FOR SELECT id FROM employees;
BEGIN
    OPEN my_cursor;
    -- forgot to CLOSE my_cursor here
    OPEN my_cursor; -- ERROR: 42P03 duplicate cursor "my_cursor"
END;
$$;

-- ✅ Fixed version
DO $$
DECLARE
    my_cursor CURSOR FOR SELECT id FROM employees;
    rec RECORD;
BEGIN
    OPEN my_cursor;
    LOOP
        FETCH my_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'ID: %', rec.id;
    END LOOP;
    CLOSE my_cursor; -- Always close explicitly

    -- Safe to open again
    OPEN my_cursor;
    CLOSE my_cursor;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

2. Missing Cursor Cleanup in Exception Handlers

When a PL/pgSQL function raises an exception and the EXCEPTION block doesn't close the cursor, the cursor remains open. If the same session calls the function again (common in connection pooling environments), the leftover cursor triggers 42P03.

-- ✅ Safe exception handling with cursor cleanup
CREATE OR REPLACE FUNCTION safe_cursor_function()
RETURNS void AS $$
DECLARE
    emp_cursor CURSOR FOR SELECT id, name FROM employees;
    rec RECORD;
BEGIN
    OPEN emp_cursor;

    LOOP
        FETCH emp_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Processing: %', rec.name;
    END LOOP;

    CLOSE emp_cursor;

EXCEPTION
    WHEN OTHERS THEN
        -- Clean up cursor even on failure
        BEGIN
            CLOSE emp_cursor;
        EXCEPTION
            WHEN invalid_cursor_name THEN
                NULL; -- Already closed, ignore
        END;
        RAISE;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

3. Hardcoded Cursor Names in Nested or Recursive Calls

Using a fixed cursor name like 'my_cursor' in a function that can be called recursively or nested leads to a collision when the inner call tries to open the same cursor name that the outer call already has open.

-- ✅ Use dynamic cursor names to avoid collisions
CREATE OR REPLACE FUNCTION process_table(p_table TEXT)
RETURNS void AS $$
DECLARE
    -- Generate a unique cursor name per call
    v_cursor_name TEXT := 'cur_' || pg_backend_pid()
                          || '_' || extract(epoch from clock_timestamp())::bigint;
    ref_cur REFCURSOR;
    rec RECORD;
BEGIN
    OPEN ref_cur FOR EXECUTE 'SELECT id, name FROM ' || quote_ident(p_table);

    LOOP
        FETCH ref_cur INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '%', rec;
    END LOOP;

    CLOSE ref_cur;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Check currently open cursors using the pg_cursors system view: 
-- See all open cursors in the current session
SELECT name, statement, creation_time
FROM pg_cursors;
Enter fullscreen mode Exit fullscreen mode
  1. Close all open cursors in one command: 
CLOSE ALL;
Enter fullscreen mode Exit fullscreen mode
  1. Prefer implicit cursors — they close automatically and never cause 42P03: 
-- ✅ Implicit cursor: no manual OPEN/CLOSE needed
DO $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id, name FROM employees LOOP
        RAISE NOTICE 'ID: %, Name: %', rec.id, rec.name;
    END LOOP;
    -- Cursor is automatically closed after the loop
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Prefer implicit FOR ... IN SELECT loops over explicit cursors whenever possible. They are automatically closed at the end of the loop, eliminating the risk of 42P03 entirely.
  • Always add cursor cleanup to EXCEPTION blocks, and in connection-pooled environments, run CLOSE ALL or DISCARD ALL when returning connections to the pool to ensure no stale cursor state is carried over to the next client.

Related Errors

Code Name Description
34000 invalid_cursor_name Opposite of 42P03 — cursor doesn't exist when accessed
25P02 in_failed_sql_transaction Often appears alongside cursor errors in aborted transactions

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

Top comments (0)