Разница в последовательности запросов, сгенерированных в Django и Postgres для select_for_update

Ответ 1

Редакция:

Как заставить Django проверять перекрывающиеся резервирования?

Можно добавить специальный метод для модели validate_unique:

    from django.db import models
    from django.core.validators import ValidationError
    from django.forms.forms import NON_FIELD_ERRORS

    class Dish(models.Model):
        name = models.CharField('Dish name', max_length=200)

    class Menu(models.Model):
        user_id = models.IntegerField()
        dish = models.ForeignKey(Dish)
        order_start_time = models.DateTimeField()
        order_end_time = models.DateTimeField()

        def validate_unique(self, *args, **kwargs):
            # call inherited unique validators
            super().validate_unique(*args, **kwargs)  # or super(Menu, self) for Python2.7
            # query if DB already has object with same dish 
            # and overlapping reservation 
            # [order_start_time, order_end_time]
            qs = self.__class__._default_manager.filter(
                    order_start_time__lte=self.order_end_time,
                    order_end_time__gte=self.order_start_time,
                    dish=self.dish,
                )
            # and this object is not the same we are working with
            if not self._state.adding and self.pk is not None:
                qs = qs.exclude(pk=self.pk)
            if qs.exists():
                raise ValidationError({
                    NON_FIELD_ERRORS: ['Overlapping order dates for dish'],
                    })

Давайте попробуем его в консоли:

    from core.models import *
    m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 10:00', order_end_time='2016-03-22 15:00')
    m.validate_unique()
    # no output here - all is ok
    m.save()
    print(m.id)
    8

    # lets add duplicate
    m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 12:00', order_end_time='2016-03-22 13:00')
    m.validate_unique()
    Traceback (most recent call last):
       File "<console>", line 1, in <module>
       File "/Users/el/tmp/hypothesis_test/menu/core/models.py", line 29, in validate_unique
           NON_FIELD_ERRORS: ['Overlapping order dates for dish'],
    django.core.exceptions.ValidationError: {'__all__': ['Overlapping order dates for dish']}

    # excellent!  dup is found!

    # But! Django helps you find dups but allows you to add them to db if you want it!
    # It responsibility of your application not to add duplicates.

    m.save()
    print(m.id)
    9

Как быть уверенным, что noboby может добавить дубликат?

В этом случае вам нужно сделать CONSTRAINT на уровне базы данных.

В консоли PostgreSQL:

    CREATE EXTENSION btree_gist;

    -- our table:
    SELECT * FROM core_menu;
    id | user_id |    order_start_time    |     order_end_time     | dish_id
   ----+---------+------------------------+------------------------+---------
     8 |       1 | 2016-03-22 13:00:00+03 | 2016-03-22 18:00:00+03 |       1
     9 |       1 | 2016-03-22 15:00:00+03 | 2016-03-22 16:00:00+03 |       1

    DELETE FROM core_menu WHERE id=9; -- we should remove dups before adding unique constraint

    ALTER TABLE core_menu 
        ADD CONSTRAINT core_menu_exclude_dish_same_tstzrange_constr 
            EXCLUDE USING gist (dish_id WITH =, tstzrange(order_start_time, order_end_time)  WITH &&);

Теперь давайте создадим дублирующий объект и добавим его в db:

    m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 13:00', order_end_time='2016-03-22 14:00')

    m.save()
    Traceback (most recent call last):
       File "/Users/el/tmp/hypothesis_test/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
         return self.cursor.execute(sql, params)
    psycopg2.IntegrityError: ОШИБКА:  конфликтующее значение ключа нарушает ограничение-исключение "core_menu_exclude_dish_same_tstzrange_constr"
    DETAIL:  Key (dish_id, tstzrange(order_start_time, order_end_time))=(1, ["2016-03-22 13:00:00+00","2016-03-22 14:00:00+00")) conflicts with existing key (dish_id, tstzrange(order_start_time, order_end_time))=(1, ["2016-03-22 10:00:00+00","2016-03-22 15:00:00+00")).

Отлично! Теперь данные проверяются на уровне программ и db.