#36822: Deleting objects with >65535 related rows fails on psycopg3 w/ server
side
binding enabled
-------------------------------------+-------------------------------------
Reporter: Craig Weber | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When deleting a model instance that has more than 65535 related objects
via CASCADE, Django exceeds PostgreSQL's query parameter limit. This
causes an {{{OperationalError}}} during the collection phase when Django
checks for further related objects.
The issue occurs because:
1. PostgreSQL backend doesn't define {{{max_query_params}}}, so
{{{bulk_batch_size()}}} returns unbounded batch sizes
2. When collecting related objects for deletion,
{{{Collector.get_del_batches()}}} returns all objects in a single batch
3. The subsequent {{{related_objects()}}} query generates an IN clause
with all PKs, exceeding the 65535 parameter limit
== Django Version ==
Tested on Django 5.2 with psycopg 3.x
== Database ==
PostgreSQL with psycopg3, which has a hard limit of 65535 query parameters
when using server-side parameter binding.
'''Note''': Django's default with psycopg3 is client-side binding (which
avoids this limit by interpolating parameters into the SQL string). This
bug manifests when {{{server_side_binding=True}}} is configured in
database OPTIONS. Server-side binding is recommended for performance and
is required for some features.
Reference: https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html
#server-side-binding
== Steps to Reproduce ==
=== Minimal Example Models ===
{{{#!python
# models.py
from django.db import models
class StockRecord(models.Model):
sku = models.CharField(max_length=100)
class Line(models.Model):
stockrecord = models.ForeignKey(
StockRecord,
on_delete=models.CASCADE,
related_name='lines'
)
class LineAttribute(models.Model):
"""
Any model with a non-DO_NOTHING FK to Line prevents fast-delete.
This forces Django to check for related LineAttributes when deleting
Lines,
which generates the query that exceeds the parameter limit.
"""
line = models.ForeignKey(
Line,
on_delete=models.CASCADE,
related_name='attributes'
)
name = models.CharField(max_length=100)
value = models.CharField(max_length=100)
}}}
=== Database Configuration ===
{{{#!python
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'OPTIONS': {
'server_side_binding': True, # Required to trigger this bug
},
# ... other settings
}
}
}}}
=== Test Case ===
{{{#!python
from django.test import TestCase
from .models import StockRecord, Line
class CascadeDeleteParameterLimitTest(TestCase):
@classmethod
def setUpTestData(cls):
"""Create one StockRecord with 66000 related Lines."""
cls.stockrecord = StockRecord.objects.create(sku="TEST-SKU")
# Create 66000 lines to exceed the 65535 parameter limit
num_lines = 66000
batch_size = 5000
for batch_start in range(0, num_lines, batch_size):
batch_end = min(batch_start + batch_size, num_lines)
Line.objects.bulk_create([
Line(stockrecord=cls.stockrecord)
for _ in range(batch_end - batch_start)
])
def test_cascade_delete_with_many_related_objects(self):
"""
Deleting a StockRecord with 66000+ Lines fails with
OperationalError.
The CASCADE delete collects all 66000 Lines. Since Line has a
reverse
FK from LineAttribute (with CASCADE), Django cannot fast-delete
and
must check for related LineAttributes. This generates:
SELECT * FROM lineattribute WHERE line_id IN ($1, $2, ..., $66000)
This exceeds PostgreSQL's 65535 parameter limit.
"""
self.assertEqual(Line.objects.count(), 66000)
# This raises OperationalError: number of parameters must be
between 0 and 65535
self.stockrecord.delete()
# If successful, all lines should be deleted via CASCADE
self.assertEqual(Line.objects.count(), 0)
}}}
== Exception Traceback ==
{{{
psycopg.OperationalError: sending query and params failed: number of
parameters must be between 0 and 65535
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "test_models.py", line 28, in
test_cascade_delete_with_many_related_objects
self.stockrecord.delete()
File "django/db/models/base.py", line 1280, in delete
collector.collect([self], keep_parents=keep_parents)
File "django/db/models/deletion.py", line 345, in collect
on_delete(self, field, sub_objs, self.using)
File "django/db/models/deletion.py", line 23, in CASCADE
collector.collect(sub_objs, ...)
File "django/db/models/deletion.py", line 343, in collect
if getattr(on_delete, "lazy_sub_objs", False) or sub_objs:
File "django/db/models/query.py", line 400, in __bool__
self._fetch_all()
...
django.db.utils.OperationalError: sending query and params failed: number
of parameters must be between 0 and 65535
}}}
== Root Cause Analysis ==
=== Issue 1: PostgreSQL doesn't define max_query_params ===
In {{{django/db/backends/base/features.py}}}:
{{{#!python
max_query_params = None # No limit by default
}}}
Oracle and SQLite override this:
* Oracle: {{{max_query_params = 2**16 - 1}}} (65535)
* SQLite: {{{max_query_params = 999}}}
PostgreSQL doesn't override it, so it remains {{{None}}}.
=== Issue 2: bulk_batch_size() returns unbounded size for PostgreSQL ===
In {{{django/db/backends/base/operations.py}}}:
{{{#!python
def bulk_batch_size(self, fields, objs):
return len(objs) # Returns ALL objects - no batching
}}}
Oracle overrides this to respect {{{max_query_params}}}:
{{{#!python
# django/db/backends/oracle/operations.py
def bulk_batch_size(self, fields, objs):
"""Oracle restricts the number of parameters in a query."""
if fields:
return self.connection.features.max_query_params // len(fields)
return len(objs)
}}}
PostgreSQL doesn't override it, so {{{Collector.get_del_batches()}}}
returns all objects in a single batch.
=== Issue 3: related_objects() query exceeds parameter limit ===
When {{{Collector.collect()}}} processes CASCADE-related objects, it must
check for further related objects. The flow is:
1. {{{StockRecord.delete()}}} triggers
{{{collector.collect([stockrecord])}}}
2. For the {{{Line.stockrecord}}} FK with CASCADE, Django calls
{{{CASCADE(collector, field, sub_objs, using)}}}
3. CASCADE calls {{{collector.collect(sub_objs)}}} where {{{sub_objs}}} is
the Lines QuerySet
4. Inside this nested {{{collect()}}}, Line cannot be "fast-deleted"
because LineAttribute has a CASCADE FK pointing to Line
5. Django fetches all 66000 Lines into memory
6. For each reverse FK on Line, Django calls:
{{{#!python
batches = self.get_del_batches(new_objs, [field]) # Returns [[all 66000
lines]]
for batch in batches:
sub_objs = self.related_objects(related_model, [field], batch)
}}}
7. {{{related_objects()}}} generates a query with all PKs:
{{{#!sql
SELECT * FROM lineattribute WHERE line_id IN ($1, $2, ..., $66000)
}}}
8. This query has 66000 parameters, exceeding the 65535 limit
=== Issue 4: Collector.delete() combines batched QuerySets with OR ===
Even after Parts 1+2 fix the SELECT queries during collection, the UPDATE
phase for SET_NULL/SET_DEFAULT relations combines all batched QuerySets
back together:
{{{#!python
# django/db/models/deletion.py, lines 483-485
if updates:
combined_updates = reduce(or_, updates) # Combines ALL batches!
combined_updates.update(**{field.name: value})
}}}
When a related model has {{{on_delete=SET_NULL}}}, Django collects the
QuerySets in batches but then recombines them with OR before executing
{{{.update()}}}. This negates the batching and can still exceed the
parameter limit.
For example, if Line has a reverse relation from {{{LogEntry.line}}} with
{{{SET_NULL}}}:
1. Deleting StockRecord cascades to 66000 Lines
2. Collection batches Lines properly (Part 1+2 fix)
3. Django needs to SET_NULL on LogEntry records pointing to those Lines
4. {{{reduce(or_, updates)}}} combines all batches into one QuerySet
5. The UPDATE query exceeds 65535 parameters
== Suggested Fix ==
=== Part 1: Add max_query_params to PostgreSQL features ===
{{{#!python
# django/db/backends/postgresql/features.py
class DatabaseFeatures(BaseDatabaseFeatures):
max_query_params = 2**16 - 1 # PostgreSQL protocol limit
}}}
=== Part 2: Add bulk_batch_size() to PostgreSQL operations ===
{{{#!python
# django/db/backends/postgresql/operations.py
from itertools import chain
from django.db.models import CompositePrimaryKey
def bulk_batch_size(self, fields, objs):
"""PostgreSQL has a 65535 parameter limit with server-side binding."""
if self.connection.features.max_query_params is None:
return len(objs)
fields = list(
chain.from_iterable(
field.fields if isinstance(field, CompositePrimaryKey) else
[field]
for field in fields
)
)
if fields:
return self.connection.features.max_query_params // len(fields)
return len(objs)
}}}
=== Part 3: Execute field updates per batch instead of combining with OR
===
{{{#!python
# django/db/models/deletion.py, in Collector.delete()
# Instead of:
if updates:
combined_updates = reduce(or_, updates)
combined_updates.update(**{field.name: value})
# Do:
for qs in updates:
qs.update(**{field.name: value})
}}}
This executes multiple UPDATE queries (one per batch) instead of combining
them into a single query that exceeds the parameter limit.
== Additional Notes ==
1. This issue manifests with psycopg3's server-side binding
({{{server_side_binding=True}}}). With psycopg2 or client-side binding
(Django's default for psycopg3), parameters are interpolated into the SQL
string, avoiding the limit.
2. The issue occurs when deleting an object that has many CASCADE-related
objects, and those objects have further related objects that Django must
check (any reverse FK with {{{on_delete != DO_NOTHING}}}). Common
scenarios include:
* E-commerce: Deleting a product/SKU with 100k+ order line items
* Audit systems: Deleting entities with extensive audit trail records
* Any model with self-referential FKs or related models that track
metadata
3. Oracle already handles this correctly because it defines both
{{{max_query_params}}} and overrides {{{bulk_batch_size()}}}. The same
pattern should be applied to PostgreSQL.
4. '''Parts 1+2 can be implemented in a custom database backend''', but
'''Part 3 requires a Django core change''' to
{{{django/db/models/deletion.py}}}. Without Part 3, the fix will still
fail when SET_NULL or SET_DEFAULT relations exist on the cascaded objects.
--
Ticket URL: <https://code.djangoproject.com/ticket/36822>
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/0107019b47dd8743-b4d6d188-5e39-4cf3-8807-30c661f7e2da-000000%40eu-central-1.amazonses.com.
