Django/PostgreSQL: Уникальное ограничение с динамическим условием, таким как expires_at > now()

Я создаю защищенную OTP-систему на Django. Модель выглядит следующим образом:

class OTP(models.Model):
    MAX_ATTEMPTS = 3
    DEFAULT_EXPIRE_IN_MINUTES = 1

    class Purposes(models.IntegerChoices):
        LOGIN = 1, "Login"
        REGISTER = 2, "Register"
        VERIFY_PHONE = 3, "Verify Phone"
        VERIFY_EMAIL = 4, "Verify Email"

    otp_hash = models.CharField(max_length=64)
    purpose = models.IntegerField(choices=Purposes)
    phone = PhoneNumberField(null=True, blank=True)
    email = models.EmailField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    expires_at = models.DateTimeField()
    failed_attempts = models.PositiveSmallIntegerField(default=0)
    used = models.BooleanField(default=False)
    used_at = models.DateTimeField(null=True, blank=True)

То, что я хочу обеспечить:

Пользователь не должен получать другой OTP, если уже существует неиспользуемый OTP с истекшим сроком действия (на основе поля expires_at) (used=False) OTP для тех же целей.

Условие должно выполняться либо для:

  • (телефон, назначение), если установлен телефон, или
  • (адрес электронной почты, назначение), если установлен адрес электронной почты.

И эта логика должна выполняться даже при одновременных запросах.

То, что я пробовал до сих пор:

  • Проверьте уровень приложения с помощью OTP.objects.filter(...) перед созданием нового - это небезопасно в условиях гонки.
  • Добавляем логическое поле is_expired и условное UniqueConstraint следующим образом:
class Meta:
    constraints = [
        models.UniqueConstraint(
            fields=["phone", "purpose"],
            condition=Q(is_expired=False),
            name="unique_active_phone_otp"
        ),
        models.UniqueConstraint(
            fields=["email", "purpose"],
            condition=Q(is_expired=False),
            name="unique_active_email_otp"
        ),
    ]

Но для этого требуется обновлять поле is_expired вручную или периодически при использовании функции expires_at < now(), что усложняет работу и о чем легко забыть.

Существует ли более простой способ обеспечить эту уникальность на уровне базы данных, даже при таких временных условиях, как expires_at > now()?

Ниже версии Postgres 18 вы можете настроить ограничение на исключение, чтобы убедиться, что не может существовать двух OTP данного типа, действительных в течение одного и того же периода. Вам нужно будет заменить expires_at на valid_between, который содержит tstzrange временных меток, в течение которых этот OTP действителен:
демонстрация в db<>fiddle

--btree_gist lets `purpose` into the gist index behind the exclusion constraint
create extension btree_gist;

create table otp(purpose int,expires_at timestamptz);

alter table otp rename column expires_at to valid_between;
alter table otp
  alter column valid_between type tstzrange 
    using tstzrange(valid_between,valid_between+'1 minute'::interval)
 ,add constraint one_valid_otp_per_purpose_at_a_time 
    exclude using gist ( purpose WITH =
                        ,valid_between WITH &&);

insert into otp values
  (1,tstzrange(now(),now()+'1 minute'::interval)) 
, (2,tstzrange(now(),now()+'1 minute'::interval));

insert into otp values
  (1,tstzrange(now()+'10 seconds',now()+'65 seconds'::interval));

ERROR:  conflicting key value violates exclusion constraint "one_valid_otp_per_purpose_at_a_time"
DETAIL:  Key (purpose, valid_between)=(1, ["2025-06-18 12:50:21.469054+00","2025-06-18 12:51:16.469054+00")) conflicts with existing key (purpose, valid_between)=(1, ["2025-06-18 12:50:11.468011+00","2025-06-18 12:51:11.468011+00")).

Postgres 18 привносит синтаксис without overlap в unique ограничения, которые делают то же самое:

alter table otp 
  add constraint one_valid_otp_per_purpose_at_a_time 
    unique(purpose,valid_between without overlap);

Чтобы действительно проверить этот флажок, используйте view ( или virtual сгенерированный столбец в версии >18) для вашего поля is_expired. Таким образом, этот флаг может быть вычислен во время запроса путем сравнения now() с valid_between.

create view v_otp as 
select*,not (now() <@ valid_between) as is_expired 
from otp;

select*from v_otp;
purpose valid_between is_expired
1 ["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00") False
2 ["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00") False 
select pg_sleep(6); 
select now();
select*from v_otp;
now
2025-06-18 12:57:43.539171+00
purpose valid_between is_expired
1 ["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00") True
2 ["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00") True

Сам по себе столбец view/virtual generated не должен решать проблему гонки. Это делается с помощью блокировки - неявной, явной, рекомендательной. Ваш клиент должен запросить представление и получить все еще действительный OTP, если он там есть (я предполагаю, что это ваше намерение). Если нет, то ограничение unique/exclude гарантирует, что если несколько запросов попытаются сгенерировать новый, только один из них завершится успешно - остальные могут перехватить созданное ими исключение и отклонить его.

Вы можете pg_advisory_lock() указать OTP (цель+владелец) еще до проверки или явно select..for update, или просто попытайтесь ввести update (поля used и used_at), поскольку update попытается получить блокировку. Любое из этих действий приведет к тому, что база данных разместит ваши параллельные клиентские узлы в очереди.

Вернуться на верх