[Fixed]-Difference in sequence of query generated in Django and Postgres for select_for_update



How to make Django validate overlapping reservations?

It is possible to add special method for a model 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(
            # 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'],

Lets try it in console:

    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')
    # no output here - all is ok

    # 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')
    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's responsibility of your application not to add duplicates.


How to be sure noboby can add duplicate?

In this case you need to make a CONSTRAINT on database-level.

In PostgreSQL console:

    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 &&);

Now lets create the duplicate object and add it to 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')

    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")).

Now data is validated at program and db levels.

Leave a comment