«если транзакция падает с ошибкой (типа «when others then»), то данную транзакцию завершают полным откатом изменений т.е. либо алгоритм отрабатывает без ошибок и сохраняем результат, либо завершаем алгоритм и не сохраняем вообще ничего».
Так как раз ваш предлагаемый подход в КАЖДОЙ процедуре глушить все иск.ситуации и делать rollback противоречит этой здравой концепции.
Предположим у нас есть управляющая процедура Main (в ней одна транзакция, в конце процедуры commit), которая вызывает по очереди три процедуры, которые делают какие-то этапы общей транзакции, выполняют dml. Назовем их Step1, Step2, Step3. Если в Step2 произойдет exception, то произойдет откат dml, выполненных в Step1 и Step2. Далее, поскольку raise не сделан, управление перейдет в Step3, и затем общий commit в Main. В итоге мы получили в базе не «все или ничего», а только dml из Step3, что при разборе крайне загадочно и поставит разработчика в тупик.
Если же использовать rollback to savepoint Step2 и raise, управляющая процедура получит информацию, что Step2 не выполнен и сможет далее принять решение, можно ли переходить к Step3 или нужно аварийно завершиться без commit. Если в Main программист не предусмотрел блок обработки ошибок, то после exception в Step2 Main завершится аварийно без commit и мы получим «все или ничего».
Мое мнение такое, что when others then… raise и rollback to savepoint в обработчике ошибок общего назначения — это как раз должно использоваться всегда, а вот отказ от них возможен в редких частных случаях. Такая практика поможет избежать множества сложновылавливаемых ошибок. Приведенные примеры показывают, как, создавая вроде бы полезную фичу для борьбы с ошибками, фактически добавлять новые ошибки, которые будут «стрелять» при обработке исключительных ситуаций. Как маскировать реальные причины проблемы.
Самая сложная категория багов — это баги, допущенные в обработчике исключительных ситуаций. И обработчик из вашего примера их содержит.
Список параметров каждой процедуры есть в системных view. Можно написать функцию проверки наличия всех параметров в тексте пакета/процедуры и запускать эту функцию по триггеру в момент компиляции пакета/процедуры на тестовом или разработческом стенде.
Тогда программист не сможет забыть указать параметр.
Обработка when others без raise внутри — отличный способ выстрелить себе в ногу. Вызванная процедура отработала вхолостую, вызывающая об этом ничего не знает и продолжает как ни в чем не бывало.
Архитекторы у вас боятся пустых полей, зато не боятся лишнего insert. Очень странные архитекторы.
А ещё rollback без savepoint, который откатывает весь dml, сделанный в вызывающей процедуре. Такая мина заложена, что когда она взорвется, уйдет не один час на то, чтобы понять, почему данные не сохраняются в базу.
Вероятность 50/50, что программист при добавлении параметра в процедуру забудет добавить его в строку p_paramvalue и в самый неподходящий момент выяснится, что его значение не залогировано, а оно-то как раз и необходимо.
Хороший набор антипаттернов для включения в каждую процедуру!
Посмотрите, как устроены гистограммы статистики, какой объем они занимают. Для запросов, которые обрабатывают маленький объем данных, но по большому количеству таблиц со сложными where, построение запроса может потребовать вычитать кратно больше данных, чем собственно выполнение запроса.
Расходы ерундовые, если разработчики позаботились о том, чтобы каждый раз не делался hard_parse.
Я не знаю как в PG, а в Oracle например на идентичный текст запроса может быть одновременно множество планов выполнения, которые используются разными сессиями. Потому что планы зависят не только от текста запроса, но и еще от кучи параметров окружения сессии.
То есть даже использование bind переменных не гарантирует отсутствия повторного hard-parse.
И вот если разработчики об этом не подозревают или вообще используют какой-то кривой генератор SQL который каждый раз новый текст генерит, или литералы, то потом встает вопрос о горизонтальном масштабировании БД, выделении реплик и т.п.
Если же с базой данных работать с умом, то можно прекрасно без этого обходиться гораздо-гораздо дольше.
База данных — сердце любой крупной системы. Оптимизация в ней или в работе с ней может многократно ускорить работу, а отношение к БД как черному ящику с данными, приводит потом к появлению костылей типа самописных кешей и т.п.
Вы писали «Раздавать всем права вручную не кажется мне более безопасным», и пояснили, что это не более безопасно, чем «логика в коде». Не вижу логической связи.
Ага, только когда логика в коде, пользователь кода в принципе не может прочитать или вызвать никакие таблицы, вью, функции или процедуры. У него нет прямого доступа в БД, в отличие от вашего варианта. Поэтому и права на них настраивать не надо и следить за их соблюдением.
Чаще всего консистентность данных в базе разрушается не пользователями, а программистами, которые допускают ошибки в бекенде или руками правят данные в базе или запускают некорректные скрипты пакетной обработки. И вот от этого всего защищают бизнес-правила инкапсулированные в БД в виде FK, констрейнов, триггеров или полного запрета на DML и замену его процедурами изменения данных, которые контролируют их согласованность.
И если этого не делать, то сами программисты бекенда потом вынуждены в коде делать кучу лишних проверок и обработчиков неконсистентных данных. Документов без строк, ссылок на несуществующие или неактуальные элементы справочников, запросы, возвращающие несколько записей, когда по логике должна быть одна, неуникальная нумерация (буквально на той неделе столкнулись в смежной системе) и т.п.
Потому что все эти неконсистентности в данных обычно никто не правит или просто не может выловить, откуда они появляются.
И как тут уже кто-то писал, начинаются ежедневные запуски процедур пересчета регистра остатков, и прочие чисто технические обработки, которые мусор под ковер заметают.
Поэтому НУЖНО бизнес-логику, относящуюся к консистентности, контролировать в БД и приходится обрезать права всем, кто обращается в БД, в том числе бекенду.
Не ровно то же, потому запретить доступ всему кроме конкретного сервиса в общем случае нельзя. Знаешь логин/пароль, ip не забанен — добро пожаловать в базу делать ad hoc sql нарушая все правила бекенда.
Ну и во-вторых, зачем тащить это на бекенд со всеми его недостатками, когда это отлично делается в базе, никаких красивых фреймворков, менеджеров зависимостей, синтаксических сахаров не надо для таких задач.
Таким образом убирается лишняя точка отказа, причем очень вероятного отказа.
На функцию нужен execute, хотя это зависит наверное от СУБД. В oracle — execute.
Если персданные критичны, то они не возвращаются функцией, потому что критичны и видеть их в списке никому не требуется.
К чему вы клоните своими вопросами?
>Раздавать всем права вручную не кажется мне более безопасным
Чем что? Права в конечном итоге всегда раздаются вручную. Вопрос только в том, какие абстракции имеются в системе управления правами.
Только пользователи и роли СУБД или дополнительный контроль доступа в ядре системы, который может реализовать контроль доступа на более сложном логическом уровне, чем колонки и записи таблиц. Например права назначаются на единицу штатного расписания, и конкретный человек получает доступ в момент выполнения приказа о назначении в отделе кадров. А сам набор доступов состоит из логических прав на просмотр и изменение справочников и документов системы, а не конкретных таблиц.
При условии инкапсуляции данных в API СУБД, это все реализуется однократно в серверной части и автоматически соблюдается любыми клиентами: windows, веб, мобильные приложения и т.п.
Что касается «защиты от инъекций», это проблема программистов-школьников, которая даже не достойна упоминания. Обращение в СУБД с использованием литералов недопустимо за редчайшими обоснованными исключениями. И главные причины строгого использования bind-переменных — это плохое влияние литералов на общую производительность СУБД и проблемы совместимости форматов чисел и дат. Если формат даты или разделитель числа на сервере СУБД и в вызывающем приложении разойдутся, то будут сбои или, хуже того, параметры будут интерпретированы неверно.
view или функция, возвращающая курсор. Естественно, доступная только с высшими привилегиями доступа. Естественно, не возвращающая критичные поля вроде хеша.
Еще с Oracle 9 можно Java на серверной стороне, то есть лет 10 уже, только мало кому она пригодилась внутри БД. Используется в редчайших случаях. PL/SQL отлично решает все проблемы бизнес-логики.
В архитектуре «бизнес-логика на сервере» у клиента не будет прав на select в таблице users, потому что все случаи ее использования (аутентификация, смена пароля, изменение имени, и т.п.) будет реализованы в виде хранимых процедур, и grant на select просто не будет предоставлен разработчиком.
Поэтому реализация бизнес-логики на сервере БД более безопасна.
«Ну вот создали новую функцию и забыли ограничить права»
Не ограничить, а назначить. Если забыли, прав на вызов ни у кого не будет. Поэтому забыть невозможно. Главное сознательно не давать права тем, кому они не положены
>затраты на план — величина меньшего порядка, чем собственно выполнение
Заблуждение. Построение плана сложного запроса требует анализа словаря БД, статистики (а это те же самые запросы к БД и анализ их результатов), выставления блокировки на добавление в список планов (что тормозит все остальные запросы, которые требуют хард-парсинга). Если такой запрос в итоге читает несколько блоков из кеша буферов, то задержки на парсинг будут в разы больше времени чтения.
А бюджет проекта (стоимость разработки и 1 года поддержки) можете прикинуть? Чтобы как-то сравнить ТСО?
По надежности и отказоусточивости ИМХО ничто не сравнится с двумя разнесенными стендбаями Oracle. Тем более такая технически сложная система как описана в посте. Потенциальных точек для сбоев очень много, специалистов по самописной системе интеграции Oracle/Tarantool на рынке нет. Выглядит все очень уязвимо.
Интересно, сколько выиграли в деньгах.
Что мешало просто поднять standby Oracle и использовать его как кэш? Или реплицировать в cache-Oracle избранные таблицы через GG. Структура таблиц ведь осталась та же? Зачем весь этот геморрой с перекачкой 2.5 Tb через csv-файлы? А «прогрев» — просто востановление из бекапа вообще без нагрузки на master-Oracle, да и не нужен он будет на практике почти никогда. И изменения в DDL автоматом бы реплицировались. Для Oracle 10 Тыс запросов в секунду это семечки, если речь идет о мелких запросах, типичных для OLTP.
Для от поставщика, когда пикнул паллету, получил SSCC код. А как система по SSCC понимает место на складе? Откуда артикульный состав паллета берется? Поставщик предоставляет в электронном виде?
Для мульти-SKU как же вы обходитесь без буферной зоны? Где происходит рассортировка на монопаллеты, прямо у ворот?
>И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица. И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада. Таким образом с точки зрения СУБД все сводится у удалению старых проводок и созданию новых. Изменение проводок не требуется.
По поводу двойного учета и у типа договора не готов дискутировать, потому что не понимаю, какая предметная область и как реализовано. На первый взгляд, изменение типа договора вообще не должно влиять на реестр остатков, так как договор не является документом товародвижения, то есть основанием для проводок.
Вчера в 17:18 вы спросили: «Как именно это делать?»
Я привел рабочую схему. И да, я сделал материализацию, которая НА ПРАКТИКЕ решает проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.
Так как раз ваш предлагаемый подход в КАЖДОЙ процедуре глушить все иск.ситуации и делать rollback противоречит этой здравой концепции.
Предположим у нас есть управляющая процедура Main (в ней одна транзакция, в конце процедуры commit), которая вызывает по очереди три процедуры, которые делают какие-то этапы общей транзакции, выполняют dml. Назовем их Step1, Step2, Step3. Если в Step2 произойдет exception, то произойдет откат dml, выполненных в Step1 и Step2. Далее, поскольку raise не сделан, управление перейдет в Step3, и затем общий commit в Main. В итоге мы получили в базе не «все или ничего», а только dml из Step3, что при разборе крайне загадочно и поставит разработчика в тупик.
Если же использовать rollback to savepoint Step2 и raise, управляющая процедура получит информацию, что Step2 не выполнен и сможет далее принять решение, можно ли переходить к Step3 или нужно аварийно завершиться без commit. Если в Main программист не предусмотрел блок обработки ошибок, то после exception в Step2 Main завершится аварийно без commit и мы получим «все или ничего».
Самая сложная категория багов — это баги, допущенные в обработчике исключительных ситуаций. И обработчик из вашего примера их содержит.
Список параметров каждой процедуры есть в системных view. Можно написать функцию проверки наличия всех параметров в тексте пакета/процедуры и запускать эту функцию по триггеру в момент компиляции пакета/процедуры на тестовом или разработческом стенде.
Тогда программист не сможет забыть указать параметр.
Обработка when others без raise внутри — отличный способ выстрелить себе в ногу. Вызванная процедура отработала вхолостую, вызывающая об этом ничего не знает и продолжает как ни в чем не бывало.
Архитекторы у вас боятся пустых полей, зато не боятся лишнего insert. Очень странные архитекторы.
А ещё rollback без savepoint, который откатывает весь dml, сделанный в вызывающей процедуре. Такая мина заложена, что когда она взорвется, уйдет не один час на то, чтобы понять, почему данные не сохраняются в базу.
Вероятность 50/50, что программист при добавлении параметра в процедуру забудет добавить его в строку p_paramvalue и в самый неподходящий момент выяснится, что его значение не залогировано, а оно-то как раз и необходимо.
Хороший набор антипаттернов для включения в каждую процедуру!
Расходы ерундовые, если разработчики позаботились о том, чтобы каждый раз не делался hard_parse.
Я не знаю как в PG, а в Oracle например на идентичный текст запроса может быть одновременно множество планов выполнения, которые используются разными сессиями. Потому что планы зависят не только от текста запроса, но и еще от кучи параметров окружения сессии.
То есть даже использование bind переменных не гарантирует отсутствия повторного hard-parse.
И вот если разработчики об этом не подозревают или вообще используют какой-то кривой генератор SQL который каждый раз новый текст генерит, или литералы, то потом встает вопрос о горизонтальном масштабировании БД, выделении реплик и т.п.
Если же с базой данных работать с умом, то можно прекрасно без этого обходиться гораздо-гораздо дольше.
База данных — сердце любой крупной системы. Оптимизация в ней или в работе с ней может многократно ускорить работу, а отношение к БД как черному ящику с данными, приводит потом к появлению костылей типа самописных кешей и т.п.
Чаще всего консистентность данных в базе разрушается не пользователями, а программистами, которые допускают ошибки в бекенде или руками правят данные в базе или запускают некорректные скрипты пакетной обработки. И вот от этого всего защищают бизнес-правила инкапсулированные в БД в виде FK, констрейнов, триггеров или полного запрета на DML и замену его процедурами изменения данных, которые контролируют их согласованность.
И если этого не делать, то сами программисты бекенда потом вынуждены в коде делать кучу лишних проверок и обработчиков неконсистентных данных. Документов без строк, ссылок на несуществующие или неактуальные элементы справочников, запросы, возвращающие несколько записей, когда по логике должна быть одна, неуникальная нумерация (буквально на той неделе столкнулись в смежной системе) и т.п.
Потому что все эти неконсистентности в данных обычно никто не правит или просто не может выловить, откуда они появляются.
И как тут уже кто-то писал, начинаются ежедневные запуски процедур пересчета регистра остатков, и прочие чисто технические обработки, которые мусор под ковер заметают.
Поэтому НУЖНО бизнес-логику, относящуюся к консистентности, контролировать в БД и приходится обрезать права всем, кто обращается в БД, в том числе бекенду.
Ну и во-вторых, зачем тащить это на бекенд со всеми его недостатками, когда это отлично делается в базе, никаких красивых фреймворков, менеджеров зависимостей, синтаксических сахаров не надо для таких задач.
Таким образом убирается лишняя точка отказа, причем очень вероятного отказа.
Если персданные критичны, то они не возвращаются функцией, потому что критичны и видеть их в списке никому не требуется.
К чему вы клоните своими вопросами?
Чем что? Права в конечном итоге всегда раздаются вручную. Вопрос только в том, какие абстракции имеются в системе управления правами.
Только пользователи и роли СУБД или дополнительный контроль доступа в ядре системы, который может реализовать контроль доступа на более сложном логическом уровне, чем колонки и записи таблиц. Например права назначаются на единицу штатного расписания, и конкретный человек получает доступ в момент выполнения приказа о назначении в отделе кадров. А сам набор доступов состоит из логических прав на просмотр и изменение справочников и документов системы, а не конкретных таблиц.
При условии инкапсуляции данных в API СУБД, это все реализуется однократно в серверной части и автоматически соблюдается любыми клиентами: windows, веб, мобильные приложения и т.п.
Что касается «защиты от инъекций», это проблема программистов-школьников, которая даже не достойна упоминания. Обращение в СУБД с использованием литералов недопустимо за редчайшими обоснованными исключениями. И главные причины строгого использования bind-переменных — это плохое влияние литералов на общую производительность СУБД и проблемы совместимости форматов чисел и дат. Если формат даты или разделитель числа на сервере СУБД и в вызывающем приложении разойдутся, то будут сбои или, хуже того, параметры будут интерпретированы неверно.
Поэтому реализация бизнес-логики на сервере БД более безопасна.
Не ограничить, а назначить. Если забыли, прав на вызов ни у кого не будет. Поэтому забыть невозможно. Главное сознательно не давать права тем, кому они не положены
Заблуждение. Построение плана сложного запроса требует анализа словаря БД, статистики (а это те же самые запросы к БД и анализ их результатов), выставления блокировки на добавление в список планов (что тормозит все остальные запросы, которые требуют хард-парсинга). Если такой запрос в итоге читает несколько блоков из кеша буферов, то задержки на парсинг будут в разы больше времени чтения.
По надежности и отказоусточивости ИМХО ничто не сравнится с двумя разнесенными стендбаями Oracle. Тем более такая технически сложная система как описана в посте. Потенциальных точек для сбоев очень много, специалистов по самописной системе интеграции Oracle/Tarantool на рынке нет. Выглядит все очень уязвимо.
Интересно, сколько выиграли в деньгах.
Для мульти-SKU как же вы обходитесь без буферной зоны? Где происходит рассортировка на монопаллеты, прямо у ворот?
Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица. И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада. Таким образом с точки зрения СУБД все сводится у удалению старых проводок и созданию новых. Изменение проводок не требуется.
По поводу двойного учета и у типа договора не готов дискутировать, потому что не понимаю, какая предметная область и как реализовано. На первый взгляд, изменение типа договора вообще не должно влиять на реестр остатков, так как договор не является документом товародвижения, то есть основанием для проводок.
Я привел рабочую схему. И да, я сделал материализацию, которая НА ПРАКТИКЕ решает проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.