#36822: Implement parameter limit for Postgres backend with server-side binding
enabled
-------------------------------------+-------------------------------------
Reporter: Craig Weber | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_batch_size, | Triage Stage: Accepted
max_query_params |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):
* keywords: => bulk_batch_size, max_query_params
* stage: Unreviewed => Accepted
* summary:
Deleting objects with >65535 related rows fails on psycopg3 w/ server
side binding enabled
=>
Implement parameter limit for Postgres backend with server-side
binding enabled
Old description:
> 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.
New description:
[NOTE:] Much of the context of the following report, as well as "Part 3"
of the mitigation is a duplicate of #36248, but this ticket was accepted
for "Part 1" and "Part 2" below, see triage decision.
Original report follows:
----
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.
--
Comment:
Thanks for the report. The related object aspect (Part 3) is a duplicate
of #36248, but I take your point that `max_query_params` shouldn't be
unbounded on Postgres if you're using server-side cursors.
I'll re-scope your ticket to just Parts 1 & 2 of your suggested fix.
--
Ticket URL: <https://code.djangoproject.com/ticket/36822#comment:1>
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/0107019b4812411f-a261be88-fca1-48a3-806b-04df62a34a5a-000000%40eu-central-1.amazonses.com.
