default db selected while deleting object even when different db is mentioned in the query.

[Samyak Gaur]

we have 2 tables, table (Person)1 and table 2(Customer). There is a one-to-one relation between them. We are having problem deleting the parent object in a multi-db setup because whenever we run the parent.delete query django uses default db to get the child object (Note:default db is not the same as db being used).

We created a db connection as follows:

db = {
    'ENGINE': 'django.contrib.gis.db.backends.postgis',
    'NAME': 'db_name',
    'USER': 'db_user',
    'PASSWORD': 'db_password',
    'HOST': 'db_host',
    'PORT': '5432'
}
connections.databases['new-stage-db'] = db

Tried deleting the object using the command:

Parent.objects.using('new-stage-db').get(id=1).delete()

and

p = Parent.objects.using('new-stage-db').get(id=1)
p.delete(using='new-stage-db')

Surprisingly, django selects default db to get child object instead of getting it from the db mentioned above, but all the other child objects of this child objects are fetched using the new-stage-db.

Attaching logs for better understanding:

In [1]: from django.db import connections

In [2]:
   ...: db = {
   ...:     'ENGINE': 'django.contrib.gis.db.backends.postgis',
   ...:     'NAME': db_name',
   ...:     'USER': db_user',
   ...:     'PASSWORD':’db_password’,
   ...:     'HOST': 'db_host',
   ...:     'PORT': '5432'
   ...: }
   ...: connections.databases['new-stage-db'] = db

In [4]: p = Person.objects.using('new-stage-db').first()
SELECT "users_person"."id",
       "users_person"."user_id",
       "users_person"."first_name",
       "users_person"."last_name",
       "users_person"."dob",
       "users_person"."gender",
       "users_person"."profile_picture_url",
       "users_person"."searchvectors",
       "users_person"."created",
       "users_person"."modified"
  FROM "users_person"
 ORDER BY "users_person"."id" ASC
 LIMIT 1

Execution time: 0.001734s [Database: new-stage-db]

In [5]: p.customer
SELECT "users_customer"."id",
       "users_customer"."person_id",
       "users_customer"."email",
       "users_customer"."customers_referral_code",
       "users_customer"."referred_code",
       "users_customer"."eligible_for_referral_bonus",
       "users_customer"."company",
       "users_customer"."created",
       "users_customer"."modified",
       "users_customer"."last_review_requested",
       "users_customer"."status",
       "users_customer"."activity",
       "users_customer"."send_promotional_sms",
       "users_customer"."send_promotional_email",
       "users_customer"."send_applozic_chat_pn",
       "users_customer"."send_vibration_alarm_pn",
       "users_customer"."credits_balance",
       "users_customer"."ghost_credits_balance",
       "users_customer"."otp",
       "users_customer"."otp_refresh_time",
       "users_customer"."city_id",
       "users_customer"."registration_location_id",
       "users_customer"."home_location_id",
       "users_customer"."office_location_id",
       "users_customer"."preferred_profile",
       "users_customer"."office_start_time",
       "users_customer"."office_end_time",
       "users_customer"."seats_preference"

Execution time: 0.001938s [Database: new-stage-db]
Out[5]: <Customer: Customer name>

In [6]: p.delete()
SELECT "users_customer"."id"
  FROM "users_customer"
 WHERE "users_customer"."person_id" IN (20)

Execution time: 0.000846s [Database: new-stage-db]
SELECT "users_customer"."id",
       "users_customer"."email"
  FROM "users_customer"
 WHERE "users_customer"."id" = 20
 LIMIT 21

**Execution time: 0.002264s [Database: default]**
--------------------------------------------------------------------------
DoesNotExist                             Traceback (most recent call last)
<ipython-input-6-6a6f0dca8e5f> in <module>
----> 1 p.delete()

/usr/local/lib/python3.9/site-packages/django/db/models/base.py in delete(self, using, keep_parents)
    964
    965         collector = Collector(using=using)
--> 966         collector.collect([self], keep_parents=keep_parents)
    967         return collector.delete()
    968

/usr/local/lib/python3.9/site-packages/django/db/models/deletion.py in collect(self, objs, source, nullable, collect_related, source_attr, reverse_dependency, keep_parents, fail_on_restricted)
    293                     ))
    294                     sub_objs = sub_objs.only(*tuple(referenced_fields))
--> 295                 if sub_objs:
    296                     try:
    297                         field.remote_field.on_delete(self, field, sub_objs, self.using)

/usr/local/lib/python3.9/site-packages/django/db/models/query.py in __bool__(self)
    282
    283     def __bool__(self):
--> 284         self._fetch_all()
    285         return bool(self._result_cache)
    286

/usr/local/lib/python3.9/site-packages/django/db/models/query.py in _fetch_all(self)
   1322     def _fetch_all(self):
   1323         if self._result_cache is None:
-> 1324             self._result_cache = list(self._iterable_class(self))
   1325         if self._prefetch_related_lookups and not self._prefetch_done:
   1326             self._prefetch_related_objects()

/usr/local/lib/python3.9/site-packages/django/db/models/query.py in __iter__(self)
     67         ]
     68         for row in compiler.results_iter(results):
---> 69             obj = model_cls.from_db(db, init_list, row[model_fields_start:model_fields_end])
     70             for rel_populator in related_populators:
     71                 rel_populator.populate(row, obj)

/usr/local/lib/python3.9/site-packages/django/db/models/base.py in from_db(cls, db, field_names, values)
    513                 for f in cls._meta.concrete_fields
    514             ]
--> 515         new = cls(*values)
    516         new._state.adding = False
    517         new._state.db = db

/cityflo-final-backend/backend/users/models.py in __init__(self, *args, **kwargs)
    250     def __init__(self, *args, **kwargs):
    251         super().__init__(*args, **kwargs)
--> 252         self.__original_email = self.email
    253
    254     def __str__(self):

/usr/local/lib/python3.9/site-packages/django/db/models/query_utils.py in __get__(self, instance, cls)
    142             val = self._check_parent_chain(instance)
    143             if val is None:
--> 144                 instance.refresh_from_db(fields=[field_name])
    145             else:
    146                 data[field_name] = val

/usr/local/lib/python3.9/site-packages/django/db/models/base.py in refresh_from_db(self, using, fields)
    648             db_instance_qs = db_instance_qs.only(*fields)
    649
--> 650         db_instance = db_instance_qs.get()
    651         non_loaded_fields = db_instance.get_deferred_fields()
    652         for field in self._meta.concrete_fields:

/usr/local/lib/python3.9/site-packages/django/db/models/query.py in get(self, *args, **kwargs)
    433             return clone._result_cache[0]
    434         if not num:
--> 435             raise self.model.DoesNotExist(
    436                 "%s matching query does not exist." %
    437                 self.model._meta.object_name

DoesNotExist: Customer matching query does not exist.

We used the same db credentials for default db and new-stage-db then the object was successfully deleted but still child object was fetched from the default db.

In [7]: p.delete()
SELECT "users_customer"."id"
  FROM "users_customer"
 WHERE "users_customer"."person_id" IN (20)

Execution time: 0.001433s [Database: new-stage-db]
SELECT "users_customer"."id",
       "users_customer"."email"
  FROM "users_customer"
 WHERE "users_customer"."id" = 20
 LIMIT 21

**Execution time: 0.001729s [Database: default]
**SELECT "routes_searchedplacespair"."id"
  FROM "routes_searchedplacespair"
 WHERE "routes_searchedplacespair"."customer_id" IN (20)

Execution time: 0.000963s [Database: new-stage-db]
SELECT "rides_customerride"."id"
  FROM "rides_customerride"
 WHERE "rides_customerride"."customer_id" IN (20)

Execution time: 0.001208s [Database: new-stage-db]
SELECT "rides_freecustomerridecoupon"."id"
  FROM "rides_freecustomerridecoupon"
 WHERE "rides_freecustomerridecoupon"."customer_id" IN (20)

….

Trimmed unnecessary table queries…
….
DELETE
  FROM "campaigns_campaignforcustomer"
 WHERE "campaigns_campaignforcustomer"."id" IN (2492638, 2279230)

Execution time: 0.002839s [Database: new-stage-db]
DELETE
  FROM "users_customer"
 WHERE "users_customer"."id" IN (20)

Execution time: 0.000842s [Database: new-stage-db]
DELETE
  FROM "users_person"
 WHERE "users_person"."id" IN (20)

Execution time: 0.000578s [Database: new-stage-db]
Out[7]:
(29,
 {'payments.CustomerWalletBalanceLog': 22,
  'push_notifs.SavedNotificationForCustomer': 1,
  'push_notifs.PushNotificationAnalytics': 1,
  'rides.FreeCustomerRideCoupon': 1,
  'campaigns.CampaignForCustomer': 2,
  'users.Customer': 1,
  'users.Person': 1})