#37000: cursor_iter relies on GC for server-side cursor cleanup, causing
transaction abort after savepoint rollback
-------------------------------------+-------------------------------------
Reporter: Ratskó László | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: iterator, server- | Triage Stage:
side-cursor, savepoint, psycopg3 | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
== 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
{{{#!python
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
{{{#!python
# 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
--
Ticket URL: <https://code.djangoproject.com/ticket/37000>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019d19a93444-9aa1a298-5b29-4463-a390-61b59cf69efb-000000%40eu-central-1.amazonses.com.
