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
