[Django] #36060: IntegrityError: null value in column "_order" when bulk_create()

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: bulk_create, | Triage Stage:
order_with_respect_to | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I have model with {{{order_with_respect_to}}} Meta's option targeted to
ForeignKey. If I try to run {{{bulk_create()}}} I catch next error:
{{{django.db.utils.IntegrityError: null value in column "_order" of
relation "appname_modelname" violates not-null constraint}}}
To fix this, I directly set the {{{_order}}} value for each instance in
the list to {{{bulk_create()}}}. This is not convenient and adds
additional code.

{{{
File "/Volumes/PROJECT/app/views.py", line 100, in form_valid
product.album.bulk_create(images)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/models/manager.py", line 87, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/models/query.py", line 818, in bulk_create
returned_columns = self._batched_insert(
^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/models/query.py", line 1875, in _batched_insert
self._insert(
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/models/query.py", line 1847, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/models/sql/compiler.py", line 1836, in execute_sql
cursor.execute(sql, params)
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/backends/utils.py", line 122, in execute
return super().execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/backends/utils.py", line 79, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/backends/utils.py", line 100, in _execute
with self.db.wrap_database_errors:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/Users/user/venv/test/lib/python3.12/site-
packages/django/db/backends/utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/user/venv/test/lib/python3.12/site-
packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)

django.db.utils.IntegrityError: null value in column "_order" of relation
"appname_modelname" violates not-null constraint
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36060>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------

Reporter: Nikolay Fedorov | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

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:

This is effectively not something `bulk_create` currently implements.

It's missing
[https://github.com/django/django/blob/51df0dff3c4f28016185a9e876ee5b3420712f99/django/db/models/base.py#L1147-L1163
the equivalent logic] that `Model.save` has which could be implemented by
doing a single query retrieving the `MAX` for each order with respect to
values. Something like


{{{#!python
if order_wrt := self.model.order_with_respect_to:
get_filter_kwargs_for_object = order_wrt.get_filter_kwargs_for_object
attnames = get_filter_kwargs_for_object(obj[0])
values = {
tuple(get_filter_kwargs_for_object(obj).values())
for obj in objs
}
filters = reduce(operator.or_, (
Q(dict(zip(attnames, vals)))
for vals in values
))
max_orders = (
self.model._base_manager.using(using)
.values(*attnames)
.filter(filters).
.annotate(
_order__max=Max("_order", default=0)
)
)
max_orders_map = {max_order[:len(attnames]: max_order[-1] for max_order
in max_orders]
# and then assign max_orders_map to each objs
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:1>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by myoungjinGo):

Hi, Can I work on this issue and try implementing the suggested logic?
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:2>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:

| myoungjinGo
Type: Bug | Status: assigned

Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by myoungjinGo):

* owner: (none) => myoungjinGo
* status: new => assigned

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

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Nikolay Fedorov):

Replying to [comment:2 myoungjinGo]:

> Hi, Can I work on this issue and try implementing the suggested logic?

If you asking me I dont' mind )
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:4>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 0

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

Changes (by myoungjinGo):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:5>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo

Type: New feature | Status: assigned

Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 0 => 1
* needs_docs: 0 => 1
* type: Bug => New feature

Comment:

Left some comments for improvements and re-purposed as a new feature given
it's likely something we'll want to document in `bulk_create` and in the
release notes? Feel free to move back to Bug if you believe it's a better
classification and we shouldn't document the change.
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:6>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo

Type: Bug | Status: assigned

Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* type: New feature => Bug

Comment:

Flipping back and forth here; thinking more about it this is more of a bug
but we might have to let users know we fixed it as some might have worked
around by setting `_order` themselves?
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:7>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* needs_better_patch: 1 => 0
* needs_docs: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:8>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Nikolay Fedorov):

Replying to [comment:7 Simon Charette]:

> Flipping back and forth here; thinking more about it this is more of a
bug but we might have to let users know we fixed it as some might have
worked around by setting `_order` themselves?

I think it is necessary to notify users in order to optimize their code
and take these changes into account in the future.
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:9>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 1

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

Changes (by Jacob Walls):

* needs_docs: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:10>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_docs: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:11>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Hikari):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:12>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:13>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: bulk_create, | Triage Stage: Ready for
order_with_respect_to | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:14>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: bulk_create, | Triage Stage: Accepted
order_with_respect_to |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1

* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:15>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: bulk_create, | Triage Stage: Ready for
order_with_respect_to | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* needs_better_patch: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:16>

[Django]

#36060: IntegrityError: null value in column "_order" when bulk_create()
-------------------------------------+-------------------------------------
Reporter: Nikolay Fedorov | Owner:
| myoungjinGo

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: bulk_create, | Triage Stage: Ready for
order_with_respect_to | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"953095d1e603fe0f8f01175b1409ca23818dcff9" 953095d1]:
{{{#!CommitTicketReference repository=""
revision="953095d1e603fe0f8f01175b1409ca23818dcff9"
Fixed #36060 -- Prevented IntegrityError in bulk_create() with
order_with_respect_to.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36060#comment:17>