#27193: ORDER BY clause not included in subqueries using select_for_update()
-------------------------------+--------------------
Reporter: sqwishy | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
This was tested using Django 1.9.5. I believe the code below fails to
generate the correct SQL. I explicitly set an ordering in the subquery but
it is not expressed in the generated SQL. This alters the behaviour of the
SELECT statement when the FOR UPDATE clause is used. My use case is to
lock rows in a particular order with the goal of preventing deadlocks.
Example code:
{{{
with transaction.atomic():
print(User.objects.filter(id__in=User.objects.order_by('id').select_for_update()))
}}}
PostgreSQL logs:
{{{
2016-09-08 02:59:43 ACWST [30398-20] testsystem@testsystem LOG:
statement: BEGIN
2016-09-08 02:59:43 ACWST [30398-21] testsystem@testsystem LOG:
statement: SELECT "testapp_user"."id", "testapp_user"."password", ... FROM
"testapp_user" WHERE "testapp_user"."id" IN (SELECT "testapp_user"."id"
FROM "testapp_user" FOR UPDATE) LIMIT 21
2016-09-08 02:59:43 ACWST [30398-22] testsystem@testsystem LOG:
statement: COMMIT
}}}
It looks like the order_by is being cleared on the queryset object here
https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L476
I can verify this as it looks like ORDER BY does show up when I include
distinct() in the subquery - which isn't a suitable workaround as DISTINCT
is not compatible with FOR UPDATE - and by slicing the subquery as to set
an offset or a limit.
For now, an acceptable workaround seems to make the SELECT FOR UPDATE in a
separate query instead of a subquery. That at least seems to accomplish
the goal I am trying to achieve.
--
Ticket URL: <https://code.djangoproject.com/ticket/27193>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.465b3c86632cc871483eebd1a9d398c8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
