PostgreSQL Duplicate Cursor (SQLSTATE 42P03)

PostgreSQL raises ERROR: cursor "<name>" already exists with SQLSTATE 42P03 (duplicate_cursor) when a DECLARE statement attempts to open a cursor using a name that is already held by an open cursor in the current transaction. The cursor must be closed or the name must be changed before it can be reused.

What This Error Means

SQLSTATE 42P03 belongs to class 42 — "Syntax Error or Access Rule Violation" — though in this case the violation is a naming collision at runtime rather than a parse-time syntax problem. The error is raised during statement execution, not during query planning.

In PostgreSQL, named cursors are scoped to a transaction. Once you execute DECLARE my_cursor CURSOR FOR ..., the name my_cursor is reserved for the lifetime of that transaction (or until you issue CLOSE my_cursor). If any subsequent DECLARE in the same transaction attempts to use the same name, PostgreSQL immediately raises 42P03 before executing the cursor's query.

Withhold cursors (declared with WITH HOLD) behave slightly differently: they survive COMMIT and remain open after the transaction ends. A WITH HOLD cursor name therefore persists beyond the originating transaction, and a new DECLARE using that name will still collide until the cursor is explicitly closed.

Common Causes

1. Reusing a cursor name in a loop. A PL/pgSQL function declares a cursor inside a loop body without closing it at the end of each iteration, so the second iteration tries to declare an already-open cursor.

2. Missing CLOSE after error handling. A cursor is opened, an exception is caught, and control returns to a code path that tries to declare the same cursor again — but the CLOSE was never reached because the exception bypassed it.

3. WITH HOLD cursor left open across transactions. Application code declares a WITH HOLD cursor, commits, and then (in a later transaction or reconnect logic) tries to re-declare the same name without first closing the still-open holdable cursor.

4. Connection pooling with session reuse. A pooled connection that previously declared a WITH HOLD cursor and never closed it is returned to the pool and reused. The next caller declares a cursor with the same name and hits the collision.

5. Concurrent calls to the same PL/pgSQL function. Each call runs in its own transaction, so collisions across sessions are not possible for ordinary cursors. However, if a function uses a hard-coded cursor name and is called multiple times within a single transaction (e.g., via recursive CTEs or nested calls), the second invocation will collide.

How to Fix duplicate_cursor

1. Close the cursor before re-declaring it.

   CLOSE my_cursor;
   DECLARE my_cursor CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
   

2. Use cursor variables in PL/pgSQL instead of named cursors. Cursor variables in PL/pgSQL are automatically managed and do not require explicit DECLARE … CURSOR FOR at the SQL level:

   DO $$
   DECLARE
     cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
     rec RECORD;
   BEGIN
     OPEN cur;
     LOOP
       FETCH cur INTO rec;
       EXIT WHEN NOT FOUND;
       -- process rec
     END LOOP;
     CLOSE cur;
   END;
   $$;
   

3. Use dynamic cursor names to avoid collisions in reentrant code.

   DO $$
   DECLARE
     cur_name TEXT := 'cur_' || txid_current() || '_' || clock_timestamp()::bigint;
     cur REFCURSOR;
   BEGIN
     OPEN cur FOR EXECUTE 'SELECT id FROM orders WHERE status = ''pending''';
     -- use cur ...
     CLOSE cur;
   END;
   $$;
   

4. Always close cursors in exception handlers to ensure cleanup even on failure:

   BEGIN
     DECLARE my_cursor CURSOR FOR SELECT id FROM orders;
     -- ... fetch rows ...
     CLOSE my_cursor;
   EXCEPTION
     WHEN OTHERS THEN
       -- Attempt to close if open; ignore error if already closed
       BEGIN
         CLOSE my_cursor;
       EXCEPTION WHEN invalid_cursor_name THEN NULL;
       END;
       RAISE;
   END;
   

5. Audit WITH HOLD cursors using pg_cursors:

   SELECT name, statement, is_holdable, creation_time
   FROM pg_cursors
   WHERE is_holdable = true;
   

   Close any stale holdable cursors before re-declaring:

   CLOSE my_holdable_cursor;
   

Additional Information

• SQLSTATE class 42 covers a wide range of naming and access-rule violations. Related sibling codes include 42P04 (duplicate_database), 42P05 (duplicate_prepared_statement), and 42723 (duplicate_function).
• The system view pg_cursors (available since PostgreSQL 8.2) lists all currently open named cursors in the session, including holdable ones. It is useful for diagnosing this error during development.
• Most PostgreSQL client drivers (libpq, psycopg2, asyncpg, JDBC) surface 42P03 as a DatabaseError / ProgrammingError with the SQLSTATE included in the exception. Inspect e.pgcode (Python) or getSQLState() (Java) to identify it programmatically.
• ORMs that generate cursor names automatically (e.g., Django's server-side cursor support via connection.cursor(name=...)) can trigger this error if the same name is passed twice within a transaction without closing the first cursor.
• This error does not leave the transaction in an aborted state on its own — the transaction remains valid and you can continue issuing commands after handling the error, unlike errors in class 40 (transaction rollback).

Frequently Asked Questions

Can two different sessions collide on the same cursor name? No. Named cursors (excluding WITH HOLD) are strictly scoped to their originating transaction and are invisible to other sessions. Only WITH HOLD cursors survive COMMIT, but they are still session-local and cannot conflict across different database connections.

Why does PostgreSQL group this under "Syntax Error or Access Rule Violation" (class 42)? Class 42 is a catch-all for errors that violate naming rules and schema-object constraints, not just SQL syntax. duplicate_cursor fits because the DECLARE statement violates the rule that cursor names must be unique within a transaction.

How do I list all open cursors in my session? Query the pg_cursors view:

SELECT name, statement, is_holdable, creation_time FROM pg_cursors;

This returns all named cursors currently open in the session, including those declared in PL/pgSQL blocks.

Does closing a cursor inside a PL/pgSQL exception block require special handling? Yes. If the cursor was never opened (e.g., the error occurred before OPEN), attempting to CLOSE it will raise invalid_cursor_name (SQLSTATE 34000). Wrap the CLOSE in a nested BEGIN … EXCEPTION WHEN invalid_cursor_name THEN NULL; END; block to handle this gracefully.