Русский
Русский
English
Статистика
Реклама

Migrations

Из песочницы Миграции данных в Ruby On Rails

05.08.2020 20:10:52 | Автор: admin

img


TL;DR Пожалуйста, выносите код миграции данных в Rake-задачи или пользуйтесь полноценными гемами в стиле миграций схемы. Покрывайте тестами эту логику.


Я работаю бэкенд-разработчиком в FunBox. В ряде проектов мы пишем бэкенд на Ruby On Rails. Мы стремимся выстраивать адекватные процессы разработки, поэтому, столкнувшись с проблемой, стараемся её осмыслить и выработать методические рекомендации. Так произошло и с проблемой миграции данных. Однажды я сделал миграцию данных в отдельной Rake-задаче, покрытой тестами, и у команды возник вопрос: Почему не в миграции схемы? Я спросил во внутреннем чате разработчиков, и, к моему большому удивлению, мнения разделились. Стало понятно, что вопрос неоднозначный и достоин вдумчивого анализа и статьи. Программа-максимум по целям на статью для меня будет выполнена, когда ссылку на этот текст кто-нибудь приведёт на ревью кода в ответ на вопрос, зачем конкретная миграция данных вынесена или, наоборот, не вынесена из миграции схемы.


Лирическое отступление


Я взялся писать эту статью, чтобы снизить боль и увеличить продуктивность командной работы. В начале я надеялся отыскать жёсткие доказательные доводы о вреде злоупотребления миграциями схемы для миграций данных. Параллельно с этим я читал книгу Николая Бердяева Смысл творчества. Опыт оправдания человека. Из неё я почерпнул понятие соборный дух.


В мире программирования и IT преимущественно царит желание людей придать всей деятельности наукообразность с подведением подо всё доказательной базы. Когда я приобщился к миру Ruby, я почувствовал что-то совсем другое. Юкихиро Матсумото создал язык, чтобы облегчить общение людей через код, и это породило особое сообщество человеколюбивых людей. Мне кажется, что в этом сообществе ощущается именно соборный дух: все разделяют похожие ценности, имеют схожие интуиции и относятся друг к другу с любовью в евангельском смысле слова, а значит, не нуждаются в доказательствах, так как, по Бердяеву, доказательства нужны при разных враждебных интуициях.


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


Проблемы смешивания миграций даннных и схемы


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


Проблемы эксплуатации


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


Длинные транзакции по миграции данных повышают вероятность возникновения deadlocks в БД.


Для предотвращения обозначенных проблем эксплуатации, на этапе разработки можно использовать инструменты статического анализа кода, например, гемы Zero Downtime Migrations и Strong Migrations.


Проблемы сопровождения


Нарушение принципа единой ответственности


Миграции схемы это DSL (Domain Specific Language) на Ruby для DDL-конструкций языка SQL и обвязки над ними. Пока мы пользуемся DSL, разумное качество гарантируется ручной проверкой того, что миграция успешно выполняется в прямом и обратном направлении. Если мы ошибёмся в смысле миграции, то не сможем продолжить разработку и сразу исправим её.


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


Нет тестов (по крайней мере адекватных, дешевых)


Автор статьи Ruby On Rails Data Migration ради тестирования миграций данных накатывает предыдущие миграции и проверяет, что целевая миграция выполнит нужные изменения данных. В большом приложении, это будет выполняться чудовищно долго и повысит когнитивную нагрузку на команду необходимостью читать и писать подобные тесты. Нежелательно иметь логику миграции данных внутри кода Rails-миграции, где её так сложно протестировать. Где эту логику расположить я расскажу в разделе о решениях.


Проблемы сопровождения при использовании классов моделей в миграции


Для логики миграции данных очень удобно вместо SQL использовать код моделей и средства языка ORM ActiveRecord.


Но это может приводить к следующим проблемам:


  1. Класс модели может быть переименован или удалён. Тогда будет получена ошибка несуществующей константы.
  2. В модели могут быть добавлены валидации, которые не позволят выполнить изменения.
  3. В модели могут присутствовать callbacks с побочными эффектами, на которые автор кода миграции не рассчитывает.

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


Процитирую пример такого костыль из официального руководства Rails:


    # db/migrate/20100513121110_add_flag_to_product.rb    class AddFlagToProduct < ActiveRecord::Migration      class Product < ActiveRecord::Base      end      def change        add_column :products, :flag, :boolean        Product.reset_column_information        Product.all.each do |product|          product.update_attributes!(:flag => false)        end      end    end

Лично мне не хочется иметь в кодовой базе подобное.


Кстати, вместо each стоит использовать find_each c batch-обработкой.


Любопытно было заметить, что, начиная с версии 4.2 этот пример из руководства совсем убрали.


Проблемы сопровождения при использовании SQL в миграции


Если, стремясь уйти от использования моделей в миграциях, мы хотим использовать напрямую команды SQL, то мы сталкиваемся со следующими недостатками такого подхода:


  1. Логика выражается сложнее, чем через код модели. Сложнее, ибо менее лаконично, на более низком уровне абстракции, на другом языке (SQL), которым мы пользуемся сравнительно редко.
  2. Если есть JOIN-ы, это уже серьёзное дублирование знаний, выраженных в связях моделей.
  3. При длительной обработке невозможно отслеживать прогресс и невозможно понять, идёт ли ещё обработка или уже случился deadlock.

Сомнительные проблемы


В статье Thoughtbot приводится такое соображение: миграции схемы данных не являются бизнес-логикой, поэтому должна быть возможность в любой момент их выбросить и загружать схему целиком через DDL. Это позволит быстрее готовить окружение и прогонять тесты на CI. При удалении миграций из проекта содержащаяся в них логика миграции данных теряется.


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


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


Допустим, данные есть и мы выбрасываем старые миграции. Но старые миграции данных уже привели базу в нужное состояние, и они всё равно нерелевантны.


Вынашивая замысел статьи, на этот довод я возлагал большие надежды, но увы. Напишите, пожалуйста, в комментариях, что я могу здесь упускать.


Исключения, когда допустимо делать миграции данных в миграциях схемы


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


Например, превращение nullable-поля в поле со значением по умолчанию или наоборот.


Так как миграции должны быть откатываемыми, мы должны обеспечить обратное преобразование и без обновления значения никак не обойтись.


Запрос будет тривиальным, вида:


    UPDATE table SET field = 'f' WHERE field IS NULL

Вся миграция может выглядеть так:


    class ClientDemandsMakeApprovedNullable < ActiveRecord::Migration      def up        change_column_null :client_demands, :approved, true        change_column_default :client_demands, :approved, nil      end      def down        execute("UPDATE client_demands SET approved = 'f' WHERE approved IS NULL")        change_column_null :client_demands, :approved, false        change_column_default :client_demands, :approved, false      end    end

Вообще говоря, при большом объёме данных в таблице так делать не стоит и нужно прибегать к более изощренным приёмам. Например, не выполнять миграцию на проде, а делать все изменения руками и потом подменять файл миграции и версию в БД. Подробно этот приём описан в статье Dan Mayer Managing DB Schema & Data Changes в разделе Modifying Large Tables.


Возможные решения


Отказ от решения ввиду мизерного объёма приложения или данных


До определённого момента роста можно закрывать глаза на эту проблему и смешивать миграции схемы и данных. Эти старые грехи, никак вам не навредят. Бросить можно в любой момент и даже поступать по ситуации. Подскажите мне, пожалуйста, в комментариях, если я заблуждаюсь.


Но если приложение, база или команда разрастаются, то лучше прийти к более строгой дисциплине ради снижения потерь.


Вынос миграций данных в Rake-задачи


Самое доступное и очень действенное средство создавать для миграций данных Rake-задачи. Вот их удобно покрывать тестами непосредственно. У меня в процессе написания теста миграции данных часто случаются озарения по поводу требований и удаётся решить потенциальные бизнес-проблемы.


Недавно на проекте была не покрытая тестом Rake-задача миграции данных. На ревью кода никто не заметил, что вместо добавления элемента в массив производилась перезапись всего массива. Опечатка привела к повреждению данных и необходимости восстановления данных из бэкапа в ручном выборочном режиме. В процессе написания теста такая логическая опечатка не могла бы быть допущена. Так что тесты наши великие помощники в мыслительном процессе.


Процитирую пример, использующий все удобства, которые даёт Rake, из статьи Thoughtbot:


    # lib/tasks/temporary/users.rake    namespace :users do      desc "Actualize achievements counter cache"      task actualize_achievements_counter_cache: :environment do        # Cкоуп (ActiveRelation) пользователей с достижениями        users = User.with_achievements        # Вывод количества обрабатываемых записей        puts "Going to update #{users.count} users"        # Транзакция, в данном случае, не обязательна        # но чаще она нужна. Пусть будет для примера        ActiveRecord::Base.transaction do          # Batch-обработка с помощью find_each          users.find_each do |user|                    # Вызов идемпотентной актуализации кеша количества            user.actualize_achievements_counter_cache!            # Отслеживание прогресса            print "."          end        end        puts "Done!"      end    end

Я заменил each на find_each, чтобы обработка шла порциями и не загружала в память всю выборку. Это обязательная практика для обработки больших выборок без memory bloats. Подробнее в статье от Akshay Mohite.


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


Вынос миграций данных в отдельные внутренние классы внутри миграции


Mark Qualie предлагает внутри кода миграций схемы добавлять определение вложенного класса с методом up, определяющим логику миграции данных. Таким образом достигается локальность знаний об изменении схемы и связанных изменениях в данных. Вот пример кода из статьи:


    class AddLastSmiledAtColumnToUsers < ActiveRecord::Migration[5.1]      def change        add_column :users, :last_smiled_at, :datetime        add_index :users, :last_smiled_at      end      class Data        def up          User.all.find_in_batches(batch_size: 250).each do |group|            ActiveRecord::Base.transaction do              group.each do |user|                user.last_smiled_at = user.smiles.last.created_at                user.save if user.changed?              end            end          end        end      end    end

Выполнять эту логику автор предлагает вот таким образом:


    Dir.glob("#{Rails.root}/db/migrate/*.rb").each { |file| require file }    AddLastSmiledAtColumnToUsers::Data.new.up

Причём данный код автор предлагает поместить в асинхронный Job, добавив логирование и отслеживание выполненных миграций наподобие хранения в БД версии миграций схемы.


Использование полноценных гемов для миграций данных в стиле миграций схемы


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


Подобных гемов оказалось достаточно много, но суперпопулярных среди них нет. Видимо, потому что масштаб проблемы мало у кого доходит до нужного размера.


У гема data-migrate наибольшее количество звездочек (> 670), ссылок из статей, а также самый ухоженный Readme. Он работает только с Rails 5+.


Ещё два гема с подобным опытом, но поддержкой Rails 4+:



Название последнего особенно примечательно. Оно кричит о противопоставлении миграций схемы и миграций НЕ схемы.


Аудит кода всех этих гемов я не проводил, ибо на проекте моего масштаба хватает подхода с Rake-задачами. Но их обнаружение было для меня одним из стимулов написать эту статью. Для меня они являются признаком серьёзности проблемы, с которой можно столкнуться при росте приложения.


Все они позволяют сгенерировать класс миграции данных в папке проекта db/data, которая находится рядом с традиционной db/migrate c миграциями схемы:


rails g data_migration add_this_to_that

А потом запускать и проверять статус командами вроде таких:


rake data:migraterake db:migrate:with_datarake db:rollback:with_datarake db:migrate:status:with_data

Хорошей идеей для упрощения тестирования будет выделение логики миграции во вложенный класс внутри миграции, как в предыдущем примере.


Заключение


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


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


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


Источники


Подробнее..

Компонентный подход. Компонент SQL миграций на PHP

05.05.2021 18:14:12 | Автор: admin

Не писал на Хабре еще о том, как я пришел к мысли формирования компонентов для своих будущих проектов или текущий вместо прямого написания кода. Если очень коротко сказать про это, то было все примерно так... Много писал разных проектов, придумывал псевдо компоненты и каждый раз натыкался на то, что в одном проекте ужасно удобно это использовать, а в другом ужасно не удобно. Попробовал перенести "удобные" компоненты в проект и стало все еще более не удобно... Короче, руки не из того места, голова слишком амбициозная... Со временем я дошел до другой мысли: "Надо делать репозитории на GitHub с отдельными компонентами, которые не будут иметь зависимость от других компонентов"... Все шло хорошо, но дошел я до того самого компонента, которые хочет работать с другим компонентом... В итоге на помощь пришли интерфейсы с методами. И вот теперь поговорим о компоненте SQL миграций в том ключе, как я его вижу.

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

Возможно я могу ошибаться, но лично я уверен для себя, что миграции необходимы исключительно для SQL операций с базами данных. Для актуализации файлов можно использовать тот же самый git или центральный init файл, как это есть в Yii2.

Идея

Компонент миграций, поскольку он исключительно для SQL операций, будет иметь в основе своем 2 SQL файла. Да, вот тут сейчас будет шквал критики по поводу входного порога и прочего, но скажу сразу, что со временем работы в компании мы от SQLBuilder перешли на чистый SQL, так как это быстрее. К тому же, большинство современных IDE может генерировать DDL для операций с базой данных. И вот представьте, надо вам создать таблицу, наполнить ее данными, а также что-то изменить в другой таблице. С одной стороны вы получаете длинный код билдером, с другой стороны можете использовать SQL чистый в том же билдере, а еще может быть эта ситуация вперемешку... Короче, тут я понял и решил, что в моем компоненте и подходе к программированию в целом будет как можно меньше двойственности. В связи с этим, я решил использовать только SQL код.

Суть работы компонента: консольной командой создается миграция, вы пишете туда код UP и DOWN, консольными командами применяете или откатываете. Все достаточно просто и очевидно. А теперь перейдем к детальному разбору компонента.

Разбор компонента

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

Как пример реализации обертки был реализован классConsoleSqlMigration, которые наследуется отSqlMigrationи переопределяет его методы. Переопределение первоначально вызываетparent::после чего реализует дополнительную логику в выводе сообщений в консоль (терминал).

Для реализации компонента необходимо передать класс реализующий интерфейсDatabaseInterfaceи массив настроек. Обязательными параметрами в настройках являются:

  • schema- схема в базе данных для миграций

  • table- таблица в базе данных для миграций

  • path- путь в файловой структуре для папки с миграциями

Компонент самостоятельно проверяет и создает необходимые (указанные) схемы, таблицы и папки при наличии заранее определенных разрешений (прав). Для корректной работы с базой данных необходимо заранее установить соединение с ней.

Для реализации класса SqlMigration потребуется реализовать его интерфейс. Конечно, класс уже готов, но мало ли вы захотите реализовать как-то по своему. В интерфейсе всего немного методов:

  1. public function up(int $count = 0): array;

  2. public function down(int $count = 0): array;

  3. public function history(int $limit = 0): array;

  4. public function create(string $name): bool;

Эти методы сами за себя говорят. Но на всякий случай, укажу тут их описание из PHPDoc:

/** * Применяет указанное количество миграций * * @param int $count Количество миграция (0 - относительно всех) * * @return array Возвращает список применения и ошибочных миграций. Список может иметь вид: * 1. Случай, когда отсутствуют миграции для выполнения, то возвращается пустой массив * 2. Когда присутствуют миграции для выполнения: * [ *  'success' => [...], *  'error' => [...] * ] * Ключ error добавляется только в случае ошибки выполнения миграции. * * @throws SqlMigrationException */public function up(int $count = 0): array;/** * Отменяет указанное количество миграций * * @param int $count Количество миграция (0 - относительно всех) * * @return array Возвращает список отменных и ошибочных миграций. Список может иметь вид: * 1. Случай, когда отсутствуют миграции для выполнения, то возвращается пустой массив * 2. Когда присутствуют миграции для выполнения: * [ *  'success' => [...], *  'error' => [...] * ] * Ключ error добавляется только в случае ошибки выполнения миграции. * * @throws SqlMigrationException */public function down(int $count = 0): array;/** * Возвращает список сообщений о примененных миграций * * @param int $limit Ограничение длины списка (null - полный список) * * @return array */public function history(int $limit = 0): array;/** * Создает новую миграцию и возвращает сообщение об успешном создании миграции * * @param string $name Название миграции * * @return bool Возвращает true, если миграция была успешно создана. В остальных случаях выкидывает исключение * * @throws RuntimeException|SqlMigrationException */public function create(string $name): bool;

Теперь перейдем непосредственно к классу SqlMigration. Для начала определим константы операций. Это надо будет для того, чтобы в последующих универсальных методах определить точное действие миграции:

/** * Константы для определения типа миграции */public const UP = 'up';public const DOWN = 'down';

Для работы компонента нужен массив его настроек и интерфейс для работы с БД. Для работы с БД будет использоваться мой персональный интерфейс DatabaseInterface. В конструкторе нашего класса мы будем устанавливать зависимости (DI) и проверять корректность переданных настроек:

/** * SqlMigration constructor. * * @param DatabaseInterface $database Компонент работы с базой данных * @param array $settings Массив настроек * * @throws SqlMigrationException */public function __construct(DatabaseInterface $database, array $settings) {$this->database = $database;$this->settings = $settings;foreach (['schema', 'table', 'path'] as $settingsKey) {if (!array_key_exists($settingsKey, $settings)) {throw new SqlMigrationException("Отсутствуют {$settingsKey} настроек.");}}}

Теперь надо создать метод, который будет создавать схему и таблицу если таковые не существуют. Метод вернут bool если схема и таблица миграции была создана успешно и в остальных случаях выкидывает исключение:

/** * Создает схему и таблицу в случае их отсутствия * * @return bool Возвращает true, если схема и таблица миграции была создана успешно. В остальных случаях выкидывает * исключение * * @throws SqlMigrationException */public function initSchemaAndTable(): bool {$schemaSql = <<<SQLCREATE SCHEMA IF NOT EXISTS {$this->settings['schema']};SQL;if (!$this->database->execute($schemaSql)) {throw new SqlMigrationException('Ошибка создания схемы миграции');}$tableSql = <<<SQLCREATE TABLE IF NOT EXISTS {$this->settings['schema']}.{$this->settings['table']} ("name" varchar(180) COLLATE "default" NOT NULL,apply_time int4,CONSTRAINT {$this->settings['table']}_pk PRIMARY KEY ("name")) WITH (OIDS=FALSE)SQL;if (!$this->database->execute($tableSql)) {throw new SqlMigrationException('Ошибка создания таблицы миграции');}return true;}

Теперь надо подготовить методы для работы с миграциями. Начнем с генерации и валидации имени миграции (папки миграции):

/** * Проверяет имя миграции на корректность * * @param string $name Название миграции * * @throws SqlMigrationException */protected function validateName(string $name): void {if (!preg_match('/^[\w]+$/', $name)) {throw new SqlMigrationException('Имя миграции должно содержать только буквы, цифры и символы подчеркивания.');}}/** * Создает имя миграции по шаблону: m{дата в формате Ymd_His}_name * * @param string $name Название миграции * * @return string */protected function generateName(string $name): string {return 'm' . gmdate('Ymd_His') . "_{$name}";}

Следующим этапом будет создание самой миграции, а именно папки и файлов в ней. Папка будет иметь определенный формат имени: m_дата_пользовательское_имя - а проверка имени файла осуществляется на буквы, цифры и символы подчеркивания:

/** * @inheritDoc * * @throws RuntimeException|SqlMigrationException */public function create(string $name): bool {$this->validateName($name);$migrationMame = $this->generateName($name);$path = "{$this->settings['path']}/{$migrationMame}";if (!mkdir($path, 0775, true) && !is_dir($path)) {throw new RuntimeException("Ошибка создания директории. Директория {$path}не была создана");}if (file_put_contents($path . '/up.sql', '') === false) {throw new RuntimeException("Ошибка создания файла миграции {$path}/up.sql");}if (!file_put_contents($path . '/down.sql', '') === false) {throw new RuntimeException("Ошибка создания файла миграции {$path}/down.sql");}return true;}

Поскольку мы работаем со списком миграций, то интересно было бы получить вообще все миграции, которые были применены. Этот метод поможет нам в дальнейшем определени не примененных миграций:

/** * Возвращает список примененных миграций * * @param int $limit Ограничение длины списка (null - полный список) * * @return array */protected function getHistoryList(int $limit = 0): array {$limitSql = $limit === 0 ? '' : "LIMIT {$limit}";$historySql = <<<SQLSELECT "name", apply_timeFROM {$this->settings['schema']}.{$this->settings['table']}ORDER BY apply_time DESC, "name" DESC {$limitSql}SQL;return $this->database->queryAll($historySql);}

Чтобы получать миграции на основе этого метода напишем еще один метод, который является оберткой на него и доступен для вызова:

/** * @inheritDoc */public function history(int $limit = 0): array {$historyList = $this->getHistoryList($limit);if (empty($historyList)) {return ['История миграций пуста'];}$messages = [];foreach ($historyList as $historyRow) {$messages[] = "Миграция {$historyRow['name']} от " . date('Y-m-d H:i:s', $historyRow['apply_time']);}return $messages;}

Так, получили все миграции, которые были применены, а теперь сделаем возможность добавления и удаления миграции в БД. Это надо для того, чтобы при применении миграции записать ее в список уже примененных и не применять заново.

/** * Добавляет запись в таблицу миграций * * @param string $name Наименование миграции * * @return bool Возвращает true, если миграция была успешно применена (добавлена в таблицу миграций). * В остальных случаях выкидывает исключение. * * @throws SqlMigrationException */protected function addHistory(string $name): bool {$sql = <<<SQLINSERT INTO {$this->settings['schema']}.{$this->settings['table']} ("name", apply_time) VALUES(:name, :apply_time);SQL;if (!$this->database->execute($sql, ['name' => $name, 'apply_time' => time()])) {throw new SqlMigrationException("Ошибка применения миграция {$name}");}return true;}/** * Удаляет миграцию из таблицы миграций * * @param string $name Наименование миграции * * @return bool Возвращает true, если миграция была успешно отменена (удалена из таблицы миграций). * В остальных случаях выкидывает исключение. * * @throws SqlMigrationException */protected function removeHistory(string $name): bool {$sql = <<<SQLDELETE FROM {$this->settings['schema']}.{$this->settings['table']} WHERE "name" = :name;SQL;if (!$this->database->execute($sql, ['name' => $name])) {throw new SqlMigrationException("Ошибка отмены миграции {$name}");}return true;}

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

/** * Возвращает список не примененных миграций * * @return array */protected function getNotAppliedList(): array {$historyList = $this->getHistoryList();$historyMap = [];foreach ($historyList as $item) {$historyMap[$item['name']] = true;}$notApplied = [];$directoryList = glob("{$this->settings['path']}/m*_*_*");foreach ($directoryList as $directory) {if (!is_dir($directory)) {continue;}$directoryParts = explode('/', $directory);preg_match('/^(m(\d{8}_?\d{6})\D.*?)$/is', end($directoryParts), $matches);$migrationName = $matches[1];if (!isset($historyMap[$migrationName])) {$migrationDateTime = DateTime::createFromFormat('Ymd_His', $matches[2])->format('Y-m-d H:i:s');$notApplied[] = ['path' => $directory,'name' => $migrationName,'date_time' => $migrationDateTime];}}ksort($notApplied);return $notApplied;}

И теперь осталось написать методы для накатывания и отката миграции: up и down. Но тут есть маленький нюанс, up и down доступны для вызова и работают одинаково за исключением применяемого файла. Следовательно, надо сделать центральный метод, который выполняет миграцию. Такой метод на вход будет принимать список миграций для выполнения, количество миграций для ограничения (если надо) и тип (up/down - константы, которые мы указали в начале).

/** * Выполняет миграции * * @param array $list Массив миграций * @param int $count Количество миграций для применения * @param string $type Тип миграции (up/down) * * @return array Список выполненных миграций * * @throws RuntimeException */protected function execute(array $list, int $count, string $type): array {$migrationInfo = [];for ($index = 0; $index < $count; $index++) {$migration = $list[$index];$migration['path'] = array_key_exists('path', $migration) ? $migration['path'] :"{$this->settings['path']}/{$migration['name']}";$migrationContent = file_get_contents("{$migration['path']}/{$type}.sql");if ($migrationContent === false) {throw new RuntimeException('Ошибка поиска/чтения миграции');}try {if (!empty($migrationContent)) {$this->database->beginTransaction();$this->database->execute($migrationContent);$this->database->commit();}if ($type === self::UP) {$this->addHistory($migration['name']);} else {$this->removeHistory($migration['name']);}$migrationInfo['success'][] = $migration;} catch (SqlMigrationException | PDOException $exception) {$migrationInfo['error'][] = array_merge($migration, ['errorMessage' => $exception->getMessage()]);break;}}return $migrationInfo;}

Метод до жути простой:

  1. Идет по каждой миграции в ограничение количества миграций для выполнения и берем ее по индексу

  2. Получаем путь до миграции $migration['path'] = array_key_exists('path', $migration) ? $migration['path'] : "{$this->settings['path']}/{$migration['name']}";

  3. Далее получаем содержимое файла с определенным типом (говорили выше): $migrationContent = file_get_contents("{$migration['path']}/{$type}.sql");

  4. И далее просто выполняем все это дело в транзакции. Если UP - до добавляем в истории, а иначе удаляем из истории.

  5. В конце пишем информацию по примененным и ошибочным миграциям (будет одна, так как на этом все упадет).

Достаточно просто, согласитесь. Ну а теперь распишем одинаковые (почти) методы up и down:

/** * @inheritDoc */public function up(int $count = 0): array {$executeList = $this->getNotAppliedList();if (empty($executeList)) {return [];}$executeListCount = count($executeList);$executeCount = $count === 0 ? $executeListCount : min($count, $executeListCount);return $this->execute($executeList, $executeCount, self::UP);}/** * @inheritDoc */public function down(int $count = 0): array {$executeList = $this->getHistoryList();if (empty($executeList)) {return [];}$executeListCount = count($executeList);$executeCount = $count === 0 ? $executeListCount : min($count, $executeListCount);return $this->execute($executeList, $executeCount, self::DOWN);}

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

<?phpdeclare(strict_types = 1);namespace mepihindeveloper\components;use mepihindeveloper\components\exceptions\SqlMigrationException;use mepihindeveloper\components\interfaces\DatabaseInterface;use RuntimeException;/** * Class ConsoleSqlMigration * * Класс предназначен для работы с SQL миграциями с выводом сообщений в консоль (терминал) * * @package mepihindeveloper\components */class ConsoleSqlMigration extends SqlMigration {public function __construct(DatabaseInterface $database, array $settings) {parent::__construct($database, $settings);try {$this->initSchemaAndTable();Console::writeLine('Схема и таблица для миграции были успешно созданы', Console::FG_GREEN);} catch (SqlMigrationException $exception) {Console::writeLine($exception->getMessage(), Console::FG_RED);exit;}}public function up(int $count = 0): array {$migrations = parent::up($count);if (empty($migrations)) {Console::writeLine("Нет миграций для применения");exit;}foreach ($migrations['success'] as $successMigration) {Console::writeLine("Миграция {$successMigration['name']} успешно применена", Console::FG_GREEN);}if (array_key_exists('error', $migrations)) {foreach ($migrations['error'] as $errorMigration) {Console::writeLine("Ошибка применения миграции {$errorMigration['name']}", Console::FG_RED);}exit;}return $migrations;}public function down(int $count = 0): array {$migrations = parent::down($count);if (empty($migrations)) {Console::writeLine("Нет миграций для отмены");exit;}if (array_key_exists('error', $migrations)) {foreach ($migrations['error'] as $errorMigration) {Console::writeLine("Ошибка отмены миграции {$errorMigration['name']} : " .PHP_EOL .$errorMigration['errorMessage'],Console::FG_RED);}exit;}foreach ($migrations['success'] as $successMigration) {Console::writeLine("Миграция {$successMigration['name']} успешно отменена", Console::FG_GREEN);}return $migrations;}public function create(string $name): bool {try {parent::create($name);Console::writeLine("Миграция {$name} успешно создана");} catch (RuntimeException | SqlMigrationException $exception) {Console::writeLine($exception->getMessage(), Console::FG_RED);return false;}return true;}public function history(int $limit = 0): array {$historyList = parent::history($limit);foreach ($historyList as $historyRow) {Console::writeLine($historyRow);}return $historyList;}}

Соглашусь, что компонент вышел не прям убойный и есть вопросы по DI к нему, но работает исправно хорошо. Данный компонент можно посмотреть на GitHub и в Composer.

Подробнее..

SQL миграции в Postgres. Часть 1

03.02.2021 00:17:16 | Автор: admin

Как обновить значение атрибута для всех записей таблицы? Как добавить первичный или уникальный ключ в таблицу? Как разбить таблицу на две? Как ...

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

На эти и другие вопросы, возникающие при проведении миграций схемы и данных в PostgreSQL, постараемся дать ответы в виде практических советов.

Эта статья расшифровка выступления на конференции SmartDataConf (здесь можно найти презентацию, со временем появится видео). Текста получилось много, поэтому материал будет разбит на 2 статьи:

  • базовые миграции
  • подходы по обновлению больших таблиц.

В конце есть выжимка всей статьи в виде сводной таблицы-шпаргалки.

Содержание


Суть проблемы
Добавление столбца
Добавление столбца со значением по умолчанию
Удаление столбца
Создание индекса
Создание индекса для партиционированной таблицы
Создание ограничения NOT NULL
Создание внешнего ключа
Создание ограничения уникальности
Создание первичного ключа
Краткая шпаргалка с миграциями

Суть проблемы


Предположим, что у нас есть приложение, которое работает с базой данных. В минимальной конфигурации оно может состоять из 2 узлов самого приложения и базы данных, соответственно.


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

Если приложение растет и появляется необходимость проводить релизы без простоя, мы начинаем использовать несколько серверов приложений. Их может быть сколько угодно, и они будут на разных версиях. В этом случае появляется необходимость обеспечения обратной совместимости.


На следующем этапе роста данные перестают влезать в одну БД. Мы начинаем масштабировать также и БД путем шардирования. Поскольку на практике синхронно проводить миграции нескольких баз данных очень сложно, это значит, что в какой-то момент они будут иметь разные схемы данных. Соответственно, мы будем работать в гетерогенной среде, где сервера приложений могут иметь разный код, а базы данных разные схемы данных.


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

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

Добавление столбца


ALTER TABLE my_table ADD COLUMN new_column INTEGER -- быстро и безопасно

Наверное, любой человек, который работает с БД, писал подобную миграцию. Если говорить про PostgreSQL, то такая миграция весьма дешевая и безопасная. Сама команда, хоть и захватывает блокировку самого высокого уровня (AccessExclusive), выполняется очень быстро, поскольку под капотом происходит лишь добавление метаинформации о новом столбце без перезаписи данных самой таблицы. В большинстве случаев это происходит незаметно. Но проблемы могут возникнуть, если в момент миграции есть долгие транзакции, работающие с этой таблицей. Чтобы понять суть проблемы рассмотрим на небольшом примере как упрощенно работают блокировки в PostgreSQL. Этот аспект будет очень важен при рассмотрении большинства других миграций в том числе.

Предположим, у нас есть большая таблица, и мы делаем из нее SELECT всех данных. В зависимости от размера БД и самой таблицы он может длиться несколько секунд или даже минут.


На время выполнения транзакции захватывается самая слабая блокировка AccessShare, которая защищает от изменений структуры таблицы.

В этот момент приходит другая транзакция, которая как раз таки пытается сделать запрос ALTER TABLE к этой таблице. Команда ALTER TABLE, как уже было сказано ранее, захватывает блокировку AccessExclusive, которая не совместима вообще ни с какой другой. Она встает в очередь.

Эта очередь блокировок разгребается в строгом порядке, т.е. даже если после ALTER TABLE приходят другие запросы (например, также SELECTы), которые сами по себе не конфликтуют с первым запросом, они все встают в очередь за ALTER TABLE. В итоге приложение встало и ждет, пока ALTER TABLE выполнится.

Что делать в такой ситуации? Можно ограничить время захвата блокировки с помощью команды SET lock_timeout. Выполняем эту команду перед ALTER TABLE (ключевое слово LOCAL означает, что настройка действует только в пределах текущей транзакции, иначе в пределах текущей сессии):

SET LOCAL lock_timeout TO '100ms'

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

Стоит сказать, что установка таймаута полезна перед любой командой, которая захватывает строгую блокировку.

Добавление столбца со значением по умолчанию


-- быстро и безопасно с PG 11ALTER TABLE my_table ADD COLUMN new_column INTEGER DEFAULT 42

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

Если версия PostgreSQL 11 или свежее, эта операция обходится весьма дешево. Дело в том, что в 11й версии была сделана оптимизация, благодаря которой вместо перезаписи таблицы значение по умолчанию сохраняется в специальную таблицу pg_attribute, и в дальнейшем при выполнении SELECT, все пустые значения этого столбца будут на лету заменяться на это значение. При этом впоследствии, когда будет происходить перезапись строк в таблице из-за других модификаций, значение будет записываться в эти строки.

Более того, с 11й версии также можно сразу создавать новый столбец и помечать его как NOT NULL:

-- быстро и безопасно с PG 11ALTER TABLE my_table ADD COLUMN new_column INTEGER DEFAULT 42 NOT NULL

Как быть, если PostgreSQL старше, чем 11?

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

ALTER TABLE my_table ADD COLUMN new_column INTEGER;ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 42;

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

Таким образом после выполнения этих команд, нам остается обновить значения, которые уже были в таблице. Грубо говоря, нам нужно сделать примерно вот так:

UPDATE my_table set new_column = 42 -- небезопасно на большой таблице

Но такой UPDATE в лоб делать на самом деле нельзя, поскольку при обновлении большой таблицы мы надолго заблокируем всю таблицу. Во второй статье (здесь в будущем будет ссылка) мы рассмотрим, какие существуют стратегии для обновления больших таблиц в PostgreSQL, а пока будем предполагать, что мы каким-то образом обновили данные, и теперь и старые данные, и новые будут с необходимым значением по умолчанию.

Удаление столбца


ALTER TABLE my_table DROP COLUMN new_column -- быстро и безопасно

Здесь логика такая же, как и при добавлении столбца: данные таблицы не модифицируются, происходит только изменение метаинформации. В данном случае столбец помечается как удаленный и недоступный при запросах. Это объясняет тот факт, что при удалении столбца в PostgreSQL физически место не освобождается (если не выполнять VACUUM FULL), то есть данные старых записей по-прежнему остаются в таблице, но недоступны при обращении. Освобождение происходит постепенно при перезаписи строк в таблице.

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

  • Для начала необходимо убрать все ограничения (NOT NULL, CHECK, ...), которые есть на этом столбце:
    ALTER TABLE my_table ALTER COLUMN new_column DROP NOT NULL
    
  • Следующий шаг обеспечить совместимость бэкенда. Нужно убедиться, что столбец нигде не используется. Например, в Hibernate необходимо пометить поле с помощью аннотации @Transient. В JOOQ, который мы используем, поле добавляется в исключения с помощью тэга <excludes>:
    <excludes>my_table.new_column</excludes>
    

    Также нужно внимательно посмотреть на запросы "SELECT *" фреймворки могут мапить все столбцы в структуру в коде (и наоборот) и, соответственно, снова можно столкнуться с проблемой обращения к несуществующему столбцу.

После вывода изменений на все сервера приложений можно удалить столбец.

Создание индекса


CREATE INDEX my_table_index ON my_table (name) -- небезопасно, блокировка таблицы

Те, кто работает с PostgreSQL, наверное, знают, что такая команда блокирует всю таблицу. Но еще с очень старой версии 8.2 существует ключевое слово CONCURRENTLY, которое позволяет создавать индекс в неблокирующем режиме.

CREATE CONCURRENTLY INDEX my_table_index ON my_table (name) -- безопасно

Команда работает медленнее, но не мешает параллельным запросам.

У этой команды есть один нюанс. Она может завершиться ошибкой например, при создании уникального индекса в таблице, содержащей дублирующиеся значения. Индекс при этом будет создан, но он будет помечен как невалидный и не будет использоваться в запросах. Статус индекса можно проверить при помощи следующего запроса:

SELECT pg_index.indisvalid    FROM pg_class, pg_indexWHERE pg_index.indexrelid = pg_class.oid    AND pg_class.relname = 'my_table_index'

В такой ситуации нужно удалить старый индекс, поправить значения в таблице и затем создать его заново.

DROP INDEX CONCURRENTLY my_table_indexUPDATE my_table ...CREATE CONCURRENTLY INDEX my_table_index ON my_table (name)

Важно заметить, что команда REINDEX, которая как раз предназначена для пересоздания индекса, до 12й версии работает только в блокирующем режиме, что не дает возможности ее использовать. В 12й версии PostgreSQL появилась поддержка CONCURRENTLY, и теперь и ей можно пользоваться.

REINDEX INDEX CONCURRENTLY my_table_index -- с PG 12

Создание индекса для партиционированной таблицы


Отдельно стоит обсудить создание индексов для партиционированных таблиц. В PostgreSQL существует 2 вида партиционирования: через наследование и декларативное, появившееся в 10й версии. Рассмотрим оба на простом примере.

Предположим, что мы хотим партиционировать таблицу по дате, и каждая партиция будет содержать данные за один год.

При партиционировании через наследование мы будем иметь примерно следующую схему.

Родительская таблица:

CREATE TABLE my_table (...reg_date  date not null)

Дочерние партиции для 2020 и 2021 годов:

CREATE TABLE my_table_y2020 (CHECK ( reg_date >= DATE '2020-01-01' AND reg_date < DATE '2021-01-01' ))INHERITS (my_table);CREATE TABLE my_table_y2021 (CHECK ( reg_date >= DATE '2021-01-01' AND reg_date < DATE '2022-01-01' ))INHERITS (my_table);

Индексы по полю партиционирования для каждой из партиций:

CREATE INDEX ON my_table_y2020 (reg_date);CREATE INDEX ON my_table_y2021 (reg_date);

Создание триггера/правила для вставки данных в таблицу оставим за рамками.

Самое важное здесь то, что каждая из партиций это практически самостоятельная таблица, которая обслуживается по отдельности. Таким образом, создание новых индексов также проводится как с обычными таблицами:

CREATE CONCURRENTLY INDEX my_table_y2020_index ON my_table_y2020 (name);CREATE CONCURRENTLY INDEX my_table_y2021_index ON my_table_y2021 (name);

Теперь рассмотрим декларативное партиционирование.

CREATE TABLE my_table (...) PARTITION BY RANGE (reg_date);CREATE TABLE my_table_y2020 PARTITION OF my_table FOR VALUES FROM ('2020-01-01') TO ('2020-12-31');CREATE TABLE my_table_y2021 PARTITION OF my_table FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

Создание индексов зависит от версии PostgreSQL. В 10й версии индексы создаются по отдельности точно так же как в предыдущем подходе. Соответственно, создание новых индексов для существующей таблицы также производится аналогично.

В 11й версии декларативное партиционирование было доработано и теперь таблицы обслуживаются вместе. Создание индекса на родительской таблице автоматически приводит к созданию индексов для всех существующих и новых партиций, которые будут созданы в будущем:

-- с PG 11 удобно для новой (пустой) партиционированной таблицыCREATE INDEX ON my_table (reg_date)

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

CREATE INDEX ON my_table (name) -- блокировка таблиц

К сожалению, CREATE INDEX не поддерживает ключевое слово CONCURRENTLY для партиционированных таблиц. Чтобы обойти ограничение и провести миграцию без блокировок можно сделать следующее.

  1. Создать индекс для родительской таблицы с опцией ONLY
    CREATE INDEX my_table_index ON ONLY my_table (name)
    

    Команда создаст пустой невалидный индекс без создания индексов для партиций.
  2. Создать индексы для каждой из партиций:
    CREATE CONCURRENTLY INDEX my_table_y2020_index ON my_table_y2020 (name);CREATE CONCURRENTLY INDEX my_table_y2021_index ON my_table_y2021 (name);
    
  3. Прикрепить индексы партиций к индексу родительской таблицы:
    ALTER INDEX my_table_index ATTACH PARTITION my_table_y2020_index;ALTER INDEX my_table_index ATTACH PARTITION my_table_y2021_index;
    
    Как только все индексы будут прикреплены, индекс родительской таблицы автоматически станет валидным.

Ограничения


Теперь пройдемся по ограничениям: NOT NULL, внешние, уникальные и первичные ключи.

Создание ограничения NOT NULL


ALTER TABLE my_table ALTER COLUMN name SET NOT NULL -- блокировка таблицы

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

Что можно сделать? В PostgreSQL есть другой тип ограничения, CHECK, с помощью которого можно получить желаемый результат. Это ограничение проверяет любое булево условие, состоящее из столбцов строки. В нашем случае условие тривиально CHECK (name IS NOT NULL). Но самое важное то, что ограничение CHECK поддерживает невалидность (ключевое слово NOT VALID):

ALTER TABLE my_table ADD CONSTRAINT chk_name_not_null     CHECK (name IS NOT NULL) NOT VALID -- безопасно, с PG 9.2

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

Для обеспечения гарантии, что существующие записи также удовлетворяют ограничению необходимо провести его валидацию (конечно же, предварительно обновив данные в таблице):

ALTER TABLE my_table VALIDATE CONSTRAINT chk_name_not_null

Команда итерируется по строкам таблицы и проверяет, что все записи не not null. Но в отличие от обычного NOT NULL ограничения, блокировка, захватываемая в этой команде, не такая строгая (ShareUpdateExclusive) она не блокирует операции insert, update и delete.

Создание внешнего ключа


ALTER TABLE my_table ADD CONSTRAINT fk_group     FOREIGN KEY (group_id) REFERENCES groups(id) -- блокировка обеих таблиц

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

К счастью, внешние ключи в PostgreSQL также поддерживают NOT VALID, а это значит мы можем использовать тот же подход, что был рассмотрен ранее с CHECK. Создаем невалидный внешний ключ:

ALTER TABLE my_table ADD CONSTRAINT fk_group     FOREIGN KEY (group_id)REFERENCES groups(id) NOT VALID

затем обновляем данные и проводим валидацию:

ALTER TABLE my_table VALIDATE CONSTRAINT fk_group_id


Создание ограничения уникальности


ALTER TABLE my_table ADD CONSTRAINT uk_my_table UNIQUE (id) -- блокировка таблицы

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

Важно знать, что под капотом PostgreSQL реализует уникальные ограничения с помощью уникальных индексов. Иными словами, когда создается ограничение, происходит создание соответствующего уникального индекса с таким же наименованием, который обслуживает это ограничение. С помощью следующего запроса можно узнать обслуживающий индекс ограничения:

SELECT conindid index_oid, conindid::regclass index_name     FROM pg_constraint WHERE conname = 'uk_my_table_id'

При этом большая часть времени создания ограничения как раз таки уходит на индекс, а его последующая привязка к ограничению происходит очень быстро. Более того, если у вас уже есть созданный уникальный индекс, то такую привязку можно осуществить самостоятельно, создавая индекс с помощью ключевых слов USING INDEX:

ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id UNIQUE     USING INDEX uk_my_table_id -- быстро, с PG 9.1

Таким образом, идея простая создаем уникальный индекс CONCURRENTLY, как мы рассматривали ранее, и затем на его основе создаем уникальное ограничение.

В этот момент может возникнуть вопрос зачем вообще создавать ограничение, если индекс выполняет ровно то, что требуется гарантирует уникальность значений? Если исключить из сравнения partial индексы, то с функциональной точки зрения результат действительно почти идентичен. Единственное отличие, которое удалось найти, состоит в том, что ограничения могут быть отложенными (deferrable), а индексы нет. В документации к старым версиям PostgreSQL (до 9.4 включительно) была сноска с информацией о том, что предпочтительный способ создания ограничения уникальности это явное создание ограничения ALTER TABLE ... ADD CONSTRAINT, а использование индексов стоит рассматривать как деталь реализации. Однако, в более свежих версиях эту сноску удалили.

Создание первичного ключа


Первичный ключ помимо уникальности накладывает ограничение not null. Если столбец изначально имел такое ограничение, то превратить его в первичный ключ не составит труда также создаем уникальный индекс CONCURRENTLY, а затем первичный ключ:

ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id PRIMARY KEY     USING INDEX uk_my_table_id -- если id is NOT NULL

Важно отметить, что столбец должен иметь честное ограничение NOT NULL рассмотренный ранее подход с помощью CHECK не сработает.

Если же ограничения нет, то до 11-й версии PostgreSQL ничего не поделать без блокировки первичный ключ никак не создать.

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

Создаем новый столбец, который по умолчанию not null и имеет значение по умолчанию:

ALTER TABLE my_table ADD COLUMN new_id INTEGER NOT NULL DEFAULT -1 -- безопасно с PG 11

Настраиваем синхронизацию данных старого и нового столбцов с помощью триггера:

CREATE FUNCTION on_insert_or_update() RETURNS TRIGGER AS$$BEGIN  NEW.new_id = NEW.id;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg BEFORE INSERT OR UPDATE ON my_tableFOR EACH ROW EXECUTE PROCEDURE on_insert_or_update();

Далее необходимо обновить данные строк, которые не затронулись триггером:

UPDATE my_table SET new_id = id WHERE new_id = -1 -- не делать на большой таблице

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

ALTER TABLE my_table RENAME COLUMN id TO old_id;ALTER TABLE my_table RENAME COLUMN new_id TO id;ALTER TABLE my_table RENAME COLUMN old_id TO new_id;

В PostgreSQL команды DDL транзакционные это значит, что можно переименовывать, добавлять, удалять столбцы, и при этом параллельная транзакция не будет этого видеть в процессе своих операций.

После смены столбцов остается создать индекс и прибраться за собой удалить триггер, функцию и старый столбец.

Краткая шпаргалка с миграциями


Перед любой командой, которая захватывает строгую блокировку (почти все ALTER TABLE ...) рекомендуется вызывать:

SET LOCAL lock_timeout TO '100ms'

Миграция Рекомендуемый подход
Добавление столбца
ALTER TABLE my_table ADD COLUMN new_column INTEGER
Добавление столбца со значением по умолчанию [и NOT NULL] c PostgreSQL 11:
ALTER TABLE my_table ADD COLUMN new_column INTEGER DEFAULT 42 [NOT NULL]

до PostgreSQL 11:
  1. ALTER TABLE my_table ADD COLUMN new_column INTEGER;ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 42;
    
  2. обновление таблицы
Удаление столбца
  1. удаление ограничений (NOT NULL, CHECK и т.д.)
  2. подготовка кода
  3. ALTER TABLE my_table DROP COLUMN removed_column
    
Создание индекса
CREATE CONCURRENTLY INDEX my_table_index ON my_table (name)

Если завершилось ошибкой:
  1. DROP INDEX CONCURRENTLY my_table_index
    
  2. обновление таблицы
  3. CREATE CONCURRENTLY INDEX my_table_index ON my_table (name)
    

Создание индекса для партиционированной таблицы Партиционирование через наследование + декларативное в PG 10:
CREATE CONCURRENTLY INDEX my_table_y2020_index ON my_table_y2020 (name);CREATE CONCURRENTLY INDEX my_table_y2021_index ON my_table_y2021 (name);...

Декларативное партиционирование с PG 11:
  1. CREATE INDEX my_table_index ON ONLY my_table (name)
    
  2. CREATE CONCURRENTLY INDEX my_table_y2020_index ON my_table_y2020 (name);CREATE CONCURRENTLY INDEX my_table_y2021_index ON my_table_y2021 (name);...
    
  3. ALTER INDEX my_table_index ATTACH PARTITION my_table_y2020_index;ALTER INDEX my_table_index ATTACH PARTITION my_table_y2021_index;...
    
Создание ограничения NOT NULL
  1. ALTER TABLE my_table ADD CONSTRAINT chk_name_not_null CHECK (name IS NOT NULL) NOT VALID
    
  2. обновление таблицы
  3. ALTER TABLE my_table VALIDATE CONSTRAINT chk_name_not_null
    

Создание внешнего ключа
  1. ALTER TABLE my_table ADD CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES groups(id) NOT VALID
    
  2. обновление таблицы
  3. ALTER TABLE my_table VALIDATE CONSTRAINT fk_group_id
    
Создание ограничения уникальности
  1. CREATE UNIQUE INDEX CONCURRENTLY uk_my_table_id ON my_table(id)
    
  2. ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id UNIQUE USING INDEX uk_my_table_id
    
Создание первичного ключа Если столбец IS NOT NULL:
  1. CREATE UNIQUE INDEX CONCURRENTLY uk_my_table_id ON my_table(id)
    
  2. ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id PRIMARY KEY USING INDEX uk_my_table_id
    

Если столбец IS NULL c PG 11:
  1. ALTER TABLE my_table ADD COLUMN new_id INTEGER NOT NULL DEFAULT -1
    
  2. CREATE FUNCTION on_insert_or_update() RETURNS TRIGGER AS$$BEGINNEW.new_id = NEW.id;RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg BEFORE INSERT OR UPDATE ON my_tableFOR EACH ROW EXECUTE PROCEDURE on_insert_or_update();
    
  3. обновление таблицы
  4. ALTER TABLE my_table RENAME COLUMN id TO old_id;ALTER TABLE my_table RENAME COLUMN new_id TO id;ALTER TABLE my_table RENAME COLUMN old_id TO new_id;
    
  5. CREATE UNIQUE INDEX CONCURRENTLY uk_my_table_id ON my_table(id)
    
  6. ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id PRIMARY KEY USING INDEX uk_my_table_id
    
  7. DROP TRIGGER trg ON my_table;DROP FUNCTION on_insert_or_update();ALTER TABLE my_table DROP COLUMN new_id;
    

В следующей статье рассмотрим подходы по обновлению больших таблиц.
Всем легких миграций!
Подробнее..

Recovery mode Code style для миграций Laravel

19.02.2021 02:10:05 | Автор: admin

Всем привет.

Первые пять лет своей карьеры программиста, я работал над in-house проектом, следующие семь лет я работал в разных стартапах, с командой максимум из пяти разработчиков.

Сейчас я пару месяцев работаю над проектом где 20+ разработчиков, работа одновременно ведётся в примерно 30-ти ветках, имеется пять сред для отработки кода (драфт, дев, тестинг, хотфикс, прод), у каждой среды своя БД (перед выкаткой камита на стенд/среду, происходит проверочная выкатка с использованием отдельной БД, то есть на пять сред мы имеем 10 отдельных баз данных).

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

Есть большая проблема с тем как синхронизировать версию кода и версию схемы базы данных.

Ниже я расскажу о правилах которые я принял для себя и буду рад если вы поделитесь своими приёмами и техниками, которые помогают вам справиться с этой бедой.

Disclaimer

Код представленный ниже это обусифицированныйбоевой код, я его не отлаживал, может потребоваться доработка напильником. Я делюсь с вами только идеями.

Описание проблемы

Есть у нас ветка драфт, что бы пушить в драфт не надо делать МР, эта ветка нужна для того что бы программист мог по быстрому показать свои наработки бизнес аналитику, что бы фронт-энд мог интегрироваться с изменениями на бэк-энде. Драфт регулярно ресетиться.

То есть ты что то сделал с функционалом, решил показать другим разработчикам - выкатываешь всё на драфт, на БД драфта раскатывается твоя миграция, ты показал, понял что был не прав и переделываешь миграцию, в это время кто то ресетит драфт вместе с твоей миграцией, и что мы имеем ? мы имеем миграцию которая были применена и которую ни кто не откатил, как думаете получиться ещё раз её применить ?

Другая проблема в том что миграции локально применяются по мере разработки кода, а накатываются на БД, по мере тестирования кода. Фича которую ты разработал может на пару недель застрять в тестировании, а фича которую ты делал на этой неделе уже будет раскатана сегодня, и когда завтра из тестирования выйдет первая фича и будет накатываться на прод, могут возникнуть проблемы. Это не смертельно, но очень неприятно.

То есть порядок применения миграций иногда бывает хаотичным.

Правило первое: "накатывать можно бесконечно"

Правило первое, пишем миграцию так что бы её можно было бесконечное количество раз накатывать на саму себя. То есть накатываем миграцию, удаляем запись этой миграции в таблице migrations, и если мы ещё раз накатим эту же миграцию, то не должно быть эксепшена, миграция не должна вызвать сбой.

Миграцию можно как бесконечное количество раз накатить на саму себя, так и откатить с самой себя.

Для работы миграциями по штучно я использую такое команды:

# накатить конкретную миграциюphp artisan migrate --path="services/best-team-servise/database/migrations/2021_02_04_240000_alter_data_model_table_add_unique_index.php" --pretend# ключ --pretend позволит нам посмотреть SQL до того как будет применена миграция, иногда полезно# откатить ровно одну миграциюphp artisan migrate:rollback --step=1# можно откатить и десять, при случае раберётесь

После того как миграция применена, можно сгенерировать описание модели

php artisan ide-helper:models "Project\Models\DataModel"

и сделать посев данных:

php artisan db:seed --class=DataModelSeeder

Как сделать так что бы миграцию можно было бесконечно применять ? в методах up() и down() миграции, мы должны делать проверку того, что действие со схемой БД можно выполнить.

Если мы добавляем колонку, то проверяем что колонка не существует, если мы дропаем индекс, то проверяем что индекс существует.

Получаем Builder :

        $conn = (new DataModel())->connection;        $builder = Schema::connection($conn);

проверяем что миграция не была применена (проверяем что миграция может быть выполнена):

        $isExists = $builder->hasColumn(            'data_model',            'deleted_at'        );

Если миграция может быть выполнена, то выполняем:

        if (!$isExists) {            $builder->table(                'data_model',                function (Blueprint $table) {                    $table->softDeletesTz();                }            );        }

Аналогично с таблицами - проверяем что таблица не существует, с индексами - проверяем что индекс не существует, с индексами посложней, но можно, поможет такой код:

        $alias = (new DataModel())->connection;        $builder = Schema            ::connection($alias)            ->getConnection()            ->getDoctrineSchemaManager();$existingIndexes = $builder->listTableIndexes('data_model');

С индексами в Laravel есть заморочка, если мы создали индекс как:

Blueprint::unique('index_name');

То и удалять надо как:

Blueprint::dropUnique('index_name');

С таблицами и колонками у Laravel ок, с индексами похуже, с триггерами совсем плохо, приходиться писать на чистом SQL, наверное я чего то не знаю о Laravel ? Адепты, подскажите !

Накатываем чистым SQL, пишем так:

DROP TRIGGER IF EXISTS trigger_name    ON public.data_model;CREATE TRIGGER trigger_name    BEFORE INSERT    ON public.data_model    FOR EACH ROW    EXECUTE PROCEDURE public.function_name();

Когда откатываем, пишем так же:

DROP TRIGGER IF EXISTS trigger_name    ON public.data_model;

Правило второе: "шаблонные имена"

Миграций в проекте сотни, за полгода было написано 1000+ миграций. Конечно вся 1000 не лежит в одной директории, они раскиданы по директориям модулей.

Но тем нем нее, когда открываешь директорию в которой 50+ миграций, тебе сложно ориентироваться в них без "хороших" имён.

Соглашения об именах

Если создаём таблицу, то имя миграции начинается с create, если меняем таблицу то alter, если удаляем таблицу, то drop.

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

alter_data_model_add_property_columnalter_data_model_alter_property_column_to_textalter_data_model_alter_property_column_set_default_valuealter_data_model_create_index_on_code_type_columnsalter_data_model_create_unique_index_on_code_column

Дропать таблицы, конечно, можно только на этапе MVP.

Команды для создания миграций:

# делаем миграцию для создания таблицыphp artisan make:migration create_profile_table --create=profile# делаем миграцию для изменения таблицыphp artisan make:migration add_confirmed_to_profile --table=profile

Файл миграции будет помещён в директорию database/migrations собственно приложения, у нас каждый сервис это отдельный пакет и после создания файла миграции его надо переложить в директорию своего пакета.

Правило третье: таблицы и колонки дропать нельзя, все колонки nullable()

Поскольку миграции применяются хаотично, и в одной версии колонка может быть создана и если колонка будет NOT NULL, то версия кода, которая ни чего не знает об этой колонке, не сможет работать, пока мы не откатим миграцию в которой эта колонка создаётся.

Откатывать миграции вместе с репозиторием, эта так себе занятие.

Поэтому все колонки должны быть только nullable(), проверку того что обязательные колонки имеют значение, надо делать на уровне кода бизнес логики или с помощью обсерверов.

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

            $columns = Schema            ::connection((new DataModel())->connection)            ->getConnection()            ->getDoctrineSchemaManager()            ->listTableColumns($(new DataModel())->getTable());            $data = [];            foreach ($columns as $column) {                $name = $column->getName();/* @var array[] $record строка данных для посева*/                $exists = key_exists($name, $record);                if ($exists) {                    $data[$name] = $record[$name];                }            }            $isSuccess = DataModel                ::withTrashed()                ->updateOrCreate(                    ['uniqe_index_column' => $data['uniqe_index_column'],],                    $data                )->exists;

Правило четвёртое: значения по умолчанию, там где null недопустим

Булевые колонки должны иметь значение по умолчанию, колонки статусов и тому подобное, тоже должны иметь значения по умолчанию, что бы записи добавленные кодом который ни чего не знает об этих колонках, не ломал работу кода, для которого эти колонки необходимы.

Либо можно в коде использовать какое то значение по умолчанию, но я такой способ не люблю, потому что это хард код, и это убивает гибкость нашего приложения. Работа приложения должна настраиваться или через переменные окружения, или файлы конфигов, или записи БД.

Заключение

Этот набор правил конечно не абсолютен, в первую очередь включаем голову и пользуемся здравым смыслом.

Давайте обсудим в комментариях. Пожалуйста, поделитесь своим опытом.

Подробнее..
Категории: Sql , Laravel , Migrations

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru