PostgreSQL 34000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 34000: Invalid Cursor Name

PostgreSQL error code 34000 (invalid_cursor_name) occurs when you try to reference a cursor that doesn't exist, hasn't been declared in the current session, or has already been closed. Cursors are database objects used to process query result sets row by row, and they must be properly declared before use. This error is especially common in PL/pgSQL procedures, complex transaction logic, and application-level database drivers.

---

Top 3 Causes

1. Referencing an Undeclared Cursor

The most common cause is attempting to FETCH, MOVE, or CLOSE a cursor that was never declared, or using a misspelled cursor name.

-- Wrong: FETCH without DECLARE
BEGIN;
FETCH NEXT FROM my_cursor; -- ERROR: 34000 invalid cursor name
COMMIT;

-- Correct: Declare before use
BEGIN;

DECLARE my_cursor CURSOR FOR
    SELECT id, name, email
    FROM users
    WHERE active = true;

FETCH NEXT FROM my_cursor;
CLOSE my_cursor;
COMMIT;

2. Accessing a Cursor After Transaction Boundary

By default, PostgreSQL cursors are only valid within the transaction they were declared in. After a COMMIT or ROLLBACK, the cursor is automatically closed — unless it was declared WITH HOLD.

-- Wrong: Accessing cursor after COMMIT
BEGIN;
DECLARE tx_cursor CURSOR FOR SELECT * FROM orders;
COMMIT; -- cursor is now closed

FETCH NEXT FROM tx_cursor; -- ERROR: 34000

-- Correct: Use WITH HOLD for cross-transaction cursors
BEGIN;
DECLARE persistent_cursor CURSOR WITH HOLD FOR
    SELECT id, amount FROM orders WHERE status = 'pending';
COMMIT; -- cursor remains open

FETCH ALL FROM persistent_cursor;
CLOSE persistent_cursor; -- must close explicitly

3. Reusing a Closed Cursor

Calling FETCH or MOVE on a cursor that has already been explicitly closed with CLOSE will trigger error 34000. This often happens inside loops or exception handlers.

-- Wrong: Fetching from a closed cursor
BEGIN;
DECLARE loop_cursor CURSOR FOR SELECT id FROM products;
FETCH NEXT FROM loop_cursor;
CLOSE loop_cursor;
FETCH NEXT FROM loop_cursor; -- ERROR: 34000

-- Correct PL/pgSQL pattern with safe exception handling
CREATE OR REPLACE FUNCTION safe_cursor_demo()
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    rec_cursor CURSOR FOR SELECT id, name FROM products;
    row_data   RECORD;
BEGIN
    OPEN rec_cursor;
    LOOP
        FETCH rec_cursor INTO row_data;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Processing product: %', row_data.name;
    END LOOP;
    CLOSE rec_cursor;
EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            CLOSE rec_cursor;
        EXCEPTION
            WHEN invalid_cursor_name THEN NULL;
        END;
        RAISE;
END;
$$;

---

Quick Fix Solutions

• Check open cursors in your session using the system view:

-- List all currently open cursors in the session
SELECT name, statement, is_holdable, creation_time
FROM pg_cursors;

-- Check if a specific cursor exists before using it
SELECT EXISTS (
    SELECT 1 FROM pg_cursors WHERE name = 'my_cursor'
) AS cursor_exists;

• Always match DECLARE → OPEN → FETCH → CLOSE in sequence.
• Use WITH HOLD only when you genuinely need the cursor to persist beyond a transaction, and always close it explicitly afterward.

---

Prevention Tips

1. Standardize cursor lifecycle management. Enforce a team coding convention that every cursor declaration is paired with an explicit CLOSE, and wrap cursor logic in exception-safe blocks. Regularly audit pg_cursors during development and testing to catch cursor leaks early.

2. Validate cursor existence before access. When writing dynamic or complex PL/pgSQL code, query pg_cursors to verify a cursor is open before executing FETCH or MOVE. For WITH HOLD cursors used via application drivers (JDBC, psycopg2, etc.), confirm that your driver properly closes server-side cursors on connection release to prevent resource leaks and stale cursor references.

---

Related Errors

Code	Name	Brief Description
34001	cursor_already_exists	Declaring a cursor with a name that's already in use
25P01	no_active_sql_transaction	Declaring a non-holdable cursor outside a transaction
42P01	undefined_table	Table not found in cursor query, causing declaration failure

---

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