django - swap foreign keys of one-to-one relationship in an atomic transaction-CodePudding

i have a Unit model with a one-to-one related handler defined as such:

handler = models.OneToOneField('users.Handler', related_name="unit_obj", blank=True, null=True, on_delete=models.SET_NULL)

i'm trying to update a set of units in an atomic transaction where the unit's handler_id foreign key can be set to null or any other handler_id. the problem is when handlers are being swapped.

i initially tried saving the updated unit inside an atomic transaction. but as soon as unit.save() is called on the first unit i get an integrity error because the second unit still has the same handler_id. i was hoping that the atomic transaction would defer the integrity error on individual unit.save() calls and only throw the error at the end of the transaction if there actually were any.

try:
    with transaction.atomic():
        for unit_model in unit_models:
            unit = Unit.objects.get(id=unit_model['id'])
            unit.handler_id = unit_model['handler']['id'] if unit_model['handler'] is not None else None
            unit.save()
except IntegrityError as e:
    print(f'Error saving units: {str(e)}')

then i tried using bulk_update which i was sure would solve the issue. unfortunately i get the same error (duplicate key value violates unique constraint) even though i have confirmed that there are no duplicates in the list. it still seems to be applying the saves sequentially, though that is not what i would have expected.

try:
    with transaction.atomic():
        bulk_units = []
        for unit_model in unit_models:
            unit = Unit.objects.get(id=unit_model['id'])
            unit.handler_id = unit_model['handler']['id'] if unit_model['handler'] is not None else None                
            bulk_units.append(unit)
        Unit.objects.bulk_update(bulk_units, ['handler_id'])
except IntegrityError as e:
    print(f'Error saving units: {str(e)}')

django's constraint page suggests that constraints can be deferrable which apparently defers them being enforced until the end of a transaction. that sounded great, until i discovered that OneToOneField does not expose any parameters for deferrable and there's a feature-request page where this is suggested to be too niche to be worth doing.

without this, the only way i can think to atomically perform a swap would be to first set all the unit's handler_id fields to None and then loop through the list and set them to their updated values. but am i missing something or understanding incorrectly? i don't see why it should be so difficult to atomically swap unique-constrained values.

CodePudding user response：

That's the limitation, for now, you can just add 1 more query to bypass it by setting everything to None before swapping

unit_models.update(handler=None)

Or you can use custom SQL migration to alter your unique constraint to deferrable.

ALTER TABLE package_unit ALTER CONSTRAINT unique_constraint DEFERRABLE INITIALLY DEFERRED