[Django] #37000: cursor_iter relies on GC for server-side cursor cleanup, causing transaction abort after savepoint rollback

[Django]

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

[Django]

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

Reporter: Ratskó László | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* stage: Unreviewed => Accepted

Comment:

Accepting but I'd like to clarify a specific point.

In your report you mention that entering the `transaction.atomic` context
will create a ''savepoint'' but in practice that only happens if the
`atomic` block is nested which is not included in your example.

Can you also reproduce without using a nested transaction as the example
you provided catches an exception inside `atomic` which
[https://docs.djangoproject.com/en/6.0/topics/db/transactions/#django.db.transaction.atomic
is a documented anti-pattern]?

We should make named cursor closing more resilient but it'd be good to
know if this can be triggered with proper usage of `transaction.atomic`.
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:1>

[Django]

#37000: cursor_iter relies on GC for server-side cursor cleanup, causing
transaction abort after savepoint rollback
-------------------------------------+-------------------------------------
Reporter: Ratskó László | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ratskó László):

Thanks for accepting the ticket!

To clarify: yes, this is specifically a nested transaction.atomic() issue
(savepoints). We have ATOMIC_REQUESTS = True, so every request is already
in a transaction, and any transaction.atomic() in our code creates a
savepoint. Sorry for not mentioning that in the report.

Our pattern follows the documented approach — catching exceptions outside
the atomic block:


{{{
# ATOMIC_REQUESTS = True → request is already in a transaction

def export_view(request):
try:
with transaction.atomic(): # this creates a savepoint (nested)
for item in queryset.iterator(): # server-side cursor
process(item) # may raise
except SomeError:
save_error_status() # ← fails with InFailedSqlTransaction
}}}

This matches the pattern from the docs:



{{{
@transaction.atomic
def viewfunc(request):
create_parent()

try:
with transaction.atomic():
generate_relationships()
except IntegrityError:
handle_exception()

add_children()
}}}

We tested both scenarios:

Nested (savepoint) — FAILS:

{{{

with transaction.atomic(): # outer (ATOMIC_REQUESTS)
try:
with transaction.atomic(): # inner → savepoint
for item in queryset.iterator():
raise ValueError()
except ValueError:
Model.objects.create(...) # ← InFailedSqlTransaction
}}}

Non-nested (full rollback) — WORKS:


{{{

try:
with transaction.atomic(): # not nested → full transaction
for item in queryset.iterator():
raise ValueError()
except ValueError:
Model.objects.create(...) # ← OK, transaction is IDLE after
full rollback
}}}

Let me know if we can help somehow by providing more information. Thanks
again!
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:2>

[Django]

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

Reporter: Ratskó László | Owner: Huwaiza
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: iterator, server- | Triage Stage: Ready for
side-cursor, savepoint, psycopg3 | checkin

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Huwaiza):

* cc: Huwaiza (added)
* owner: (none) => Huwaiza
* stage: Accepted => Ready for checkin
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:3>

[Django]

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

Reporter: Ratskó László | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)
* owner: Huwaiza => (none)
* stage: Ready for checkin => Accepted
* status: assigned => new

Comment:

Huwaiza please refrain from assigning ticket to yourself mid conversation
with reports and changing ticket status -- RFC is a status meant that the
patch is ready to be checked in and there's no patch here.

Refer to [https://docs.djangoproject.com/en/6.0/internals/contributing
/triaging-tickets/#:~:text=Ready%20for%20checkin%E2%80%9D.%20You the
triaging ticket documentation].

-----

Thanks for confirming Ratskó, I'll see if I can write a regression test
that reproduces the issue as I've been unable to do so yet. #28062 is a
related ticket, savepoint rollback might have been a cause all along.
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:4>

[Django]

#37000: cursor_iter relies on GC for server-side cursor cleanup, causing
transaction abort after savepoint rollback
-------------------------------------+-------------------------------------
Reporter: Ratskó László | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

I was able to reproduce with the following test

{{{#!diff
diff --git a/tests/backends/postgresql/test_server_side_cursors.py
b/tests/backends/postgresql/test_server_side_cursors.py
index 9a6457cce6..900a2dc229 100644
--- a/tests/backends/postgresql/test_server_side_cursors.py
+++ b/tests/backends/postgresql/test_server_side_cursors.py
@@ -1,9 +1,10 @@
import operator
+import gc
import unittest
from collections import namedtuple
from contextlib import contextmanager

-from django.db import connection, models
+from django.db import connection, models, transaction
from django.db.utils import ProgrammingError
from django.test import TestCase
from django.test.utils import garbage_collect
@@ -154,3 +155,12 @@ class ServerSideCursorsPostgres(TestCase):
# most likely need to be adapted.
with self.assertRaises(ProgrammingError):
perform_query()
+
+ def test_transaction_cursor_closing(self):
+ with self.assertRaises(ValueError), transaction.atomic():
+ persons = Person.objects.iterator()
+ next(persons)
+ raise ValueError
+ del persons
+ gc.collect()
+ list(Person.objects.all())
}}}

Without the last query we still get presented with a resource warning

{{{#!python
Exception ignored while closing generator <generator object cursor_iter at
0x7ffb1b97fab0>:
Traceback (most recent call last):
File "/django/source/django/db/models/sql/compiler.py", line 2266, in
cursor_iter
cursor.close()
File "/usr/local/lib/python3.14/site-
packages/psycopg/_server_cursor.py", line 73, in close
self._conn.wait(self._close_gen())
File "/usr/local/lib/python3.14/site-packages/psycopg/connection.py",
line 484, in wait
return waiting.wait(gen, self.pgconn.socket, interval=interval)
File "psycopg_binary/_psycopg/waiting.pyx", line 241, in
psycopg_binary._psycopg.wait_c
File "/usr/local/lib/python3.14/site-
packages/psycopg/_server_cursor_base.py", line 163, in _close_gen
yield from self._conn._exec_command(query)
File "/usr/local/lib/python3.14/site-
packages/psycopg/_connection_base.py", line 483, in _exec_command
raise e.error_from_result(result, encoding=self.pgconn._encoding)
psycopg.errors.InvalidCursorName: cursor
"_django_curs_140716552528704_sync_1" does not exist
Exception ignored while calling deallocator <function
ServerCursorMixin.__del__ at 0x7ffb1c113060>:
Traceback (most recent call last):
File "/usr/local/lib/python3.14/site-
packages/psycopg/_server_cursor_base.py", line 55, in __del__
__warn(
ResourceWarning: <django.db.backends.postgresql.base.ServerSideCursor
object at 0x55b16299aac0> was deleted while still open. Please use 'with'
or '.close()' to close the cursor properly
}}}

The approach in [https://github.com/django/django/pull/20975/ the Claude
generated MR] has merit but it's too naive in the sense that it tries to
close all server side cursors on each savepoint rollbacks but these can be
nested and we likely want to keep weakrefs to cursors as to avoid memory
leaks.

An ideal solution would weak track cursors by savepoint ID and only do so
in nested transactions. I'll note that even if we do so failing to consume
iterators returned by `QuerySet.iterator` entirely will always incur a
`ResourceWarning` (transaction or not) so maybe we should also (or
instead) adjust
[https://docs.djangoproject.com/en/6.0/ref/models/querysets/#django.db.models.query.QuerySet.iterator
the documentation] to mention that the following pattern should be used
instead?

{{{#!python
import contextlib

def export_items():
try:
with (
transaction.atomic(),
contextlib.closing(Item.objects.iterator()) as items
):
for item in items:

if item.value == "bad":
raise ValueError("Export failed")
process(item)
except ValueError:

Item.objects.create(value="error logged")
}}}

to enforce explicit closing of iterators? Maybe we should even have the
object returned by `QuerySet.iterator` be a context closing of itself so
we can document

{{{#!python
def export_items():
try:
with (
transaction.atomic(),
Item.objects.iterator() as items,
):
for item in items:

if item.value == "bad":
raise ValueError("Export failed")
process(item)
except ValueError:

Item.objects.create(value="error logged")
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:5>

[Django]

#37000: cursor_iter relies on GC for server-side cursor cleanup, causing
transaction abort after savepoint rollback
-------------------------------------+-------------------------------------
Reporter: Ratskó László | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Vidhi Singh):

Can I work on this?
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:6>

[Django]

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

Reporter: Ratskó László | Owner: Vidhi
| Singh
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Vidhi Singh):

* owner: (none) => Vidhi Singh

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:7>

[Django]

#37000: cursor_iter relies on GC for server-side cursor cleanup, causing
transaction abort after savepoint rollback
-------------------------------------+-------------------------------------
Reporter: Ratskó László | Owner: Vidhi
| Singh
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: iterator, server- | Triage Stage: Accepted
side-cursor, savepoint, psycopg3 |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Vidhi Singh):

I’ve worked on this issue and opened a PR to make server-side cursor
closing more
resilient when iterator() is interrupted inside transaction.atomic().

The fix ensures that delayed cursor.close() calls do not raise
InvalidCursorName
and break the transaction.

PR: https://github.com/django/django/pull/XXXX
--
Ticket URL: <https://code.djangoproject.com/ticket/37000#comment:8>