PostgreSQL: обеспечение уникальности записи с проверкой даты валидности

Как бы вы решали такую задачу? Предположим, есть таблица с купонами, и у купонов есть некая дата устаревания valid_until. Вам надо обеспечить такое ограничение (constraint) на уровне БД, чтобы у одного человека мог быть только один действующий купон.

Т.е., таблица изначально выглядит так:

CREATE TABLE coupons (
    id  bigint primary key generated by default as identity,
    user_id bigint not null,
    created_at timestamp not null,
    valid_until timestamp not null
)

Попытка решения в лоб

Думаю, всем известно, что есть ключевое слово UNIQUE, с помощью которого можно обеспечить уникальность какого-то поля/набора полей. Но напрямую оно нам не подойдёт, так как в базе могут остаться устаревшие купоны, которые будут конфликтовать со свежими.

Также многим известно, что UNIQUE может идти с условием. Казалось бы, добавить просто к констрейнту WHERE (valid_until > NOW()), и дело с концом. Однако так нельзя: невозможно сделать констрейт по функции, которую заранее не вычислить. Т.е. WHERE (is_valid) бы прокатило, но не сравнение с NOW().

Можно выкрутиться с помощью триггера. Но есть способ поэлегантнее.

Решение через EXCLUDE и tsrange

В постгресе есть тип данных tsrange (диапазон времени), а также ключевое слово EXCLUDE, с помощью которого можно запретить вставку строк, где диапазоны пересекаются. Это можно объединить:

EXCLUDE USING GIST (tsrange(created_at, valid_until) WITH &&)

Но такое ограничение действует на всю таблицу, а не в рамках юзера. Поэтому надо немного расширить выражение, добавив user_id:

EXCLUDE USING GIST (user_id WITH =, tsrange(created_at, valid_until) WITH &&)

Чтобы это работало, нужно сначала включить расширение btree_gist.

Итого проверяем:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE coupons (
    id  bigint primary key generated by default as identity,
    user_id bigint not null,
    created_at timestamp not null,
    valid_until timestamp not null,
    EXCLUDE USING GIST (
        user_id WITH =, 
        tsrange(created_at, valid_until) WITH &&
    )
);

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
(100500, now(), now() + interval '1 day');

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
    (100501, now(), now() + interval '10 day');

INSERT INTO coupons(user_id, created_at, valid_until)
VALUES
    (100500, now(), now() + interval '10 day');

Последний insert не пройдёт: "ERROR: conflicting key value violates exclusion constraint"

Т.е. по сути решение идет в одну-единственную строчку EXCLUDE.

Решение через триггеры

В проектах Каруны мне иногда доводится писать триггеры, и они всегда многословны. Для данной задачи это выглядело бы примерно так:

CREATE OR REPLACE FUNCTION check_uniq() RETURNS trigger
    LANGUAGE plpgsql AS
$$
BEGIN
    IF (SELECT true
        FROM coupons
        WHERE id != NEW.id
          AND user_id = NEW.user_id
          AND valid_until > NEW.created_at) THEN
        RAISE EXCEPTION 'Coupon for this user already exists';
    END IF;
    RETURN NEW;
END
$$;

CREATE TRIGGER coupon_unique_trigger
    AFTER INSERT
    ON coupons
    FOR EACH ROW
EXECUTE PROCEDURE check_uniq();

Как правильно заметили в комментах, триггер написан неидеально — его надо дорабатывать и отлаживать. В общем, долго и неприятно. Можно еще извратиться и по условному крону явно обновлять состояние валидности купонов (is_valid), тогда можно было бы сделать unique с условием, но это тоже писанина еще та.

Статья написана на основе постов канала Cross Join