cursor_iter relies on GC for server-side cursor cleanup, causing transaction abort after savepoint rollback

[Ratskó László]

Description

When QuerySet.iterator() is used inside transaction.atomic() and an exception interrupts the iteration, the server-side cursor opened by cursor_iter is not closed eagerly. The generator stays alive (referenced from the exception's traceback), and the cursor is only closed when GC collects the generator — by which time the savepoint has already been rolled back, destroying the cursor on the PostgreSQL side.

The delayed cursor.close() sends a CLOSE command for a non-existent cursor, which raises InvalidCursorName and aborts the entire transaction. Any subsequent DB operations fail with InFailedSqlTransaction.

This was discussed with the psycopg maintainer in ​https://github.com/psycopg/psycopg/discussions/1282, who suggested that we open a discussion about this here is well.

Reproduction

from django.db import transaction
from myapp.models import Item

def export_items():
    try:
        with transaction.atomic():  # creates a savepoint
            for item in Item.objects.iterator():  # opens server-side cursor
                if item.value == "bad":
                    raise ValueError("Export failed")
                process(item)
    except ValueError:
        # Savepoint was rolled back, cursor destroyed on PostgreSQL side.
        # But cursor_iter generator is still alive in the traceback.
        # GC will eventually close() the cursor → CLOSE fails → transaction aborted.
        Item.objects.create(value="error logged")  # ← fails with InFailedSqlTransaction

What happens step by step

1. Item.objects.iterator() opens a server-side cursor via cursor_iter
2. Exception is raised mid-iteration
3. transaction.atomic().__exit__ rolls back the savepoint → PostgreSQL destroys the cursor
4. The cursor_iter generator is not closed — still referenced from the exception traceback
5. GC collects the generator → finally: cursor.close() runs
6. psycopg sends CLOSE "cursor_name" → InvalidCursorName
7. The failed SQL aborts the transaction
8. Subsequent queries fail with InFailedSqlTransaction

The problem in cursor_iter

# django/db/models/sql/compiler.py
def cursor_iter(cursor, sentinel, col_count, itersize):
    try:
        for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
            yield rows if col_count is None else [r[:col_count] for r in rows]
    finally:
        cursor.close()  # only runs when generator is closed or GC'd

The finally block is correct for normal completion, but when an exception interrupts the generator mid-yield, the close is deferred to GC. By that time, a savepoint rollback may have already destroyed the cursor.

Related

• psycopg discussion: ​https://github.com/psycopg/psycopg/discussions/1282