[Django] #36144: DatabaseOperations.bulk_batch_size() should consider more database limits on SQLite and Oracle

Django

unread,

Jan 27, 2025, 4:05:14 AMJan 27

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------
Reporter: Sarah Boyce | Type:
| Cleanup/optimization
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | 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
-------------------------------------+-------------------------------------
`DatabaseOperations.bulk_batch_size()` is used to calculate the maximum
batch size when doing operations such as bulk_update and bulk_create

When investigating the impact of composite primary keys on the maximum
batch size calculation for `bulk_update()`, it became clear that there are
more database limits that need to be considered when calculating the
maximum batch size in order to have a bullet proof solution.

One possible limit in play on SQLite is `SQLITE_MAX_EXPR_DEPTH` which is
1000 (see https://www.sqlite.org/limits.html#max_expr_depth).
On Oracle, we found that a query could error with the ambiguous message:
`ORA-00907: missing right parenthesis`, which may be due to hitting some
limit (possibly documented here:
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-
program-limits.html)

We may need to revisit the API design.

PR discussion:
https://github.com/django/django/pull/19088#discussion_r1929940327
--
Ticket URL: <https://code.djangoproject.com/ticket/36144>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 27, 2025, 4:06:21 AMJan 27

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------

Description changed by Sarah Boyce:

Old description:

> `DatabaseOperations.bulk_batch_size()` is used to calculate the maximum
> batch size when doing operations such as bulk_update and bulk_create
>
> When investigating the impact of composite primary keys on the maximum
> batch size calculation for `bulk_update()`, it became clear that there
> are more database limits that need to be considered when calculating the
> maximum batch size in order to have a bullet proof solution.
>
> One possible limit in play on SQLite is `SQLITE_MAX_EXPR_DEPTH` which is
> 1000 (see https://www.sqlite.org/limits.html#max_expr_depth).
> On Oracle, we found that a query could error with the ambiguous message:
> `ORA-00907: missing right parenthesis`, which may be due to hitting some
> limit (possibly documented here:
> https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls
> /plsql-program-limits.html)
>
> We may need to revisit the API design.
>
> PR discussion:
> https://github.com/django/django/pull/19088#discussion_r1929940327

New description:

`DatabaseOperations.bulk_batch_size()` is used to calculate the maximum
batch size when doing operations such as bulk_update and bulk_create

When investigating the impact of composite primary keys on the maximum
batch size calculation for `bulk_update()`, it became clear that there are
more database limits that need to be considered when calculating the
maximum batch size in order to have a bullet proof solution.

One possible limit in play on SQLite is `SQLITE_MAX_EXPR_DEPTH` which is
1000 (see https://www.sqlite.org/limits.html#max_expr_depth).
On Oracle, we found that a query could error with the ambiguous message:
`ORA-00907: missing right parenthesis`, which may be due to hitting some
limit (possibly documented here:
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-
program-limits.html)

We may need to revisit the API design.

PR discussion:
https://github.com/django/django/pull/19088#discussion_r1929940327

Ticket which sparked the discussion/discovery: #36118

--
--
Ticket URL: <https://code.djangoproject.com/ticket/36144#comment:1>

Django

unread,

Jan 28, 2025, 1:15:31 PMJan 28

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------
Reporter: Sarah Boyce | Owner: (none)
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: 5.2

(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted

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

Changes (by Natalia Bidart):

* stage: Unreviewed => Accepted
* version: 5.1 => 5.2

Comment:

Thank you Sarah, I read the conversation and it makes sense.
--
Ticket URL: <https://code.djangoproject.com/ticket/36144#comment:2>

Django

unread,

Mar 17, 2025, 2:36:27 AMMar 17

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------

Reporter: Sarah Boyce | Owner: Ahmed
Type: | Nassar
Cleanup/optimization | Status: assigned

Changes (by Ahmed Nassar):

* owner: (none) => Ahmed Nassar
* status: new => assigned

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

Django

unread,

Nov 29, 2025, 6:08:14 AMNov 29

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------

Reporter: Sarah Boyce | Owner: (none)
Type: | Status: new
Cleanup/optimization |

Comment (by Pravin):

Hi all,
I tried to produce the failure. I have created relatively large complex
model setup.

{{{
class CompositePKModel(models.Model):
part1 = models.IntegerField()
part2 = models.IntegerField()
value1 = models.IntegerField(default=0)
value2 = models.IntegerField(default=0)
value3 = models.IntegerField(default=0)
flag = models.BooleanField(default=False)

class Meta:

constraints = [
models.UniqueConstraint(fields=["part1", "part2"],
name="pk_cmp")
]

class RelatedModel(models.Model):
parent = models.ForeignKey(CompositePKModel, on_delete=models.CASCADE)
score1 = models.IntegerField(default=0)
score2 = models.IntegerField(default=0)
active = models.BooleanField(default=False)

class AnotherRelatedModel(models.Model):
parent = models.ForeignKey(RelatedModel, on_delete=models.CASCADE)
rating = models.IntegerField(default=0)
status = models.BooleanField(default=True)
}}}

{{{
class BulkUpdateComplexTest(TestCase):
databases = {"default"}

@classmethod
def setUpTestData(cls):
cls.parents = [
CompositePKModel(part1=i, part2=i + 1)
for i in range(800)
]
CompositePKModel.objects.bulk_create(cls.parents)

cls.related_objs = []
for parent in cls.parents:
for j in range(3):
cls.related_objs.append(
RelatedModel(parent=parent)
)
RelatedModel.objects.bulk_create(cls.related_objs)

cls.another_related_objs = []
for rel in cls.related_objs:
for k in range(2):
cls.another_related_objs.append(
AnotherRelatedModel(parent=rel)
)
AnotherRelatedModel.objects.bulk_create(cls.another_related_objs)

def test_bulk_update_multiple_models(self):
for obj in self.parents:
obj.value1 = 111
obj.value2 = 222
obj.value3 = 333
obj.flag = True
for obj in self.related_objs:
obj.score1 = 10
obj.score2 = 20
obj.active = True
for obj in self.another_related_objs:
obj.rating = 5
obj.status = False

try:
with transaction.atomic():
CompositePKModel.objects.bulk_update(
self.parents, ['value1', 'value2', 'value3', 'flag']
)
RelatedModel.objects.bulk_update(
self.related_objs, ['score1', 'score2', 'active']
)
AnotherRelatedModel.objects.bulk_update(
self.another_related_objs, ['rating', 'status']
)
except OperationalError as e:
self.fail(f"bulk_update failed due to SQLite expression depth:
{e}")

self.assertTrue(all(o.value1 == 111 for o in
CompositePKModel.objects.all()))
self.assertTrue(all(o.score1 == 10 for o in
RelatedModel.objects.all()))
self.assertTrue(all(o.rating == 5 for o in
AnotherRelatedModel.objects.all()))
}}}

am i missing something ?

Django

unread,

Dec 28, 2025, 1:12:37 PM (yesterday) Dec 28

to django-...@googlegroups.com

#36144: DatabaseOperations.bulk_batch_size() should consider more database limits
on SQLite and Oracle
-------------------------------------+-------------------------------------

Reporter: Sarah Boyce | Owner: nzioker
Type: | Status: assigned

Changes (by nzioker):

* owner: (none) => nzioker

* status: new => assigned

Comment:

I'm taking this ticket to work on it.

Plan:
1. First reproduce the SQLITE_MAX_EXPR_DEPTH issue with a proper test
2. Investigate Oracle's undocumented limit causing ORA-00907
3. Update DatabaseOperations.bulk_batch_size() to consider these limits
4. Submit a patch with tests
--
Ticket URL: <https://code.djangoproject.com/ticket/36144#comment:4>

Reply all

Reply to author

Forward