Comments 16
Рекомендация не использовать create or replace - только для пакетов и функций же, верно понимаю ?
А то в голове вопрос, как тогда пере собирать вью или матвью. Делать постоянно бекап и через него ?
Не рекомендуется использовать create or replace во всех случаях. Лучше явно выполнить удаление объекта, а потом создать заново (для функций и представлений это даже делается на разных этапах). Это уменьшит риск случайной замены объектов с потерей.
С удалением обычных view (на первом этапе) и созданием их заново (на третьем), как правило, меньше всего проблем. Самая часто встречающаяся - необходимость соблюдать порядок создания/удаления, который учитываем взаимозависимости.
С materialized view сложнее, т.к. они не только обрабатывают данные, но и хранят их. Как отметил в статье, для таких структур универсального подхода нет, из-за наличия данных их чаще приходится относить к инкрементальным изменениям (обрабатываются на втором этапе).
Это уменьшит риск случайной замены объектов с потерей
Что такое "случайная замена функции" и с потерей чего? Особенно в контексте описанного сценария, где дропается вообще всё и потом создается вообще всё.
Не один и не два раза в разных проектах встречалась проблема - в разных файлах создавались однотипные объекты с одинаковым именем. Причины разные, от copy-paste и забыли переименовать, до неправильного порядка при накате скриптов. Если использовать только create вместо create or replace, обновление развалится при перой же попытке накатить, это быстро локализует проблему.
Что-то я как будто середину чьего-то разговора застал... Не очень понятно, на какую дискуссию рассчитан пост без деталей реализации и примеров из жизни. Соберите минимальную репу на GitHub или скрины того, что у вас есть приложите. Как выглядит репозиторий, код пишется в Notepad++ и вы узнаете о том, что синтаксис кривой только на деплое или как это все у вас происходит.
Из описания не понял, так у вас миграции (дельты) хранятся в Git или финальные определения объектов. По таблицам больше похоже, что миграции (ALTER TABLE ADD COLUMN), по хп больше похоже что скрипт один и навсегда, т.е. смесь подходов. Как тогда рулите тем, что является дельтой, которую нужно применить к серверу? Ф-и и хп, поскольку описано безусловное пересоздание, вероятно, всегда "тупо" все накатываете. Больше костыль при отсутствии решения, чем решение.
Упоминается некий скрипт, в котором ф-и и хп в правильном порядке дропаются и создаются - вы его "на руках" держите или есть автоматизация, которая эту последовательность строит глядя в код?
У этого подхода есть и отрицательная сторона — на уровне скрипта сложнее оценить влияние изменения структуры хранения данных на представления и функции. В момент внесения изменения их уже нет, не проследить цепочку зависимостей, а потом они будут пересозданы уже с учетом изменившейся структуры хранения. Здесь целесообразно применять дополнительные инструменты, сравнивающие структуру БД до внесения изменения и после, чтобы выявить все изменения.
ай донт ферштейн че вообще происходит в этом абзаце. По "структуре" вы накатываете дельту - сами изменения. Почему вам нужно их искать дополнительными инструментами? Вопрос риторический, абзац нуждается в расшифровке и переводе на человеческий.
все сущности пересоздаются каждый раз
не самое удачное жонглирование словом "сущности" в разговоре про Relational DB. Функции и процедуры никто сущностями не называет.
Пересоздание таких сущностей занимает немного времени
но вы же, если правильно понял, дропаете их до применения миграций к таблицам, а создаете уже после. А миграции данных вполне себе могут занимать сколько угодно времени. И БД будет сидеть голенькая все это время. Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
крупных проектов с небольшим уклоном в сторону PostgreSQL
правильно ли я понимаю, что проект крупный во всем остальном, а код и структура БД как раз маленькие? Чуть больше фактов и подробностей добавьте пожалуйста, суть решения и к каким ситуациям оно применимо осталось внезапно за скобками: сколько таблиц, хранимок. Как выглядит финальный деплой-скрипт.
И решение вы оцениваете как полностью ваш локальный велосипед (без оценки качества, только уникальность) или его можно сравнить со способом работы DbUp, SqlPackage или чего-то еще?
По таблицам больше похоже, что миграции (ALTER TABLE ADD COLUMN), по хп больше похоже что скрипт один и навсегда, т.е. смесь подходов. Как тогда рулите тем, что является дельтой, которую нужно применить к серверу?
Действительно, используется двойной подход, именно для этого и разделяем сущности по их отношению к данным - хранят (инкрементальные миграции) или только обрабатывают ("скрипт один и навсегда").
На сервере накатывается код соответствующий релизу. Он содержит и новые дельты и актуальные версии процедур/представлений.
> Как тогда рулите тем, что является дельтой, которую нужно применить к серверу? На сервере накатывается код соответствующий релизу
как вы определяете, какой код соответствует какому релизу?
Ветка или тег релиза (что принято в проекте) совпадают для кода миграций и соответствующего кода бэка.
А как добиться соответствия кода релизов БД, нескольких приложениях бэка и фронта - отдельная тема)
И как вы по тэгу в гите, который отдает вам всё содержимое репозитория, понимаете, какие из триллиона миграций не нужно, а какие нужно применять в данном конкретном релизе? Верно ли я понимаю, что автоматизации вокруг описанной системы примерно ноль и вы всё "понимаете" и собираете руками? И правильную последовательность дропов, и правильный набор миграций.
И правильно ли я понял, что остальные вопросы из первого комментария останутся без ответа?
В статье описан подход по разделению сущностей БД, и, в зависимости от этого, разные алгоритмы организации хранения (инкременты или постоянный скрипт) и применения (строго один раз или многократно).
Подход может быть реализован разными инструментами.
В текущем проекте используем liquibase, он обеспечивает очередность, проверяет контрольные суммы файлов, сколько раз скрипт должен быть выполнен, какие скрипты уже были накачены. Деплой - liquibase получает код необходимой версии и параметры коннекта к БД, обрабатывает changelog файлы.
На какой сервер, куда, чего, чем накатывается? Вам же предложили сделать "минимальную репу на GitHub или скрины".
И решение вы оцениваете как полностью ваш локальный велосипед (без оценки качества, только уникальность) или его можно сравнить со способом работы DbUp, SqlPackage или чего-то еще?
Вероятно мы говорим о разных вещах. Повторюсь, в статье описан подход по разделению сущностей БД, и, в зависимости от этого, разные алгоритмы организации хранения (инкременты или постоянный скрипт) и применения (строго один раз или многократно).
Подход может быть реализован разными инструментами и liquibase, и "своими велосипедами" (в опросе вариант "Разработали и применяем своё решение" достаточно популярен).
Пересоздание таких сущностей занимает немного времени
но вы же, если правильно понял, дропаете их до применения миграций к таблицам, а создаете уже после. А миграции данных вполне себе могут занимать сколько угодно времени. И БД будет сидеть голенькая все это время. Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
Инкрементные миграции, которые выполняются на втором этапе, действительно могут выполняться долго. Удаление и создание хранимок и представлений, как правило, занимет мало времени, экономия не всегда целесообразна в ущерб простоте поддержки.
Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
В БД может быть от нескольких десятков view, которые зависят друг от друга, часть из них, могут иметь код вида
CREATE VIEW view_name AS SELECT table_name.*, ... FROM table_name ...Да, выборка без фиксации колонок не относится к лучшим практикам, но такой код объективно встречается, не всегда есть время и ресурсы, чтобы отрефакторить и оттестировать все подобные места в связке БД и бэка. Если view не пересоздать, новая колонка не попадёт из table_name во view_name.
Пересоздавать проще, чем каждый раз вручную отслеживать что должно/может измениться при "добавлении одной колонки в одну таблицу".
Ну учитывая что огород знатный речь не о mssql. Там бы все легко решил проект в vs и sqlpackage.
Но огород все равно знатный. Зачем все дропать то?
Нужно называть объекты и файлы одинаково.
Нужно разделить типы объектов по папкам.
В альтере таблиц нужно добавлять проверки на существование или сделать что то по аналогии рефакторлога из SQL проектов.
Дропать и пересоздавать только то что нужно по зависимостям из бд.
Накатывать только те объекты что у вас в релизе. И запускать все только один раз зачем запускать что то несколько раз вообще?
Не знаком с инструментом SqlPackage, подскажите, как с его помощью эффективно реализовать обновление из комментария выше - "добавление одной колонки в одну таблицу". SqlPackage самостоятельно выявит все объекты, в т.ч. харнимые процедуры, где поведение может измениться из-за добавления колонки? SqlPackage создает набор скриптов, которые позволяют с любой версии обновиться до актуальной?
Как разрешается ситуация, если два разработчика параллельно внесли изменения в структуру БД (в разные таблицы добавили по одной колонке) затрагивающие view_name, получили наборы файлов миграций m1 и m2, отправили PR каждый со своими изменениями, их изменения приняли. Порядок приёма PR может повлиять на конечный вид view_name ?
В подходе описанном в статье, когда m1 и m2 это инкрементальные изменения, а view будет пересоздано, обе добавленные колонки будут учтены во view_name.
SqlPackage создает набор скриптов, которые позволяют с любой версии обновиться до актуальной?
Именно так: привести любого состояния БД к тому что надо, что в репозитории/в релизе.
SqlPackage самостоятельно выявит все объекты, в т.ч. харнимые процедуры, где поведение может измениться
Выявит, но только отправит на рефреш. Если хп менялись, то альтернет. "На всякий случай" ничто никогда не дропается.
CREATE VIEW view_name AS SELECT table_name.*
Подстраиваться под эти ужасы на мой взгляд гиблое направление, которое приводит к появлению "Больше костыль при отсутствии решения, чем решение" (мой коммент выше). Исходники надо линтить, подобные ужасы выявлять и устранять абсолютно все. SELECT * имеют право существовать только в условно одноразовых говноскриптах. Если нет SELECT *, то не существует хп или вьюх, которые каким-то непостижимым образом берут и неявно меняют свое поведение, потому что где-то в таблицу колонку добавили или две. Такого не существует как явления (если навести порядок). Гугл говорит, что для postgresql какие-то линтеры есть. К SonarQube есть плагин с поддержкой postgresql.
Порядок приёма PR может повлиять на конечный вид
view_name?
Это уже про что-то за гранью добра и зла.
Организация SQL скриптов крупного проекта