Opened 6 weeks ago

Last modified 3 weeks ago

#36060 assigned Bug

IntegrityError: null value in column "_order" when bulk_create()

Reported by: Nikolay Fedorov Owned by: myoungjinGo
Component: Database layer (models, ORM) Version: 5.1
Severity: Normal Keywords: bulk_create, order_with_respect_to
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no
Pull Requests:19012

Description

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

According to the ticket's flags, the next step(s) to move this issue forward are:

  • For anyone except the patch author to review the patch using the patch review checklist and either mark the ticket as "Ready for checkin" if everything looks good, or leave comments for improvement and mark the ticket as "Patch needs improvement".

Change History (9)

comment:1 by Simon Charette, 6 weeks ago

Triage Stage: UnreviewedAccepted

This is effectively not something bulk_create currently implements.

It's missing 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

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

comment:2 by myoungjinGo, 6 weeks ago

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

comment:3 by myoungjinGo, 6 weeks ago

Owner: set to myoungjinGo
Status: newassigned

in reply to:  2 comment:4 by Nikolay Fedorov, 6 weeks ago

Replying to myoungjinGo:

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

If you asking me I dont' mind )

comment:5 by myoungjinGo, 5 weeks ago

Has patch: set

comment:6 by Simon Charette, 4 weeks ago

Needs documentation: set
Patch needs improvement: set
Type: BugNew feature

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.

comment:7 by Simon Charette, 4 weeks ago

Type: New featureBug

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?

comment:8 by Simon Charette, 3 weeks ago

Needs documentation: unset
Patch needs improvement: unset

in reply to:  7 comment:9 by Nikolay Fedorov, 3 weeks ago

Replying to 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.

Note: See TracTickets for help on using tickets.
Back to Top