Constraints в PostgreSQL, или о том, как попытаться спокойно жить
Данный материал был создан на основе одноимённого доклада на PGConf.Online, вошедшего в число самых популярных выступлений конференции. Поскольку тема ограничений по-прежнему сохраняет свою актуальность, а смотреть видео с мероприятий любят не все, появилась эта статья.
Концепция «тупого хранилища»
В последние годы разработчики ПО всё чаще утверждают, что база в их проекте «всего лишь тупое хранилище, и поэтому никакой логики в ней нет». Откуда такой подход? Обычно он объясняется сложностями миграции, развёртывания, неудобствами при работе с системами контроля исходного кода. Не стоит списывать со счетов и простую человеческую лень: раз всё и так нормально, зачем связываться с логикой в СУБД? Создали таблицы (или, ещё лучше, пусть ORM их создаст!), и всё отлично.
NoSQL для документов
Случай с NoSQL ещё проще — не надо ничего создавать, контролировать и напрягать мозги, всё уже автоматизировано, оно само работает. Этого вполне достаточно, если из базы нужно просто доставать документы по идентификатору, но если требуется решать задачи посложнее, то всё-таки выбирают SQL СУБД. Их использование, однако, ограничивается созданием таблиц и индексов, логика на стороне СУБД и в этом случае видится избыточной.
СУБД: не только технология, но и бизнес-инструмент
Такой подход является очень распространённым (люди вообще ленивы!). Тем не менее, крайне наивно дистанцироваться от хороших возможностей только из-за нежелания заморачиваться и приобретать новые навыки. СУБД — это очень изощрённая система хранения (чтобы понять это, достаточно почитать про уровни изоляции или процедуры резервного копирования). СУБД помогает синхронизировать бизнес-процессы и избежать реальных убытков, иногда в очень крупном размере.
Ecommerce: проблемы в системах учёта
Конечно, если у вас на складе товар в одном экземпляре, а вы его продали сразу двум покупателям, ваш бизнес от этого не сильно пострадает. В самом худшем случае вы из-за этой ситуации потеряете одного клиента-физлицо. Но что будет, если случатся более серьёзные неприятности? Осенью 2020 года я слышал о скандале внутри крупного российского онлайн-ритейлера. Тогда на складе обнаружили целые залежи товаров, ожидающих отправки покупателям ещё с весны. Виноваты, как всегда, были все сразу и никто конкретно.
«Сюрпризы» в коде
Концепция «СУБД — тупое хранилище» не может предотвратить такие случаи. Более того, она создаёт для них предпосылки. Это всего лишь вопрос времени, когда случится подобное. Даже лучший в мире разработчик рано или поздно совершит ошибку. Проект может существовать с этой ошибкой годами, до момента, пока не будут созданы условия, в которых она проявится. Тушить такой внезапный пожар всегда проблематично. Необходимо иметь в виду, что даже давно проверенный старый код однажды может преподнести неприятные сюрпризы.
Почему «умная» база лучше?
Как же уберечься от подобного рода неприятностей или хотя бы уменьшить вероятность их возникновения? Стоит вернуться к логике в СУБД, сделать её «умным» хранилищем. С помощью ограничений (constraints) можно сделать так, чтобы СУБД не допускала перевод себя в явно некорректное состояние. Конечно, определить все некорректные состояния раз и навсегда вряд ли получится. Однако, можно хотя бы составить список условий, в которых база точно никогда не должна находиться.
Когда ограничения стоят денег
Однажды знакомый разработчик ошибся при расчёте обменного курса криптовалют и отправил нескольким получателям по триста тысяч долларов каждому вместо ожидаемых трёхсот. Это могла бы предотвратить обычная нудная проверка, если бы она была. «Для такой-то пары значение отправленной суммы должно быть не больше 1/5 от значения полученной», — если бы такое ограничение было вовремя установлено, можно было бы сберечь время и нервы:
check(case when in_ticker='BTC' and out_ticker='ETH'
then out_amt/in_amt>5
else true
end)Намного приятнее и спокойнее разбираться, почему платёж не ушёл, чем выяснять, почему он ушёл не туда или с неправильной суммой.
Дело не только в производительности
Таких жизненных историй я знаю очень много, поэтому стандартные возражения против ограничений целостности кажутся мне нелепыми. Естественно, ограничения снижают производительность, зато никому не придется продавать почку, если что-то пойдет не так. (Вряд ли кому-то захочется иметь дело с взысканием миллионного ущерба на работе.) Знаю, что многие молодые разработчики искренне верят в надежность своего кода. C годами это проходит, но зачем же ждать так долго?
Двойная защита для любых ценностей
Мой опыт подсказывает, что в реальном приложении, работающем с деньгами или другими материальными ценностями, любая проверка должна дублироваться. При этом желательно, чтобы код для каждой из проверок писали разные люди, которые не знакомы друг с другом и думают по-разному. Если вернуться к вышеупомянутому примеру с криптовалютами, одна проверка должна быть для грубой оценки — «не более 1/5», а вторая — проверять, чтобы вычисленный курс не отличался от реального более чем на 2%:
(select abs(1-(select (out_amt/in_amt)/r.rate from rates r
where r.ask_ticker='TCKR1' and r.bid_ticker='TCKR2'))<0.02)Зачем дублировать проверки? Рано или поздно какая-то из этих проверок сломается, и в отсутствие «запасной» проверки придётся работать без ограничений, а это чревато убытками.
Паранойя или управление рисками?
Расскажу ещё об одном случае из моей практики. Мне нужно было убедиться, что заказы не размещаются повторно. Для этого я создал уникальный индекс по идентификатору товара плюс предусмотрел простенькую проверку того, не был ли такой же товар добавлен в таблицу ранее. Казалось бы, паранойя на грани сумасшествия: зачем проверять то, что и так проверяется, причем не строго корректным образом (корректная реализация проверки на уникальность хоть и несложна, но, тем не менее, немного нетривиальна)? Тем не менее, в процессе очередной реорганизации уникальный индекс по недосмотру был заменен на обычный. Двойные продажи товаров начались бы, если бы не резервная проверка. Замена индекса обнаружилась только дней десять спустя, тем не менее, проблемных ситуаций не возникло. Так что у паранойи тоже есть практичная сторона. Конечно, такие дублирующиеся проверки подходят не для всех случаев. Но если нарушение бизнес-процессов грозит заметными потерями, благоразумно иметь две проверки или даже больше.
В наиболее критичных ситуациях отдельные разработчики рекомендуют ставить не менее трех серверов и выполнять операцию только если минимум два из них приходят к консенсусу (замечание в сторону: именно такое поведение автоматически получается в случае использования блокчейна).
Почему гибкость не всегда хороша
Ещё одно распространённое возражение против ограничений — отсутствие гибкости. Разумеется, база данных станет негибкой! Но при применении ограничений в этом и состоит наша цель. Так, например, при описании таблицы с примыкающими диапазонами дат («с -infinty по 2020–01–01, с 2020–01–01 по 2020–06–01, с 2020–06–01 по 2021–01–01, с 2021–01–01 по infinity») вставить в середину отдельный диапазон не так-то просто. Для этого потребуются либо отложенные (deferrable) проверки и не просто вставка нужной строки, но и изменение соседних, либо придётся обновить таблицу целиком.
create constraint trigger check_daterange_consistency_iu
after insert or update on fees deferrable initially deferred
for each row execute procedure check_fees_table_consistency();
create or replace function wb.check_fees_table_consistency()
returns trigger as
$code$
begin
perform pg_advisory_xact_lock(hashtext('fees'),
hash_record(row(new.period_start, new.period_end)));
if new.period_start<>'-infinity'
and not exists(select * from fees f where f.period_end=new.period_start)
then
raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new);
end if;
if new.period_end<>'infinity'
and not exists(select * from fees f where f.period_start=new.period_end)
then
raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new);
end if;
if exists(select * from fees f
where (f.period_start,f.period_end)overlaps(new.period_start, new.period_end)
and f.period_start<>new.period_start
and f.period_end<>new.period_end )
then
raise sqlstate '23Q01' using message=format('Invalid date range:%s',new);
end if;
end
$code$
language plpgsqlНо ведь именно это и является целью ограничений — ни в коем случае не допустить непримыкающих диапазонов дат. Нам необходимо гарантировать, что диапазоны, во-первых, примыкают к друг другу и, во-вторых, не перекрываются.
В реальной жизни настолько жесткие ограничения встречаются не столь часто, как хотелось бы. Если бы большее число компаний и организаций пользовались возможностями СУБД в полном объёме, мы реже слышали бы о гигантских убытках и недовольных пользователях.
Осиротевшие строки и неопределённость
Какова же стандартная практика? Обычно у каждой таблицы есть первичный ключ, хотя встречаются — и даже слишком часто — таблицы и без него. Реже, но всё же довольно часто применяют ограничение not null. Что касается внешних ключей (foreign keys), вполне можно наблюдать базы без них. А если в базе нет внешних ключей, и она уже достаточно стара и велика, есть 100% вероятности, что в её дочерних таблицах есть осиротевшие строки. Это плохо само по себе, плюс закономерно предположить, что если родитель для одной строки потерян, то и для другой он может быть указан некорректно.
«Экзотические» ограничения и ecommerce
Некоторые ограничения практически не встречаются. Например, EXCLUDE позволяет не допускать перекрывающихся диапазонов. Проверка JSON или XML на соответствие схеме также довольно экзотична. Совсем редки проверки в триггерах, хотя последние предназначены в том числе и для нестандартных проверок. Так, стандартными средствами СУБД невозможно обеспечить ограничение целостности вроде «каждый заказчик может иметь не более трех неоплаченных заказов» или «сумма неоплаченных заказом не должна превышать определенного значения». Для реализации этого ограничения сначала потребуется написать триггер на добавление либо обновление удаление строки в таблице «Заказы». Потом в нём обязательно нужно использовать блокировку, чтобы предотвратить обновление строки пользователя в транзакции, либо придётся использовать advisory-блокировку, используя хеш от идентификатора пользователя в качестве ключа блокировки. (Пример можно видеть выше в тексте триггерной функции:
pg_advisory_xact_lock(hashtext('fees'),
hash_record(row(new.period_start, new.period_end)));Использование advisory-блокировки является отступлением от стандарта, но при большом количестве операций модификации она даёт выигрыш по производительности.
Триггеры и финтех
Не получится обойтись без триггеров и в случае проверки значения. Например, в России рублевый счет коммерческой нефинансовой организации в банке обязательно должен начинаться на 40702810. Проверку можно описать как CHECK, но в более сложных случаях придется обратиться к таблице-справочнику (40701 — финансовые организации, 40703 — некоммерческие, 408 — частные лица и т.п.). Правда, подобный триггер обычно имеет достаточно простой вид:
if not exists(select * from account_chart ac where ac.prefix=substring(new.account from 1 for 5)) then
raise sqlstate ‘23Q03’ using message=’Invalid account for …’;
end if;ASSERTION: круто, но не реализовано
В стандарте SQL описано такое полезное ограничение уровня БД, как ASSERTION. Будь оно реализовано, оно позволяло бы делать замечательные вещи. Так, ограничение на количество неоплаченных заказов было бы совершенно тривиальным. Ограничения по суммам продаж, по датам отгрузки тоже не представляли бы никакой проблемы. К сожалению, это ограничение не реализовано в Postgres. В оправдание Postgres можно сказать, что оно вообще не реализовано ни в одной из популярных СУБД. Честно говоря, не очень понятно, как его вообще можно эффективно реализовать.
Зачем писать триггеры?
Таким образом, если требуются достаточно нестандартные ограничения, придётся создавать триггеры или регулярно запускать скрипты проверки. (Это справедливо для ситуаций вида «не больше трех неоплаченных заказов», «оплаченный заказ не может оставаться неотгруженным более суток» и им подобных.) Почему требуются какие-то дополнительные скрипты? Потому что со временем вполне корректные состояния могут оказываться некорректными. Например, товар, предназначенный для отправки, не может находиться на складе позднее какой-то даты:
for r in select * from goods g
where g.shipment_date is null
and g.received_date>current_date-1
loop
perform send_notification_to_manager_on_stalled_delivery(r);
end loop;Так как штатных триггеров по времени в СУБД не предусмотрено, придётся эмулировать их самостоятельно. Можно даже создать целый фреймворк для этого, но это уже тема другого разговора.
Основные тезисы
Какие основные тезисы этой статьи стоит запомнить? Я бы остановился на нескольких:
База данных — это не «тупое хранилище».
База данных не должна допускать явно некорректные операции.
База данных должна обнаруживать некорректные состояния.
Вывод
Понятно, что реализация подхода «база данных — умное хранилище» требует дополнительных усилий. Тем не менее, без этих усилий обойтись не удастся, если данные в СУБД являются частью сколько-нибудь серьезного бизнеса. Даже если проверки и другие ограничения не решат всех проблем, они позволят обнаруживать их раньше. А время — деньги.
