PostgreSQL Held Cursor Requires Same Isolation Level (SQLSTATE 25008)

PostgreSQL raises ERROR: held cursor requires the same isolation level (SQLSTATE 25008, condition name held_cursor_requires_same_isolation_level) when a WITH HOLD cursor — one declared to persist beyond its creating transaction — is subsequently fetched inside a transaction that uses a different isolation level than the transaction in which the cursor was opened.

What This Error Means

SQLSTATE class 25 covers invalid transaction state errors. The 25008 code is specifically about the lifecycle contract of WITH HOLD cursors: such cursors survive a COMMIT, but their result set was materialized under the snapshot and isolation semantics of the originating transaction. PostgreSQL enforces that any subsequent transaction reading from the cursor must operate at the same isolation level, because a stricter or more relaxed level could produce results that are inconsistent with what the cursor already saw.

A WITH HOLD cursor materializes its remaining rows at COMMIT time (PostgreSQL copies the result set into a temporary structure). Even so, the isolation level is recorded and checked on subsequent FETCH calls. If the fetching transaction's isolation level does not match, PostgreSQL aborts the fetch with this error rather than silently returning potentially incoherent data.

This error belongs to the transaction state error class and does not leave the current transaction in an aborted state by itself — the FETCH statement fails, but the surrounding transaction may continue or be rolled back depending on how the application handles the exception.

Common Causes

1. Opening a WITH HOLD cursor in READ COMMITTED and fetching from it inside a SERIALIZABLE (or REPEATABLE READ) transaction. A common pattern is to open a cursor in a utility session at default isolation, then later execute a batch job that wraps its work in SERIALIZABLE and iterates the same cursor.

2. Changing the session default isolation level between sessions or transactions. If default_transaction_isolation is altered at the session level (e.g., SET SESSION default_transaction_isolation = 'serializable') after a WITH HOLD cursor was created, subsequent transactions automatically start at the new level, which may no longer match the cursor's recorded level.

3. Connection pooling that resets session parameters. A pooler that issues SET default_transaction_isolation as part of its session-reset sequence can silently change the isolation level between the transaction that opened the cursor and the transaction that fetches from it.

4. Explicit BEGIN ISOLATION LEVEL on the fetching transaction. Code that explicitly starts a new transaction with a different isolation level (e.g., BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ) while a WITH HOLD cursor opened at READ COMMITTED is still open will hit this error on the first FETCH.

How to Fix held_cursor_requires_same_isolation_level

1. Match the isolation level of the fetching transaction to the cursor's originating transaction. The most direct fix is to ensure both the DECLARE ... WITH HOLD and the subsequent FETCH statements run under the same isolation level:

   -- Session A: open cursor at SERIALIZABLE
   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   DECLARE my_cursor CURSOR WITH HOLD FOR SELECT id, payload FROM events ORDER BY id;
   COMMIT;
   
   -- Session A: fetch must also use SERIALIZABLE
   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   FETCH 100 FROM my_cursor;
   COMMIT;
   

2. Avoid mixing WITH HOLD cursors with non-default isolation levels. If the cursor is used only for reading large result sets in chunks, consider keeping the entire operation in a single transaction without WITH HOLD, or use READ COMMITTED throughout:

   BEGIN; -- defaults to READ COMMITTED
   DECLARE batch_cursor CURSOR FOR SELECT * FROM large_table;
   FETCH 500 FROM batch_cursor;
   -- ... process rows ...
   FETCH 500 FROM batch_cursor;
   COMMIT; -- cursor closes automatically
   

3. Close and re-declare the cursor if the isolation level must change. If you legitimately need different isolation levels in different parts of your workflow, close the WITH HOLD cursor before starting a transaction at a different level, then re-declare it:

   CLOSE my_cursor;
   
   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   DECLARE my_cursor CURSOR WITH HOLD FOR SELECT id, payload FROM events ORDER BY id;
   COMMIT;
   

4. Audit connection pool reset scripts. If the error appears intermittently in a pooled environment, check whether the pool's reset or cleanup query modifies default_transaction_isolation. Remove or align those settings so all transactions in the pool start at a consistent level.

5. Inspect the current cursor's isolation level by querying pg_cursors:

   SELECT name, is_holdable, creation_time
   FROM pg_cursors
   WHERE name = 'my_cursor';
   

   pg_cursors does not expose the originating isolation level directly, but knowing the cursor is holdable (is_holdable = true) confirms you are dealing with a WITH HOLD cursor subject to this constraint.

Additional Information

• SQLSTATE 25008 is defined in the SQL standard under the invalid transaction state class. PostgreSQL introduced WITH HOLD cursor support in version 7.4; the isolation level check has been part of the implementation since that version.
• Related SQLSTATE codes in class 25: 25001 (active_sql_transaction), 25002 (branch_transaction_already_active), 25006 (read_only_sql_transaction), 25P01 (no_active_sql_transaction), 25P02 (in_failed_sql_transaction).
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a server-side error and will raise their standard database exception type. The SQLSTATE 25008 is included in the exception and can be caught explicitly.
• WITH HOLD cursors hold a reference to a server-side result set and consume memory on the PostgreSQL server until they are explicitly closed or the session ends. Long-lived holdable cursors in high-concurrency environments can contribute to memory pressure; prefer closing them promptly.

Frequently Asked Questions

What is a WITH HOLD cursor and why does PostgreSQL track its isolation level? A WITH HOLD cursor survives a COMMIT — its result set is materialized on the server so it can be fetched across multiple transactions. Because the rows were read under a specific isolation snapshot, PostgreSQL records that isolation level and enforces it on subsequent fetches to maintain data consistency guarantees.

Does this error abort the current transaction? The failing FETCH statement raises an error, which in PostgreSQL causes the current transaction to enter an aborted state if the error is unhandled. You must ROLLBACK (or let the driver roll back) before issuing further commands. If the error is caught in a PL/pgSQL EXCEPTION block, the subtransaction is rolled back but the outer transaction may continue.

Can I check the isolation level of an existing WITH HOLD cursor before fetching? Not directly — pg_cursors does not expose the originating isolation level. The safest approach is to track the isolation level in application code when declaring the cursor, or to use a consistent default isolation level (READ COMMITTED) throughout the session.

Does this error occur with regular (non-holdable) cursors? No. Regular cursors are scoped to a single transaction and are automatically closed at COMMIT or ROLLBACK. The isolation level mismatch check only applies to WITH HOLD cursors, which can span transaction boundaries.