Комментарии 43
Тоже такое чувство было при чтении статьи. От журнала миграции БД потенциально зависят все компоненты, которые используют данную БД. Добавление лишнего слоя процессинга в миграции не решит эту принципиальную проблему, а только все усложняет.
Нужно просто иметь модульную систему, независимые БД, достаточно малые, чтобы разработчики редко мешали другу другу в изменениях, и просто использовать инструмент для миграций. Ну и интегрироваться на уровне api, а не через базу.
Я такую задачу решил просто создав триггер в каждой бд сервера . Этот триггер все события dml пишет в отдельную таблицу отдельной БД. Всё что делается на сервере фиксируется последовательно, модификации таблиц, код хранимых процедур, функций, индексов и тп. Проекты у меня небольшие, максимум по 150 процедур и 40 таблиц. Сейчас 6 проектов ведётся и поддерживается. Не для продаж, корпоративная бд задач производства. Задачи все непростые. Одну из них о описал в своей статье на хабре
Когда мы обсуждали различные подходы - также обсуждали и этот вариант.
Но он не подошёл.
К примеру, бывает и такое, когда за один МР ты насоздаёшь объектов, а потом часть удалишь. Потом смотри весь этот лог и перетирай его.
А вот, к примеру, фишка, которая у нас сейчас вводится в поддержку ( вот прям сейчас допиливаем и будем тестить ): можно комментарием к объекту управлять его контектом.
Вот пример: допустим, такая-то таблица должна быть только на тестовом и ревью сервере, а на проде - нет. Всё что мы делаем - просто в комментарии к таблице пишем флаг, а парсер уже всё сам настраивает.
Ну и ещё раз попробую напомнить про тот подход, который я описывал: если мы в функции с 1к строк ( к примеру ) модифицируем 1-у строку, то ревьюеру будет удобно такое проверять, поскольку в репозитории будет показана модификация одной строки, а не сплошь тысяча новых строк.
Надеюсь, я донёс мысль :)
У нас на одном из проектов ( не самом большом ) 700+ функций. Бывает и такое, что одновременно открыто 3 МР на изменение одной и той-же функции.
Свою систему я не развивал особо, только комментарии к версии процедур добавляю при изменении в процессе разработки. При фильтрации можно показывать из таблицы процедур только наименование, дату и комментарии. Можно настроить триггер на раскладку по отдельным таблицам процедур, функций и тп. Ну и разработать что то вроде упрощенного git, но руки не доходят, команды нет а самому не разорваться на все что нужно. Пока откат вручную делаю, но это бывает редко.
Hidden text
Я не понял в самом начале - как получилось, что слияние изменений функции myfunc в git (очевидно, в файл myfunc.sql) не обнаружило конфликтов между миграциями #100 и #101?
Ну потому что это разные файлы
То есть, вы хранили изменения объектов, но не сами объекты? Так, может, исправить это и всё?
100, 101 и 102
Это как раз пример того, как собирают код в миграции ( это всё разные файлы версии БД )
Поэтому они даже гит-конфликтом не отловятся
Что-то я так и не понял, где и как будет разрешаться конфликт, описанный в примере параллельного изменения функции (те самые миграции с номерами 100, 101 и 102). Не, я, слава богу, не разработчик, и мне это всё не надо, просто любопытно... но я не понимаю, почему при создании вот этих миграций не фиксируется номер той миграции, которая послужила базой. Если бы было зафиксировано, что есть миграция 101 после миграции 100, есть миграция 102 после миграции 100, то такой конфликт никак не мог бы привести к тому, что применение второй миграции трёт изменения, внесённые первой миграцией, потому что миграция 102 не может выполняться после миграции 101, она идёт после миграции 100. И ловится такой конфликт буквально по щелчку пальцев. А разрешается - исключительно вручную. Ведь в примере с функцией требования никак не могут быть реализованы одновременно - либо то, либо другое, а если реализовать их вместе, получится третье, которого вообще никто не просил.
К сожалению, именно для не-разработчика трудно понимать ваш рассказ. Вы постоянно прыгаете с одного примера на другой (только что говорили про функцию, и сразу, вообще без перехода, начали про таблицу users), потом на третий... в результате всей цепочки и не видать.
Миграции миграциями, а объекты целиком хранить надо в первую очередь - описания таблиц (create table), процедур и прочего. А иначе как??
Хм. Странно это всё.
А не проще иметь просто разные контура для этих целей. Ревью, проверка, тестирование. Ну то есть отдельную СУБД.
Ну и все кто перерос опенсорс. Рано или поздно пилят что-то своё, удобное и полезное.
Для команды разработки более удобным бывает например делать миграции программным кодом, который в sql превращается. А откатывать миграции удобно снапшотами, если там и данных полно поменялось вместе с метаданными. Ну зачем например пилить целый скрипт отмены, если до даже лютой миграции можно скопировать ВМ, да и всё.
Зачем вообще нужны миграции для функций и хранимых процедур? Миграции используем только при существенных изменениях метаданных, когда действительно нужно трансформировать данные из старой структуры в новую.
Почему то пропущена ветка test. А извлечение кода функций и процедур из БД лишает возможности их препроцессинга, что уже ни в какие ворота не лезет.
Зачем вообще нужны миграции для функций и хранимых процедур?
Немного не понял, а как вы предлагаете заливать функции и процедуры в прод ?
ЗЫ думаю, вы не верно интерпретировали слово "миграция" в контексте этой статьи
Почему то пропущена ветка test
Вы можете выполнять тесты как в общей ветке разработки, как в предпроде, так и в фич-ветке и соответствующей ей БД, вас никто не ограничивает
Немного не понял, а как вы предлагаете заливать функции и процедуры в прод ?
CREATE OR REPLACE FUNCTION или CREATE OR REPLACE PROCEDURE. А как еще?
Естественно после интеграционного тестирования на staging. А уж история изменений кода функций и процедур пусть остается в GIT.
Аналогично, и подавляющее большинство изменения метаданных производятся чем то типа ALTER TABLE IF EXISTS SQL_DB_INSTANCE.OBJECT_NAME ADD COLUMN IF NOT EXISTS ...
Вы можете выполнять тесты как в общей ветке разработки, как в предпроде, так и в фич-ветке и соответствующей ей БД, вас никто не ограничивает
В ветке контура не только интеграционное и нагрузочное, но даже просто полноценное тестирование произвести в общем случае невозможно. Для контуров БД обрезается многократно до скромных не более чем сотни гигабайт.
А замораживать на несколько дней тестирования релиза основную ветку разработки - не лучшая идея. Особенно, если релизный цикл всего 1-2 недели.
И Вы так и не ответили на вопрос об отказе от препроцессинга.
CREATE OR REPLACE FUNCTION или CREATE OR REPLACE PROCEDURE. А как еще?
Это понятно, что такие скрипты. Как вы эти скрипты на прод зальёте ?
Руками или через какой-то софт ? ( я намекаю, что софт кушает как раз миграции ( файлы со скриптами) для БД )
В ветке контура не только интеграционное и нагрузочное, но даже просто полноценное тестирование произвести в общем случае невозможно. Для контуров БД обрезается многократно до скромных не более чем сотни гигабайт.
Никто вам не мешает использовать,, условно ZFS и быстро откатываться назад
А замораживать на несколько дней тестирования релиза основную ветку разработки - не лучшая идея. Особенно, если релизный цикл всего 1-2 недели.
Вводите промежуточный сервер
Я не понимаю, при чём тут рассмотрение стейджей ревью, тестирования и т.д. к теме этой статьи ( я напомню, что статья про хранение скриптов для версионирования БД )
И Вы так и не ответили на вопрос об отказе от препроцессинга.
Мб я тупой, но не могли бы вы по другому поставить вопрос ?
Руками или через какой-то софт ? ( я намекаю, что софт кушает как раз миграции ( файлы со скриптами) для БД )
Почему руками? При сборке после препроцессинга получается уже готовый скрипт, который можно заливать напрямую psql. Вот взял первый попавшийся проект. Результирующий скрипт 16 МБ, через psql, если не активизируются миграции (в моем понимании), он заливается за 25-30 секунд. В этом проекте полторы тысячи таблиц и две тысячи процедур и функций. В чем проблемы?
Никто вам не мешает использовать,, условно ZFS и быстро откатываться назад
Вообще не понял о чем Вы. Элементарные создания новых индексов на таблице в терабайт-другой сожрет на множестве контуров десятки если не сотни терабайт.
статья про хранение скриптов для версионирования БД
Вот это меня и удивляет, так как хранение и версионирование решается средствами GIT. Зачем БД знать что-либо, кроме тега в ветке?
Мб я тупой, но не могли бы вы по другому поставить вопрос ?
Попробую примером.
Вот пример части кода процедуры до препроцессинга
SVC_LOG_STEP_START(0, proc.step_startup_time, 'Starting parmeters parsing')
BEGIN
SVC_LOG_PARAMETER_TABLE(SVC_PARAMETER_loaded_legs, loaded_legs)
SVC_LOG_PARAMETER_DATE(SVC_PARAMETER_on_date, on_date)
SVC_LOG_PARAMETER_INT(SVC_PARAMETER_optimal_rows, optimal_rows)
SVC_LOG_PARAMETER_NUM(SVC_PARAMETER_speed_dev, speed_dev)
SVC_LOG_SQL_EXCEPTION_LOG_AND_RAISE_MAIN
SVC_LOG_STEP_FINISH(0, proc.step_startup_time, 'Finished parmeters parsing')
А вот после
proc.step_startup_time=clock_timestamp();
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, LogMessageId, LogMessage)
VALUES ('SVC_PrincipalDirectionOptimizer_sp', 0, session_id, 14, 'Starting parmeters parsing');
COMMIT AND CHAIN;
BEGIN
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, ParameterId, LogMessageId, LogMessage, LogTableContent)
SELECT 'SVC_PrincipalDirectionOptimizer_sp', 0, proc.session_id, P.ParameterId, 13, P.Name, loaded_legs::text
FROM dev.SVC_Parameters P
WHERE P.ParameterId=8;
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, ParameterId, LogMessageId, LogMessage, LogTextData, LogDateData)
SELECT 'SVC_PrincipalDirectionOptimizer_sp', 0, proc.session_id, P.ParameterId, 13, P.Name, on_date, on_date
FROM dev.SVC_Parameters P
WHERE P.ParameterId=16;
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, ParameterId, LogMessageId, LogMessage, LogTextData, LogIntData)
SELECT 'SVC_PrincipalDirectionOptimizer_sp', 0, proc.session_id, P.ParameterId, 13, P.Name, optimal_rows, optimal_rows
FROM dev.SVC_Parameters P
WHERE P.ParameterId=24;
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, ParameterId, LogMessageId, LogMessage, LogTextData, LogNumData)
SELECT 'SVC_PrincipalDirectionOptimizer_sp', 0, proc.session_id, P.ParameterId, 13, P.Name, speed_dev, speed_dev
FROM dev.SVC_Parameters P
WHERE P.ParameterId=48;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
proc.sql_state=RETURNED_SQLSTATE,
proc.sql_column=COLUMN_NAME,
proc.sql_constraint=CONSTRAINT_NAME,
proc.sql_datatype=PG_DATATYPE_NAME,
proc.sql_table=TABLE_NAME,
proc.sql_schema=SCHEMA_NAME,
proc.sql_message=MESSAGE_TEXT,
proc.sql_detail=PG_EXCEPTION_DETAIL,
proc.sql_hint=PG_EXCEPTION_HINT,
proc.sql_context=PG_EXCEPTION_CONTEXT;
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, LogMessage, SQLState, SQLColumn, SQLConstraint, SQLDataType, SQLTable, SQLSchema, SQLMessage, SQLDetail, SQLHint, SQLContext)
VALUES ('SVC_PrincipalDirectionOptimizer_sp', 32, session_id, 'SQL error '||proc.sql_message, proc.sql_state, proc.sql_column, proc.sql_constraint, proc.sql_datatype, proc.sql_table, proc.sql_schema, proc.sql_message, proc.sql_detail, proc.sql_hint, proc.sql_context);
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, IsFinish, LogMessageId, LogMessage, LogIntData, LogIntervalms)
SELECT I.SourceName, L.LogLevel, session_id, TRUE, 2,
CASE WHEN L.LogLevel>=12 THEN 'Services finished with errors'
WHEN L.LogLevel>4 THEN 'Services finished with warnings'
ELSE 'Services finished succesfully' END,
I.LogIntData, (EXTRACT(EPOCH FROM clock_timestamp()-I.LogTime)*1000)
FROM dev.SVC_ExecutionLog I
CROSS JOIN LATERAL (
SELECT MAX(LogLevel) AS LogLevel
FROM dev.SVC_ExecutionLog L
WHERE L.SessionID=session_id ) L
WHERE I.IsStart AND I.SessionId=session_id;
COMMIT;
RAISE WARNING 'SQL error %', sql_message;
RETURN;
END;
INSERT INTO dev.SVC_ExecutionLog (SourceName, LogLevel, SessionId, LogMessageId, LogMessage, LogIntervalms)
VALUES ('SVC_PrincipalDirectionOptimizer_sp', 0, session_id, 15, 'Finished parmeters parsing', ROUND(EXTRACT(EPOCH FROM clock_timestamp()-proc.step_startup_time)*1000));
COMMIT AND CHAIN;
CREATE OR REPLACE FUNCTION или CREATE OR REPLACE PROCEDURE. А как еще?
В PostgreSQL не сработает, если типы / количество параметров у функции / процедуры поменялись. Придётся дропать. А это тоже боль, так как зачастую другие объекты от них зависят (другие функции / процедуры, представления), поэтому дропать надо каскадно, а потом как-то восстанавливать дропнутое обратно.
В PostgreSQL не сработает, если типы / количество параметров у функции / процедуры поменялись. Придётся дропать.
Вообще то наоборот, чаще это используется специально именно в целях полиморфизма. Если же действительно нужно дропнуть, то даже если разработчик забыл это сделать, то ни к каким неприятным последствиям это не приводит. Зачистится потом при рефакторинге.
А это тоже боль, так как зачастую другие объекты от них зависят (другие функции / процедуры, представления), поэтому дропать надо каскадно, а потом как-то восстанавливать дропнутое обратно.
Навскидку могу вспомнить только зависимость триггера от его функции. Но в этом случае нет ни параметров ни их типов, а значит и нет проблем с CREATE OR REPLACE.
Можете более развернуто объяснить о каких зависимостях Вы ведете речь?
P.S. Ах да, еще VIEW. Но там аналогичная ситуация, так как тип и количество параметров используемых функций не изменить, не пересоздав VIEW.
Представления зависят, при дропе надо будет пересоздавать вьюхи
Я вспомнил и указал выше. Но если VIEW не меняется, то не меняется так же тип и количество параметров функций, которые она вызывает.
Можете более развернуто объяснить о каких зависимостях Вы ведете речь?
Вроде понятно написал, что от функции может зависеть другая функция (внутри вызывает нашу) и/или представление (внутри представления стоит вызов функции). Меняете тип параметра или даже просто добавляете новый — это создаст вам новую функцию, а не заменит старую. Для именно замены старую придётся удалять через drop function, а если она используется в представлении, то просто так PostgreSQl вам удалить её не даст. Удалите сначала представление, а потом свою функцию. И всё это может по рекурсии как снежный ком накапливаться. Ну или cascade пропишите у дропа, но тогда вы даже не узнаете, сколько системы у вас улетит в трубу при дропе.
Вроде понятно написал, что от функции может зависеть другая функция (внутри вызывает нашу)
Впервые слышу о такой зависимости. С перепугу даже проверил:
CREATE OR REPLACE FUNCTION _tst_0(i integer)
RETURNS integer AS $func$ <<func>> BEGIN
RETURN _tst_1(i);
END; $func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION _tst_1(i integer)
RETURNS integer AS $func$ <<func>> BEGIN
RETURN i + 1;
END; $func$ LANGUAGE plpgsql;
DROP FUNCTION IF EXISTS _tst_1;
DROP FUNCTION IF EXISTS _tst_0;Пожалуйста, приведите пример такой зависимости.
Меняете тип параметра или даже просто добавляете новый — это создаст вам новую функцию, а не заменит старую.
Это и есть полиморфизм, который широко используется.
Для именно замены старую придётся удалять через
drop function, а если она используется в представлении, то просто так PostgreSQl вам удалить её не даст.
Но в этом случае все равно необходимо удалять и заново создавать VIEW, так как она будет вызывать функцию с другим набором параметров.
А вот удалять старую функцию совершенно не обязательно. Возможно её использует в запросах какой-то сервис или интеграция. Проще оставить её до рефакторинга. Наличие лишних функций на производительность системы влияет на уровне статистической погрешности.
И всё это может по рекурсии как снежный ком накапливаться.
Только если нет архитектора на проекте. Я и так создавать VIEW позволяю очень неохотно и только ради внешних систем, которые не могут при сборке подхватить SQL запрос из отдельного файла. А уж PR с VIEW на VIEW точно не пропущу.
Пожалуйста, приведите пример такой зависимости.
К сожалению, иногда PostgreSQL ругается на функции, используемые в других функциях, а иногда нет. Я так и не смог понять, при каких условиях это происходит, но крови это мне попило изрядно, поэтому мне проще считать, что функция, использующая другую функцию, жёстко от неё зависит. Но там и код функций много сложнее вашего синтетического примера. Но ОК, допустим, это меня память подводит и PostgreSQL на это не ругается. Всё равно остаются представления.
Но в этом случае все равно необходимо удалять и заново создавать VIEW, так как она будет вызывать функцию с другим набором параметров.
Не будет. Я хочу добавить в функцию новый параметр со значением по умолчанию (флаг включения отладочного вывода, к примеру), на вызов функции в существующих местах это не влияет абсолютно никак. Но я не могу этого сделать. Создав новую функцию с добавочным дефолтным параметром, я сразу же нарушу вызовы во всех местах, так они вызываемая функция станет не уникальной. В общем, после Oracle всё это выглядит прямо скажем, не очень.
А вот удалять старую функцию совершенно не обязательно.
Функция внутренняя, извне её никто не вызывает. Она сугубо наша. Не вижу ни одной причины, почему я её должен оставлять, если она никем не используется, а вот пониманию мешает весьма сильно (а ещё её по ошибке может кто-то из разработчиков начать использовать вместо правильной).
А уж PR с VIEW на VIEW точно не пропущу.
В нашей системе представление на представлении — норма жизни, поэтому без этого никак.
К сожалению, иногда PostgreSQL ругается на функции, используемые в других функциях, а иногда нет.
Приведите пример. Насколько я знаю, PostgreSQL компилирует функции и процедуры только при их вызове. При создании никакой компиляции не производится. Только простейшая проверка синтаксиса. Например, молча создается такая функция:
CREATE OR REPLACE FUNCTION _tst_0(i integer)
RETURNS integer AS $func$ <<func>> BEGIN
SELECT some_field FROM this_table_not_exist;
END; $func$ LANGUAGE plpgsql;Я хочу добавить в функцию новый параметр со значением по умолчанию (флаг включения отладочного вывода, к примеру), на вызов функции в существующих местах это не влияет абсолютно никак.
Это очень опасно из-за полиморфизма. Совершенно в неожиданный момент можете получить SQL Error [42725]: ERROR: function ... is not unique. В документации сказано "If there are two or more such functions in the same schema with identical parameter types in the non-defaulted positions (which is possible if they have different sets of defaultable parameters), the system will not be able to determine which to prefer". Во избежании таких проблем вызов функций без указаний всех параметров в проектах категорически запрещается.
Функция внутренняя, извне её никто не вызывает.
В таких редких и частных случаях никто не мешает её действительно убить в скрипте её же создания. К тому же, раз она внутренняя, то и во VIEW для внешней системы она не используется. Но я с такими случаями сталкиваюсь чрезвычайно редко и, преимущественно, все же с хранимыми процедурами, а не функциями.
её по ошибке может кто-то из разработчиков начать использовать вместо правильной
Это сразу же вылезет при контроле метаданных созданием пустой БД и последующей проверкой линтером.
В нашей системе представление на представлении — норма жизни
Сколько раз я таким не сталкивался, всегда это приводило к весьма продолжительным и тяжелым оптимизациям. Проблема в том, что когда используются VIEW, разобраться в плане запроса становится затруднительно. А если еще VIEW на VIEW - вообще невозможно без ручного их разворачивания. При этом разработчик, когда пишет запрос, даже не видит, что VIEW может соединять целый ряд не нужных в данном случае таблиц. А лишние INNER JOIN ведут к заметной деградации производительности из-за излишних проверок на наличие записей в связываемых таблицах, даже если связывание выполняется по внешнему ключу. Да и компиляция запроса с VIEW всегда дольше, чем без него.
без этого никак.
Почему никак? Просто не утверждаются PR с запросами использующими VIEW. Пользуйтесь макроопределениями вместо них. Тогда в диагностике сразу будет виден весь проблемный запрос и его не надо будет вручную разворачивать для анализа.
Не вижу ни одного преимущества в использовании VIEW, кроме как для внешних систем.
Это очень опасно из-за полиморфизма. Совершенно в неожиданный момент можете получить SQL Error [42725]: ERROR: function ... is not unique.
Так я же об этом как раз и говорю. Я хотел просто добавить параметр в функцию, а вместо этого молча создалась новая (несмотря на фразу or replace — то есть, я явно выразил намерение заменить функцию). И я об этом узнаю только тогда, когда где-то запрос упадёт. Очень не интуитивно это.
Во избежании таких проблем вызов функций без указаний всех параметров в проектах категорически запрещается.
Это требование делает параметры по умолчанию совершенно бесполезными.
В таких редких и частных случаях никто не мешает её действительно убить в скрипте её же создания.
Я же говорю, что мешает. PostgreSQL не даст мне просто сделать drop function и создать новую, если она в представлении используется. Придётся сначала запомнить код представления, сделать drop view, сделать drop function, сделать create function, сделать create view по сохранённому ранее DDL. Причём сохранить DDL надо всех объектов рекурсивно, так как это представление может использоваться в другом представлении, а то — в следующем. Это решаемая задача, но огромный геморрой. А удалять функцию каскадно нельзя, потому что кто потом восстановит удалённые по каскаду представления?
Пользуйтесь макроопределениями вместо них.
Что ещё за макроопределения, впервые слышу о таком?
Не вижу ни одного преимущества в использовании VIEW, кроме как для внешних систем.
Дело в том, что система потихоньку мигрирует с Oracle, а там представления играют существенную роль. И нельзя просто так от них избавиться.
то есть, я явно выразил намерение заменить функцию
Ну так функция уйдёт под замену, если выполняется 3 условия:
Совпадает название
Совпадают типы входных параметров
Совпадают типы выходных параметров
В остальных случаях будет создание новой функции
Причём сохранить DDL надо всех объектов рекурсивно, так как это представление может использоваться в другом представлении, а то — в следующем. Это решаемая задача, но огромный геморрой.
Ну так а для чего предлагается хранение в гите ?
Как раз для того, чтобы вы точно видели, что у вас меняется в структуре
Это требование делает параметры по умолчанию совершенно бесполезными.
Использовать одновременно полиморфизм и параметры по умолчанию при вызовах с позиционным указанием значений параметров проблематично из-за ограничений синтаксиса PostgreSQL. Но так как полиморфизм дает явно больше преимуществ, то выбираем его.
Придётся сначала запомнить код представления, сделать
drop view, сделатьdrop function, сделатьcreate function, сделатьcreate viewпо сохранённому ранее DDL.
Исходим из того, что мы отказались от параметров по умолчанию в пользу полиморфизма. Тогда, если можно было сделать CREATE OR ALTER FUNCTION, то не зачем было убивать VIEW. В противном случае убивать VIEW всё равно необходимо, а после пересоздания функции создать VIEW "по сохранённому ранее DDL" не получится, так как функция будет уже другой. Как видим, если отказаться от параметров по умолчанию, получаем не только полиморфизм, но и одновременно решение и этой проблемы.
Что ещё за макроопределения, впервые слышу о таком?
Любой исходный код в процессе сборки перед разворачиванием может быть обработан макропроцессором. Я даже тут в комментариях приводил простейший пример. Одновременно это решает проблему формирования единого скрипта разворачивания БД для psql.
Мы пользуемся C препроцессором. Я бы предпочел, конечно, m4, но приходится учитывать уровень знаний разработчиков, в большинстве своем с m4 не знакомых.
Дело в том, что система потихоньку мигрирует с Oracle, а там представления играют существенную роль. И нельзя просто так от них избавиться.
Вам всё равно потребуется от большинства из них избавляться при оптимизации производительности. Я же писал об этом выше. А в PostgreSQL ситуация только усугубляется его менее развитым оптимизатором запросов и не умением игнорировать неиспользуемые таблицы, связанные INNER JOIN по внешнему ключу.
Попробую показать на примере, к чему это приводит:
CREATE TABLE _tmp_test0 (
id int PRIMARY KEY,
val varchar NULL
);
CREATE TABLE _tmp_test1 (
id int PRIMARY KEY,
ref_id int CONSTRAINT FK_tmp_test1_ref_id REFERENCES _tmp_test0(id),
val varchar NULL
);
CREATE VIEW _tmp_test_vw AS
SELECT T.id, T.ref_id, T.val, R.val AS r_val
FROM _tmp_test1 T
JOIN _tmp_test0 R ON R.id=T.ref_id;
INSERT INTO _tmp_test0 (id, val)
SELECT G.n, (G.n/100)::text
FROM generate_series(0,9999) G(n);
INSERT INTO _tmp_test1 (id, ref_id, val)
SELECT G.n, G.n/100, (G.n/1000)::text
FROM generate_series(0,999999) G(n);
Теперь делаем запрос из VIEW, в SELECT, списке которого нет поля r_val:
EXPLAIN ANALYZE
SELECT val
FROM _tmp_test_vw
WHERE id BETWEEN 100000 AND 500000;
Hash Join (cost=308.71..12721.15 rows=399695 width=3) (actual time=1.592..104.212 rows=400001 loops=1)
Hash Cond: (t.ref_id = r.id)
-> Index Scan using _tmp_test1_pkey on _tmp_test1 t (cost=0.42..11363.23 rows=399695 width=7) (actual time=0.021..50.201 rows=400001 loops=1)
Index Cond: ((id >= 100000) AND (id <= 500000))
-> Hash (cost=183.28..183.28 rows=10000 width=4) (actual time=1.553..1.555 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
-> Index Only Scan using _tmp_test0_pkey on _tmp_test0 r (cost=0.29..183.28 rows=10000 width=4) (actual time=0.010..0.822 rows=10000 loops=1)
Heap Fetches: 0
Planning Time: 0.208 ms
Execution Time: 114.185 msКак видим, он всё равно полез в _tmp_test0. А если сделать запрос непосредственно по _tmp_test1, то этого ожидаемо не происходит:
EXPLAIN ANALYZE
SELECT val
FROM _tmp_test0
WHERE id BETWEEN 100000 AND 500000;
Index Scan using _tmp_test0_pkey on _tmp_test0 (cost=0.29..2.50 rows=1 width=2) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((id >= 100000) AND (id <= 500000))
Planning Time: 0.117 ms
Execution Time: 0.018 msИ эти три порядка разницы в производительности на продуктивной системе ощущаются очень болезненно. Контролировать это на уровне PR сложно и трудоёмко, а сами разработчики на такое редко обращают внимание из-за обманчивого "Index Only Scan". Отсюда и моя позиция категорического запрета на VIEW внутреннего использования.
К сожалению, иногда PostgreSQL ругается на функции, используемые в других функциях, а иногда нет.
Я могу сейчас ошибаться.
Но функции на plpgsql у вас кэшируются при первом выполнении ( строится план и кэшируется в локальной памяти процесса )
Так вот, как мне кажется, если удалили функцию после кэширования и создали новую - можно получить ошибку ( хотя я такого не встречал, если честно )
в любом случае, если во время разработки вы дропните что-то - система всё равно делает дамп и показывает на файлах, что случилось ( т.е. в гите в МР вы увидите, что у вас удалилось в итоге )
Мы пока ещё не дошли до теста удаления вью и 100% не могу сказать, на сколько система сможет полностью восстановиться ( пересоздать вьюшки )
ЗЫ добавлю, что мы не так часто используем вью. Например, если клиент не гибкий ( например даталенз ).
вы не верно интерпретировали слово "миграция"
Вы сами всех запутали и себя в первую очередь. Миграциями называется только то, что вы называете изобретенным термином "добивочные миграции".
Вариантов организации исходников БД и построения Continuous Delivery два:
вы храните только миграции (alter table add, alter trigger) и конечное состояние понять можно только прогнав все миграции что есть; это удобно при работе через ORM, когда нет хранимок и триггеров, а таблицы определены классами в ООП
вы храните определения объектов, а скрипт миграции для деплоя генерируется утилитой вроде liquibase налету и нигде не хранится; этот вариант удобен, когда у вас полноценная разработка на стороне БД: в GIT лежат финальные версии определений таблиц и хранимок, при возникновении конфликта по изменениям, он, во-первых, очевиден, во-вторых решается штатным образом как с нормальными исходниками
Если утилита не умеет в alter table, то вариантов нет и нужно для таблиц (дополнительно к определению) создавать и хранить файлы миграций. Для функций у вас хранятся не миграции, а определения (definition), миграция возникает налету, когда liquibase понимает, что файл с CREATE FUNCTION изменился и генерирует ALTER FUNCTION.
На самом деле есть третий вариант, когда в отдельные скрипты миграции выделяются лишь трансформации данных из одного множества таблиц в другое. А остальные миграции ведутся в исходном файле таблицы, начинающегося именно с определения таблицы как CREATE TABLE IF NOT EXISTS. При этом допустимо поддерживать и финальное описание таблицы так как миграции вида ALTER TABLE IF EXISTS SQL_DB_INSTANCE.OBJECT_NAME ADD COLUMN IF NOT EXISTS отрабатывают только по необходимости, а более сложные проверки можно заранее упростить макросами и функциями/процедурами.
liquibase понимает, что файл с CREATE FUNCTION изменился и генерирует ALTER FUNCTION
А кто мешает написать сразу CREATE OR REPLACE FUNCTION? PostgreSQL все равно не будет модифицировать функцию, если код её не изменился. Но эта проверка произойдет намного быстрее, чем средствами liquibase.
В добавок к этому есть версионность миграций И типы файлов - mutable (R) / immutable (V).
Какие вы рекомендуете валидационные проверки запускать в мерж регвесте, без выполнения которых разработчик не сможет закрыть мерж регвест?
Уточните, пожалуйста, вы имеете в виду проверку именно правильности выполнения миграций или вообще проверки кода, не связанные с тематикой статьи ?
Конечно про проверки правильность миграции, кода, чтобы отловить ошибки, которые могут возникнуть при обновлении бд
Пока-что для проверки можно взять функционал из раздела генерации "добивочных" миграций, а именно ( первые 2 пункта взяты):
1) Возьмём клон dev-базы
2) Накатим туда весь МР
3.1) Меньше рисков: сравним с фич-БД, путём сравнивания именно генерации файлов ( обработанный выхлоп pg_dump)
3.2) Альтернативный вариант, но больше рисков: сравнение сделать не через структуру файлов, а через диф-утилиту
Конечно, надо понимать, что ни первый, ни второй вариант не спасут от ошибки, если в результате МР мы создаём уникальный индекс по уже существующему полю, а там есть дубли.
Тут могут вызникнуть некоторые проблемы, которые будут зависеть от того, каким инструментом заливки пользоваться
Вот так 7 лет разрабатываешь pgCodeKeeper, а тебе говорят, что у него скромный функционал)
Альтернативный вариант к подходу хранения SQL-кода в репозитории и его разработке