PostgreSQL 34000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 34000: Invalid Cursor Name

PostgreSQL error code 34000 (invalid_cursor_name) occurs when your SQL statement references a cursor that doesn't exist, has already been closed, or was never declared in the current session. This typically happens when you attempt FETCH, MOVE, or CLOSE on a cursor name that the server cannot find. It is one of the more common runtime errors in applications that process large datasets using server-side cursors.

---

Top 3 Causes and Fixes

Cause 1: Using a Cursor Before Declaring It

The most straightforward cause is attempting to FETCH from a cursor that was never opened with DECLARE.

-- Wrong: FETCH before DECLARE
BEGIN;
FETCH ALL FROM my_cursor;
-- ERROR: 34000 - cursor "my_cursor" does not exist

-- Correct: Always DECLARE first
BEGIN;
DECLARE my_cursor CURSOR FOR
    SELECT id, name FROM customers WHERE active = true ORDER BY id;

FETCH 50 FROM my_cursor;
CLOSE my_cursor;
COMMIT;

-- Check currently open cursors
SELECT name, statement, is_holdable, creation_time
FROM pg_cursors;

---

Cause 2: Referencing a Cursor After Transaction Ends

By default, PostgreSQL cursors are WITHOUT HOLD, meaning they are destroyed when the transaction commits or rolls back. Trying to use them afterward raises error 34000.

-- Problem: cursor destroyed after COMMIT
BEGIN;
DECLARE short_cursor CURSOR FOR
    SELECT * FROM orders WHERE status = 'pending';
COMMIT;

FETCH ALL FROM short_cursor;
-- ERROR: 34000 - cursor "short_cursor" does not exist

-- Solution: Use WITH HOLD to persist cursor across transactions
BEGIN;
DECLARE persistent_cursor CURSOR WITH HOLD FOR
    SELECT order_id, total FROM orders WHERE status = 'pending';
COMMIT;

-- Still accessible after COMMIT
FETCH 100 FROM persistent_cursor;
CLOSE persistent_cursor; -- Must be closed explicitly

---

Cause 3: Dynamic or Misnamed Cursors in PL/pgSQL

In PL/pgSQL functions, typos in cursor variable names or improper loop handling can trigger this error. Always use typed cursor variables and include proper exception handling.

-- Safe PL/pgSQL cursor pattern with error handling
CREATE OR REPLACE FUNCTION batch_process_orders()
RETURNS void AS $$
DECLARE
    v_cursor CURSOR FOR
        SELECT id, amount FROM orders WHERE processed = false;
    v_row orders%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_row;
        EXIT WHEN NOT FOUND;

        UPDATE orders
        SET processed = true
        WHERE id = v_row.id;
    END LOOP;
    CLOSE v_cursor;

EXCEPTION
    WHEN invalid_cursor_name THEN
        RAISE WARNING 'Cursor not found: %', SQLERRM;
    WHEN OTHERS THEN
        IF v_cursor%ISOPEN THEN
            CLOSE v_cursor;
        END IF;
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Using REFCURSOR for dynamic cursor names
CREATE OR REPLACE FUNCTION open_dynamic_cursor(p_status TEXT)
RETURNS refcursor AS $$
DECLARE
    v_ref REFCURSOR := 'cursor_' || p_status;
BEGIN
    OPEN v_ref FOR
        SELECT * FROM orders WHERE status = p_status;
    RETURN v_ref;
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Checklist

• Always DECLARE a cursor before FETCH or CLOSE
• Use WITH HOLD if the cursor needs to outlive its transaction
• Always call CLOSE explicitly, especially in connection pool environments
• Catch SQLSTATE '34000' in application error handlers
• Query pg_cursors to debug which cursors are currently open

---

Prevention Tips

1. Enforce cursor lifecycle discipline.
Structure your code so that OPEN, FETCH, and CLOSE always appear as a matched set within the same logical block. In PL/pgSQL, always add an EXCEPTION handler that closes any open cursor before re-raising the error.

2. Be careful with connection pooling.
When using PgBouncer or application-level pools, cursors from a previous session may not exist in a recycled connection. Avoid relying on WITH HOLD cursors across connection boundaries, and always verify cursor state with pg_cursors during development.

---

Related Error Codes

Code	Name	Relation
24000	invalid_transaction_state	Often co-occurs with cursor misuse
25P02	in_failed_sql_transaction	Chained errors after a failed transaction
42P01	undefined_table	Cursor declaration fails due to missing table

---

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