Comments 57
Функция, которая считает баланс взаиморасчетов в разрезе клиентов и заносит его во временную таблицу, это целостность данных или бизнес-логика?
Это целостность данных, так как это денормализация посредством агрегации уже имеющихся данных в таблице.
Это бизнес-логика какая-то.
Почему?
Если отчет типа "Баланс клиента с XX.XX.2016 по YY.YYY. 2016 на момент ZZ.ZZ.2016", который потом не должен меняться, даже если что то добавили задним числом — то бизнеслогика.
Если просто кеширование расчетов — денормализация.
Если же вам нужно обеспечить, чтобы эта таблица ВСЕГДА содержала данные, соответствующие вашим операциям, тогда её можно и нужно рассматривать как часть ваших данных, к которым предъявляются требования к целостности.
Сам по себе расчёт баланса — чистой воды бизнес-логика, а поддержка целостности данных — это одно из требований к бизнес-логике. Иногда, в частных случаях, одно от другого можно отделить. Но в общем случае это вещи, идущие параллельно, и просто так взять и поставить слева умных, а справа красивых, не получится.
> Временная таблица не может быть частью нормализованных данных.
Почему? Понятие «временности» тут всего лишь определяет характер хранения данных в ней. Непосредственной связи с нормализацией/денормализацией нет. Если во временной таблице вы храните результаты агрегирования данных по каким-либо признакам, они вполне себе могут быть нормализованы.
а поддержка целостности данных — это одно из требований к бизнес-логике.
По вашей логике и HTML/CSS/OpenGL можно за уши притянуть.
Ведь графический интерфейс — это одно из требований бизнес логики, не так ли?
По вашей формулировке это задача однозначно относится к целостности.
За уши можно притянуть при желании всё, что угодно, при любой логике.
> Ведь графический интерфейс — это одно из требований бизнес логики, не так ли?
Бизнес-логика определяет правила поведения и преобразования данных. Соответственно, графический интерфейс к бизнес-логике отношения не имеет, а вот целостность данных очень даже.
> По вашей формулировке это задача однозначно относится к целостности.
Обоснуйте своё мнение, пожалуйста. Я исхожу из того, что «функция, которая считает баланс взаиморасчетов в разрезе клиентов и заносит его во временную таблицу,» на выходе получает результат, имеющий вполне определённое значение в предметной области.
Поэтому появляется код, который её создаёт.
1) Код её создаёт быстро, таблица в БД нет — это БЛ.
2) Код тормозит, решили прихранивать в БД — теперь это функция, обеспечивающая целостность?
3) Код тормозит, в БД триггер тормозит, решили кешировать на клиенте — снова БЛ?
> В каждом таком случае задаю вопрос: если бы данные были нормализованы, то была бы нужна такая функция?
А как может быть не нужна функция, генерящая данные, если данные то нужны?
не знаю как можно ими не пользоваться…
приходилось слышать, что триггеры жутко замедляют базу данных
Пф, снимите трассу 10046 на вставке с триггером — и не только услышите, но и увидите.
Насколько я понял, statement restart можно огрести, если вы в рамках транзакции пытаетесь выйти за её рамки — написать в output (возможно, прокинуть внутри автономную транзакцию, но это проверять нужно), скорее всего — дернуть внешний сервис, но если работаете в обозначенных рамках транзакции — постэффектов не будет. С другой стороны, если вы в рамках транзакции дергаете внешний сервис, то либо у вас крупные проблемы, либо вы очень круто знаете как их решать — в обоих случаях это не проблема базы и триггеров.
А по поводу замедления — всё ж очень просто — триггер выполняет работу. Очевидно, что он замедляет выполнение операций. Насколько — зависит от вашего намерения и умения — поставите внутри крутиться длинный цикл -может и фатально замедлить. Но обычно работа триггера это размен в выполнении операции — либо её один раз выполнить в триггере при обновлении данных, либо выполнять на каждой выборке. А дальше простое сравнение характеристики нагрузки — если чтений существенно больше — ставим на триггер, если записей больше — на выборку.
Я как-то доходил и до точки, когда генерация последовательностей тормозила определенную работу, это же не значит, что ими нельзя пользоваться.
Сорри, я не хотел бы сейчас ударяться в философию и рассуждать, когда у меня начинается «серьезная» логика, а когда ещё нет :)
Или я что-то не знаю?
Впрочем, есть мнение, что всю бизнес логику легко можно реализовать средствами современной СУБД, такой как PostgreSQL или Oracle. Подтверждение нахожу в своём just-for-fun проекте.
.оффтопик
я далёк (к сожалению) от веб-технологий, поэтому задам тупой вопрос, извините. у меня есть проект, где вся бизнес-логика реализована на Postgres (с приложением под windows), права на процедуры (функции) заданы там же, на Postgres. насколько я понимаю, в веб-приложениях так не делается. вопрос собственно в том, что делать, если очень хочется, чтобы уже на этапе авторизации пользователя он представлял сам себя в БД? и, соответственно все запросы шли от его имени. такое вообще бывает? смысл в том, чтобы оставив всё как есть в БД, попробовать сделать к ней веб-интерфейс, (почти) ничего не меняя в процедурах, и уж точно ничего не меняя в распределении ролей.
такое вообще бывает?
Да, но зависит от числа одновременно работающих пользователей. Просто в пуле придется держать число коннектов по числу одновременно работающих авторизованных пользователей. А так, имхо, это более правильный подход.
кстати о «сделать проверку», каким образом? я пробовал писать пустые процедуры с разным доступом, только для проверки прав, и потом ловить исключения, в MSSQL это работает, в PostgreSQL победить не смог.
Вам не нужно поддерживать соответствие между пользователями в БД и на сервере. В простейшем случае в базе из пользователей будут владелец базы и пользователь для пула коннектов, под которым все ходят. У этого пользователя не будет прав на таблицы, только на выполнение функций с security definer. В каждой транзакции сервер должен выставлять
begin;
select set_config('ваша_переменная_пользователя_из_pg_config', 'пользователь_на_сервере', false);
select func(...);
commit;
Перед выполнением каждой функции в ней должна быть проверка на то, что имя пользователя, содержащееся в глобальной переменной на уровне данной транзакции, разрешено для запрашиваемой функции. Это делается через
select current_setting('ваша_переменная_пользователя_из_pg_config') into username;
А дальше мы и смотрим, может ли данный username использовать вызванную функцию. Но эта проверка должна быть на уровне некой дополнительной функции, а не через пользователей PostgreSQL.
> Вы не любите кошек? Да вы просто не умеете их готовить! (с) Альф
А мне вспоминается «а вы на шкаф залезьте»
Расскажете как?
Да. так тоже можно.
set total = select sum(operations.amount) from operations where operations.account = current_account
после обновления 100 000 записей. И на «быстрое построчное» поменять можно далеко не всегда.
если ваше обновление затрагивает 100000 разных счетов в примере топикстартера, в таблице операций миллиард записей и нет индекса по счету — ну, вперед! :)
Естественно, исходить надо от задачи, можно придумать пример когда и триггер будет плох.
как будто триггеру индекс не нужен
> Естественно, исходить надо от задачи, можно придумать пример когда и триггер будет плох.
Можно. А когда репозиторий будет плох?
А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.
>> нет индекса по счету
>как будто триггеру индекс не нужен
индекс по счету нужен на таблицу totals (и в моем и в вашем варианте), но не на operations (в вашем)
>Можно. А когда репозиторий будет плох?
ваш репозиторий — «сферический конь в вакууме» его можно сделать хорошо, можно плохо, а вариант с триггером понятен потому прозрачен и просчитывается
>А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.
вот! а как по вашему реализованы эти самые вьюшки :) триггера используются самим oracle в хвост и в гриву
Я вас уверяю, что на operations индекс по счёту будет, к такой таблице не по счёту вообще не обращаются.
> ваш репозиторий — «сферический конь в вакууме» его можно сделать хорошо, можно плохо, а вариант с триггером понятен потому прозрачен и просчитывается
Потому, что вы на триггер изначально вешаете ограничения, что он быстр и нужные индексы созданы.
> а как по вашему реализованы эти самые вьюшки
А мне, простите, без разницы. Мне не надо писать и отлаживать триггера — это функционал оракла, который мне, действительно, не надо переписывать.
> триггера используются самим oracle
И, кстати, почему именно в оракле?
домыслы.
триггера позволяют стоить учет по «левым» параметрам по которым не нужно строить выборку и потому индекс по ним — дорогое удовольствие
>… это функционал оракла, который мне, действительно, не надо переписывать.
триггер — точно такой же функционал оракла
Опыт.
И нехитрая логика.
> триггера позволяют стоить учет по «левым» параметрам по которым не нужно строить выборку и потому индекс по ним — дорогое удовольствие
Вы меня обвиняли в сферичности коней, а сами загадками говорите. Я не представляю зачем строить учёт по параметрам, по которым не надо строить выборку.
Впрочем, ради хитрой оптимизации можно и не так раскорячиться.
> триггер — точно такой же функционал оракла
Нет, это абстракция более низкого уровня. Ниже KonstantinSoloviov выписал часть того, что надо учитывать с триггерами. С вьюхой всё проще.
А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.
Увы, не решается. Материализованное представление требует периодического обновления. При первой же записи в operations это представление будет содержать устаревшие данные до очередного обновления.
Помню, там что-то мутное про «какие-то импорты, API, сторонние приложения, делают разные люди, команды».
> Обновлять представление во время каждого коммита слишком накладно.
А по вашей ссылке написано «However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table.». На сервере не надо экономить.
Да и не обязательно в режиме COMPLETE обновлять.
2. Кроме того, вы же, наверное, понимаете, что таким триггером как у вас описан вы прямо таки нарываетесь на блокировки? Что если несколько сессий попробуют добавлять строки по одному счету?
3. Триггеры реально очень сильно ухудшают производительность и к тому же заставляют сервер генерить больше реду. Сравните свое приведенное решение и нормальное решение где одна процедура будет и обновлять остаток и вставлять записи в таблицу на паре десятков конкурентных процессов в несколько тысяч вставок
4. И, кстати, если триггер написан так что повторный его вызов изменяет его логику, то проблему с рестартами решить в принципе невозможно. Кроме того, не забывайте про нюансы с вызовом тригера при merge.
5. Больше всего пугает ваше доверие к таким данным… Кто и насколько часто потом перепроверяет ваши остатки? А вдруг вы там с рестартами по 3 раза некоторые операции снижения баланса понапроводили?
тут он прав на все 100% — dml триггеры все-таки все-таки это зло, и применяются они только из компромисса, когда уже «слишком дорого и долго» переделывать изначальные архитектурные ошибки.
Можно пруф?
нормальное решение где одна процедура будет и обновлять остаток и вставлять записи в таблицу на паре десятков конкурентных процессов в несколько тысяч вставок
Весь вопрос как эта процедура будет обновлять остаток. Если пересчитывать select sum() from table, то это будет долго на больших объёмах. Если обновлять set total = total + current_amount, то на нескольких конкурентных процессах вы в любом случае наткнётесь либо на блокировки, либо на некорректные итоги.
А вдруг вы там с рестартами по 3 раза некоторые операции снижения баланса понапроводили?
Рассчитываю, что буква A из ACID обеспечена.
Такой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.
Тогда, наверное, мы сменим зависимость "тормозов" от объёма данных на тормоза от нагрузки на базу? — но, в принципе, для большинства нужд типа бухучёта, это подойдёт.
Такой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.
На самом деле это не так. Триггер может в какой-то момент для чего-нибудь быть отключен или сломан. Так что рассчитывать итог всё равно нужно. Просто это можно делать не в триггере, а, например, в шедулере с необходимой периодичностью, для того чтобы исправлять возможные сбои в подсчётах суммы.
В случае использования хранимых процедур всю логику и алгоритмы работы можно сосредоточить в одном месте.
А триггеры — зло, имхо. На них можно делать только логирование изменений в таблицах,
но запихивать в них бизнес-код нет смысла, если не хочется выстрелить себе в ногу.
И да, для сохранения целостности нужно не бояться использовать конестрейнты,
благо, в Оракле они очень развиты.
Триггеры очень сильно размывают логику программ.
В случае использования хранимых процедур всю логику и алгоритмы работы можно сосредоточить в одном месте.
с логикой становится тяжелее, да. но есть узловые (критические) таблицы, с большим количеством точек записи/изменений в них (десятки хранимых процедур), где очень хочется иметь целостные данные вне зависимости от того, кто как и когда придумает менять данные в таблиц. и решение предложенное в статье мне кажется изящным и, самое главное, лёгким для переваривания сервером БД.
Немного не в тему, но...FLAP из примера стоило бы "заточить" по антикоррупционную направленность, научив обнаруживать основные схемы мошенничеств и оформить её в виде одного исполняемого файла (который даже установки не потребует) — тогда она была бы востребована в среде старших по дому и граждан, проживающих в домах, а также у правоохранителей (в качестве несложного "термометра" для коррупции).
Триггеры прекрасны, но пользоваться надо ими очень аккуратно.
Ваш
...«лёгкий» запрос типа:
update totals
set total = totals.total + current_amount
where totals.account = current_account
сработает только в случае инсерта в таблицу operations и то при условии что в totals есть соотвествующий account.
То есть не забываем делать insert новых account в totals.
Дальше, если изменяется значение current_amount в operations триггер должен учитавать :old и :new значения current_amount.
Eсли делается delete operations и это последная запись с таким account в totals — надо делать delete в totals.
И вишенка на торте: в operations можно изменить не только current_amount но и сам account! Тогда в totals надо модифицировать значение по старому счету и добавить по новому.
И закрываться надо со всех сторон, не полагаясь, например, на то, что бизнес-логика не позволяет менять номер счета — все рано или поздно найдется умник с каким-нибудь sqldevelop-ером на перевес :)
Да и триггера конечно должны быть только AFTER т.к. в BEFORE триггерах :new, :old значения окончательно не определены.
И закрываться надо со всех сторон, не полагаясь, например, на то, что бизнес-логика не позволяет менять номер счета — все рано или поздно найдется умник с каким-нибудь sqldevelop-ером на перевес :)
у умника должен быть соответствующий доступ. а простой смертный имеет права только на процедуры, которые за него придумал разработчик, и у него нет прав даже читать из таблицы простым select'ом. но вы правы, метод хорош тем, что защищает данные в том числе и от самого себя.
Конечно, в реальности триггер несколько развесистее, с контролями и обработкой разных ситуаций. Я сознательно привёл только участок кода, на оптимизацию которого обращаю внимание в статье.
Опять же, призываю всех включать своё инженерное мышление. Воможно, в каких-то конкретных случаях есть смысл ради повышения производительности упростить триггерную функцию и запретить изменения, например, поля account в триггере BEFORE. В других случаях может быть принято решение пожертвовать скоростью операций вставки/изменения/удаления ради тотального контроля. А в каких-то случаях жертвуют целостностью ради повышения скорости вставки данных.
Впрочем, строго говоря, ссылка на ограничения фремфорка и т.п. — по сути попытка оправдать недостаток квалификации.
Отклонили комментарий, ну и ладно, хотя бы ошибку исправьте.
Вы не любите триггеры?