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