Opened 5 years ago

Closed 5 years ago

#30883 closed Bug (invalid)

select_for_update doesn't include submodels in PostgreSQL

Reported by: Abhijeet Viswa Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords: psql, select_for_update, transactions
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Consider the following line of code:

order = Order.objects.filter(uuid=order_uuid).prefetch_related(
            Prefetch(
                lookup='items',
                queryset=items
            )
        ).select_related('outlet').select_related('user').select_related('outlet__group') \
         .select_for_update(of=('self', 'outlet'))

Here, outlet is a relation defined as:

outlet = models.ForeignKey(Outlet, on_delete=models.PROTECT, related_name="orders")

Here the Order and Outlet models are sub-models to Transaction and Shop models respectively (which are situated in another Django app)
The former models reside in an app called cashless while the latter reside in payment_gateway.

I logged the PSQL output and this is the resultant query for the above line of code:

SELECT 
"payment_gateway_transaction"."uuid", 
"payment_gateway_transaction"."ref_number", 
"payment_gateway_transaction"."shop_id", 
"payment_gateway_transaction"."user_id", 
"payment_gateway_transaction"."timestamp", 
"payment_gateway_transaction"."amount", 
"payment_gateway_transaction"."payment_method", 
"payment_gateway_transaction"."transaction_status", 
"payment_gateway_transaction"."service", 
"cashless_order"."transaction_ptr_id", "cashless_order"."token", 
"cashless_order"."outlet_id", "cashless_order"."order_status", 
"cashless_order"."order_start_time", "cashless_order"."order_end_time", 
"cashless_order"."order_collect_time", "cashless_order"."order_review", 
"cashless_order"."order_ratings", "authentication_user"."id", 
"authentication_user"."password", "authentication_user"."last_login", 
"authentication_user"."is_superuser", "authentication_user"."email", 
"authentication_user"."student_id", "authentication_user"."first_name", 
"authentication_user"."last_name", "authentication_user"."date_joined", 
"authentication_user"."ip_address", "authentication_user"."mac_address", 
"authentication_user"."location", "authentication_user"."phone_number", 
"authentication_user"."total_spent", "authentication_user"."is_banned", 
"authentication_user"."needs_student_id", 
"authentication_user"."generated_at", "authentication_user"."verify_token", 
"authentication_user"."is_active", "payment_gateway_shop"."uuid", 
"payment_gateway_shop"."name", "payment_gateway_shop"."location_id", 
"payment_gateway_shop"."location_coords", 
"payment_gateway_shop"."tax_applicable", 
"payment_gateway_shop"."percentage_tax", 
"payment_gateway_shop"."gst_number", "payment_gateway_shop"."open", 
"payment_gateway_shop"."active", "payment_gateway_shop"."group_id", 
"cashless_outlet"."shop_ptr_id", "cashless_outlet"."start_time", 
"cashless_outlet"."end_time", "cashless_outlet"."break_start_time", 
"cashless_outlet"."break_end_time", "cashless_outlet"."items_limit", 
"cashless_outlet"."image", "cashless_outlet"."description", 
"cashless_outlet"."on_cashless", "cashless_outlet"."ratings", 
"cashless_outlet"."latest_token", "auth_group"."id", "auth_group"."name" 
FROM "cashless_order" 
INNER JOIN "payment_gateway_transaction" ON  ("cashless_order"."transaction_ptr_id" =  "payment_gateway_transaction"."uuid") 
INNER JOIN "authentication_user" ON ("payment_gateway_transaction"."user_id" = "authentication_user"."id") 
INNER JOIN "cashless_outlet" ON ("cashless_order"."outlet_id" = "cashless_outlet"."shop_ptr_id") 
INNER JOIN "payment_gateway_shop" ON ("cashless_outlet"."shop_ptr_id" = "payment_gateway_shop"."uuid") 
INNER JOIN "auth_group" ON ("payment_gateway_shop"."group_id" = "auth_group"."id") 
WHERE "cashless_order"."transaction_ptr_id" = '6e413b2e8f07-4452-8cd2-5bc186e7ffcb'::uuid 
FOR UPDATE OF "payment_gateway_transaction", "payment_gateway_shop"

As seen from the SQL query, only the rows of tables payment_gateway_transaction and payment_gateway_model (Transaction and Shop models respectively) are being locked for update. This is despite the fact that the original Queryset references Order (a submodel of Transaction).

Since the of parameter provides no way of specifying the Models/tables that we would want to lock, there is no way around this problem.

Change History (1)

comment:1 by Abhijeet Viswa, 5 years ago

Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top