Некоторые ситуации в работе PostgreSQL кажутся неочевидными, пока не попытаешься детально понять, «почему это работает так». Из-за незнания таких особенностей иногда разработчик сам провоцирует проблемы для нормальной работы своего приложения в будущем.

Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:

• накрутка serial при ON CONFLICT
• накрутка счетчика транзакций

Допустим, вам необходимо сделать таблицу БД со списком адресов. Причем, каждый сайт должен упоминаться только один раз. Очень распространенная задача, взять, хотя бы, каталоги сайтов.

Сайт можно идентифицировать по имени домена. Если бы мы жили в идеальном мире, то на этом месте мой пост бы и закончился. :) Но, тут всё только начинается…

Большинство сайтов доступны (отдается HTML или происходит редирект) по двум адресам: example.com и www.еxample.com. В почти идеальном мире :) мы бы просто отбросили “www.” и забили на эту проблему. Но находятся некоторые криворукие личности, которые обеспечивают работу своего сайта только по одному адресу, “только с www.”.

Решение проблемы такое:

Целостность данных легко нарушить. Бывает так, что в поле price попадает значение 0 из-за ошибки в коде приложения (периодически всплывают новости, как в том или ином инет-магазине продавали товары по 0 долларов). Или бывает, что удалили юзера из таблицы, но какие-то данные о нем остались в других таблицах, и эти данные вылезли в каком-то интерфейсе.

PostgreSQL, как и любая другая СУБД, умеет делать некоторые проверки при вставке/изменении данных, и этим обязательно нужно уметь пользоваться. Давайте посмотрим, что мы можем проверять:

1. Кастомный подтип через ключевое слово DOMAIN

Как бы вы решали такую задачу? Предположим, есть таблица с купонами, и у купонов есть некая дата устаревания 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
)