PostgreSQL 42P03 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P03: duplicate_cursor

The 42P03 duplicate_cursor error occurs in PostgreSQL when you attempt to declare a cursor with a name that already exists and is still open within the current transaction. Since cursor names must be unique within a transaction scope, re-declaring an already-open cursor without closing it first will immediately trigger this error. This issue most commonly surfaces in PL/pgSQL functions, stored procedures, or application code that manages complex transactional logic with large data sets.

Top 3 Causes

1. Re-declaring a Cursor Without Closing It First

The most common cause is simply forgetting to CLOSE a cursor before re-using the same name within the same transaction block.

-- ❌ Bad: triggers 42P03
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT id FROM orders WHERE status = 'NEW';
FETCH ALL FROM my_cursor;

-- Oops — cursor still open, same name used again
DECLARE my_cursor CURSOR FOR SELECT id FROM orders WHERE status = 'DONE';

COMMIT;

-- ✅ Good: always close before re-declaring
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT id FROM orders WHERE status = 'NEW';
FETCH ALL FROM my_cursor;
CLOSE my_cursor;  -- close first!

DECLARE my_cursor CURSOR FOR SELECT id FROM orders WHERE status = 'DONE';
FETCH ALL FROM my_cursor;
CLOSE my_cursor;

COMMIT;

2. PL/pgSQL Functions Called Repeatedly Within the Same Transaction

If a PL/pgSQL function declares a hardcoded cursor name and the function is called more than once inside the same transaction — or exits abnormally without closing the cursor — the second call will collide with the still-open cursor from the first call.

-- ❌ Problematic function with a fixed cursor name
CREATE OR REPLACE FUNCTION load_data(p_status TEXT) RETURNS VOID AS $$
DECLARE
    c CURSOR FOR SELECT id FROM orders WHERE status = p_status;
BEGIN
    OPEN c;
    -- if an error occurs here, cursor is never closed
    FETCH ALL FROM c;
    CLOSE c;
END;
$$ LANGUAGE plpgsql;

-- ✅ Safe version using REFCURSOR and EXCEPTION block
CREATE OR REPLACE FUNCTION load_data_safe(p_status TEXT) RETURNS VOID AS $$
DECLARE
    c REFCURSOR;
    v_row orders%ROWTYPE;
BEGIN
    OPEN c FOR SELECT * FROM orders WHERE status = p_status;
    LOOP
        FETCH c INTO v_row;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Processing order: %', v_row.id;
    END LOOP;
    CLOSE c;
EXCEPTION
    WHEN OTHERS THEN
        IF c IS NOT NULL THEN
            CLOSE c;
        END IF;
        RAISE;
END;
$$ LANGUAGE plpgsql;

3. Connection Pooling Without Proper Transaction Cleanup

When using connection pools (PgBouncer, HikariCP, etc.), a connection may be returned to the pool with an open cursor still active if the application fails to properly commit, rollback, or close cursors before releasing the connection. The next time that connection is borrowed and the same cursor name is declared, the error fires immediately.

-- ✅ Always verify open cursors in your session
SELECT name, statement, creation_time
FROM pg_cursors;

-- ✅ Defensive cursor cleanup pattern inside a DO block
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_cursors WHERE name = 'my_cursor') THEN
        CLOSE my_cursor;
    END IF;
END;
$$;

Quick Fix Solutions

Always pair OPEN with CLOSE: treat cursor lifecycle like file handles — open, use, close, every time.
Use REFCURSOR instead of named cursors in functions to avoid name collision across multiple calls.
Wrap cursor logic in EXCEPTION blocks to guarantee cleanup even on error paths.
Monitor pg_cursors regularly to spot leaked or long-running open cursors before they cause problems.

Prevention Tips

Adopt REFCURSOR as the default in PL/pgSQL functions. Unlike named cursors, REFCURSOR variables are scoped to the local function block and avoid global name conflicts entirely. Combine this with EXCEPTION WHEN OTHERS cleanup blocks to make cursor handling bulletproof.
Integrate pg_cursors monitoring into your DBA runbook. Set up a periodic query or alerting rule that flags any cursor open longer than a defined threshold (e.g., 5 minutes). Leaked cursors are often a symptom of deeper transaction management issues in the application layer, and catching them early prevents cascading errors including 42P03.

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

DEV Community