get() returned more than one Model -- it returned 4! - but only 1 Model in db

Question

In a Django view, I'm getting a MultipleObjectsReturned error using a get_or_create. Many questions have been asked about this, but those people seem to be using this construct wrongly. I think that I'm using it correctly, but please correct me if I'm wrong.

I have the following model:

class MyModel(models.Model):
    field1 = models.ForeignKey('app.OtherModel', on_delete=models.SET_NULL, null=True)
    field2 = models.CharField(max_length=50, blank=True, null=True)

    field3 = models.JSONField()
    field4 = models.JSONField()

    updated = models.DateTimeField(_('Date updated'), auto_now=True)
    created = models.DateTimeField(_('Date created'), default=timezone.now)

    ...

    class Meta:
        ordering = ('-created',)
        constraints = [
            models.UniqueConstraint(fields=['field1', 'field2'], name='unique_model'),
        ]

And this code in my view:

o, created = MyModel.objects.get_or_create(field1=value1, field2='value2',
    defaults={'field3': '...', 'field4': '...'})

So the get is performed on the two fields that are in the UniqueConstraint, the other fields are listed in the defaults part (in case the create is required).

The MultipleObjectsReturned is thrown on that last line, when Django performs the get part of the get_or_create, but only in some rare cases - most of the time this construct works well.

The view is actually a handler for AWS's SNS event messages for SES, handling delivery delays, bounces and complaints. In theory it should not happen that this view is called exactly twice, causing a race-condition. When I check the server logs, I indeed do not see multiple calls to this view at the time this error is thrown. (Only one email was sent and SNS tries resending the SNS event only every 20 seconds.)

This article explains the problem well, but unfortunately does not provide a solution for me.

Note that sometimes "-- it returned 2!", sometimes "-- it returned 3!" and sometimes "-- it returned 4!"

Also note that the error is thrown with fields that are not None: the ForeignKey of field1 points to a row in "OtherModel" and the CharField of field2 contains a string.

And the kicker is: when I run that same get_or_create in ipython in production, using the exact same values as mentioned in the error (in Sentry), no error is thrown and the get is performed correctly.

What and why is this going wrong in the view?

I'm using Django 3.2.2 and Postgres 11.10.

Answer 1

In an SQL database system, NULL is not equal to NULL (usually NULL = NULL returns NULL), therefore if the fields are nullable, it means that the other field can be repeated an arbitrary number of times.

What we can do is implement three constraints: uniques on the two fields, uniqness on one field if the other is NULL and vice-versa, so then the constraints look like:

from django.db.models import Q, UniqueConstraint

class MyModel(models.Model):
    field1 = models.ForeignKey(
        'app.OtherModel',
        on_delete=models.SET_NULL,
        null=True
    )
    field2 = models.CharField(max_length=50, blank=True, null=True)
    # …

    class Meta:
        ordering = ('-created',)
        constraints = [
            UniqueConstraint(fields=['field1', 'field2'], name='unique_model1'),
            UniqueConstraint(fields=['field1'], condition=Q(field2=None), name='unique_model2'),
            UniqueConstraint(fields=['field2'], condition=Q(field1=None), name='unique_mode3'),
        ]

Not all database backend however might enforce unique constraints with a condition.