Создание хранимой процедуры на лету. Какие риски / проблемы?

Во-первых, ответ на этот вопрос действительно зависит от того, для чего именно предназначена эта хранимая процедура. Если он просто читает данные или создает набор результатов для отчета, и вы не возражаете, если он немного непоследователен, то, вероятно, у вас все в порядке. Если он делает что-то отдаленно интересное с вашими данными, то это очень рискованно. Вы должны подумать о том, возможно ли (и что произойдет) для двух пользователей (или одного и того же пользователя дважды) одновременно запустить несколько версий одной и той же хранимой процедуры. Для меня это запах крушения поезда. Один из вариантов - разрешить это изменение процедуры только тогда, когда другие пользователи не вошли в систему, или принудительно загрузить их из базы данных, если они есть. Другой вариант - создать новую хранимую процедуру с немного другим именем и поменять их местами, когда вы сочтете это безопасным.

Другая проблема заключается в том, что одним из основных преимуществ хранимых процедур является то, что план выполнения кэшируется, что означает, что он будет выполняться быстрее. Если вы создаете их на лету, вы теряете это преимущество.

Для транзакционной системы это, вероятно, довольно дорого. Если у вас есть большое пакетное задание и по какой-то причине вы хотите использовать генератор кода (довольно распространенная архитектура в инструментах ETL, особенно Oracle Warehouse Builder и Wherescape Red), это небезосновательно.

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

Это было бы крайне редко, когда это было бы желательно - не могли бы вы вообще уточнить, что заставило вас выбрать этот подход?

Я бы не стал этого делать лично.

Как вы упомянули, вам потребуются дополнительные привилегии для предоставления доступа для создания / изменения объектов базы данных. Это может создать серьезную угрозу безопасности, поскольку ничто не помешает вашему приложению создать вредоносную хранимую процедуру, если кто-то обнаружит в ней брешь в безопасности.

Если ваша схема изменится, измените хранимые процедуры со схемой.

Вы не сможете изменить процедуру, если один или несколько пользователей выполняют процедуру или другую процедуру, которая ссылается на вашу процедуру. Вы будете блокироваться до тех пор, пока все зависимые процедуры и процедура, которую вы хотите скомпилировать (и я думаю, что процедуры, которые вы вызываете из своей процедуры, но я не уверен) не будут использоваться. Это может занять много времени в загруженной производственной системе, и если вам не повезет, вы можете ожидать, пока не будут использоваться все зависимости (5 минут в Oracle) .
Вы также можете попасть в очень неприятные ситуации (у меня). Возьмем, к примеру, хранимые процедуры B и C, обе из которых вызывают A, процедуру, которую вы пытаетесь скомпилировать. Когда никто не запускает B, система блокирует B. Теперь любой пользователь, пытающийся запустить B, остановится. Затем система пытается заблокировать C, но C создает очень длинный отчет, который не будет готов еще в течение 10 минут. У вас будет тайм-аут ожидания блокировки, и у некоторых из ваших пользователей система не будет отвечать в течение 5 минут. Мой опыт работы с Oracle, я бы удостоверился, что ваша целевая СУБД не ведет себя таким же образом, имеет более быстрые сбои или лучшую стратегию получения блокировок. Я предполагаю, что предупреждаю, что то, что похоже, может работать на сервере разработки, может резко выйти из строя в загруженной производственной системе.

Я не уверен, что блокировка, описанная Тони Бан Брахимом, верна в SQL Server 2005.

У меня есть несколько длительно работающих SP (пакетный процесс за 3 часа, включающий около 30 подпроцессов), и я смог изменить SP, пока он все еще работает. (Я не верю, что изменения вступят в силу до следующего запуска, но это не вызывает никаких блокировок или ошибок). Теперь внешний долгосрочный SP вызывает SP динамически с EXEC и статически, но я изменил как корневой, так и вложенные SP, пока они работают без сообщений об ошибках или блоков.

НАПИШИТЕ свой первоначальный вопрос, я думаю, что ваша тактика хороша, если используется контролируемым образом.

Вы упомянули, что при выполнении этого изменения вы будете добавлять и / или изменять вызывающий профиль хранимой процедуры. Как вы выполняете синхронизацию нового профиля вызова с приложением, которое его выполняет? Каков ваш план отката, если вам когда-нибудь придется отменить внесенное изменение?

Раньше я просто добавлял увеличивающийся числовой суффикс к имени хранимой процедуры с новым профилем вызова - затем вы можете изменить старую версию SP, чтобы вызвать новую со значением по умолчанию для параметра, а затем вы можете выпустить свое программное обеспечение, назвав новую версию.

Если что-то сломается в вашей новой версии и вам придется выполнить откат, вызовы старой сохраненной процедуры будут по-прежнему работать без ошибок и просто заполнят настраиваемые поля значениями по умолчанию.

Я не знаю наверняка, но это звучит как одно или оба:

• архитектурная проблема
• блокирует ли существующий код таблицы схемы из приложения?

Я бы посмотрел, какой код блокирует таблицы схемы, и перепишу этот код. Есть ли у вас что-то стороннее, блокирующее эти таблицы?