Prepare for zero downtime migrationszero-downtime-migrations

Starting with GitLab 9.1.0 we will no longer allow downtime migrations
unless absolutely necessary. This commit updates the various developer
guides and adds code that is necessary to make zero downtime migrations
less painful.

2 files changed, 293 insertions, 1 deletions

diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb
index 525aa920328..a6873ac63a0 100644
--- a/lib/gitlab/database/migration_helpers.rb
+++ b/lib/gitlab/database/migration_helpers.rb
@@ -89,7 +89,8 @@ module Gitlab
         ADD CONSTRAINT #{key_name}
         FOREIGN KEY (#{column})
         REFERENCES #{target} (id)
-        ON DELETE #{on_delete} NOT VALID;
+        #{on_delete ? "ON DELETE #{on_delete}" : ''}
+        NOT VALID;
         EOF
 
         # Validate the existing constraint. This can potentially take a very
@@ -250,6 +251,245 @@ module Gitlab
           raise error
         end
       end
+
+      # Renames a column without requiring downtime.
+      #
+      # Concurrent renames work by using database triggers to ensure both the
+      # old and new column are in sync. However, this method will _not_ remove
+      # the triggers or the old column automatically; this needs to be done
+      # manually in a post-deployment migration. This can be done using the
+      # method `cleanup_concurrent_column_rename`.
+      #
+      # table - The name of the database table containing the column.
+      # old - The old column name.
+      # new - The new column name.
+      # type - The type of the new column. If no type is given the old column's
+      #        type is used.
+      def rename_column_concurrently(table, old, new, type: nil)
+        if transaction_open?
+          raise 'rename_column_concurrently can not be run inside a transaction'
+        end
+
+        trigger_name = rename_trigger_name(table, old, new)
+        quoted_table = quote_table_name(table)
+        quoted_old = quote_column_name(old)
+        quoted_new = quote_column_name(new)
+
+        if Database.postgresql?
+          install_rename_triggers_for_postgresql(trigger_name, quoted_table,
+                                                 quoted_old, quoted_new)
+        else
+          install_rename_triggers_for_mysql(trigger_name, quoted_table,
+                                            quoted_old, quoted_new)
+        end
+
+        old_col = column_for(table, old)
+        new_type = type || old_col.type
+
+        add_column(table, new, new_type,
+                   limit: old_col.limit,
+                   default: old_col.default,
+                   null: old_col.null,
+                   precision: old_col.precision,
+                   scale: old_col.scale)
+
+        update_column_in_batches(table, new, Arel::Table.new(table)[old])
+
+        copy_indexes(table, old, new)
+        copy_foreign_keys(table, old, new)
+      end
+
+      # Changes the type of a column concurrently.
+      #
+      # table - The table containing the column.
+      # column - The name of the column to change.
+      # new_type - The new column type.
+      def change_column_type_concurrently(table, column, new_type)
+        temp_column = "#{column}_for_type_change"
+
+        rename_column_concurrently(table, column, temp_column, type: new_type)
+      end
+
+      # Performs cleanup of a concurrent type change.
+      #
+      # table - The table containing the column.
+      # column - The name of the column to change.
+      # new_type - The new column type.
+      def cleanup_concurrent_column_type_change(table, column)
+        temp_column = "#{column}_for_type_change"
+
+        transaction do
+          # This has to be performed in a transaction as otherwise we might have
+          # inconsistent data.
+          cleanup_concurrent_column_rename(table, column, temp_column)
+          rename_column(table, temp_column, column)
+        end
+      end
+
+      # Cleans up a concurrent column name.
+      #
+      # This method takes care of removing previously installed triggers as well
+      # as removing the old column.
+      #
+      # table - The name of the database table.
+      # old - The name of the old column.
+      # new - The name of the new column.
+      def cleanup_concurrent_column_rename(table, old, new)
+        trigger_name = rename_trigger_name(table, old, new)
+
+        if Database.postgresql?
+          remove_rename_triggers_for_postgresql(table, trigger_name)
+        else
+          remove_rename_triggers_for_mysql(trigger_name)
+        end
+
+        remove_column(table, old)
+      end
+
+      # Performs a concurrent column rename when using PostgreSQL.
+      def install_rename_triggers_for_postgresql(trigger, table, old, new)
+        execute <<-EOF.strip_heredoc
+        CREATE OR REPLACE FUNCTION #{trigger}()
+        RETURNS trigger AS
+        $BODY$
+        BEGIN
+          NEW.#{new} := NEW.#{old};
+          RETURN NEW;
+        END;
+        $BODY$
+        LANGUAGE 'plpgsql'
+        VOLATILE
+        EOF
+
+        execute <<-EOF.strip_heredoc
+        CREATE TRIGGER #{trigger}
+        BEFORE INSERT OR UPDATE
+        ON #{table}
+        FOR EACH ROW
+        EXECUTE PROCEDURE #{trigger}()
+        EOF
+      end
+
+      # Installs the triggers necessary to perform a concurrent column rename on
+      # MySQL.
+      def install_rename_triggers_for_mysql(trigger, table, old, new)
+        execute <<-EOF.strip_heredoc
+        CREATE TRIGGER #{trigger}_insert
+        BEFORE INSERT
+        ON #{table}
+        FOR EACH ROW
+        SET NEW.#{new} = NEW.#{old}
+        EOF
+
+        execute <<-EOF.strip_heredoc
+        CREATE TRIGGER #{trigger}_update
+        BEFORE UPDATE
+        ON #{table}
+        FOR EACH ROW
+        SET NEW.#{new} = NEW.#{old}
+        EOF
+      end
+
+      # Removes the triggers used for renaming a PostgreSQL column concurrently.
+      def remove_rename_triggers_for_postgresql(table, trigger)
+        execute("DROP TRIGGER #{trigger} ON #{table}")
+        execute("DROP FUNCTION #{trigger}()")
+      end
+
+      # Removes the triggers used for renaming a MySQL column concurrently.
+      def remove_rename_triggers_for_mysql(trigger)
+        execute("DROP TRIGGER #{trigger}_insert")
+        execute("DROP TRIGGER #{trigger}_update")
+      end
+
+      # Returns the (base) name to use for triggers when renaming columns.
+      def rename_trigger_name(table, old, new)
+        'trigger_' + Digest::SHA256.hexdigest("#{table}_#{old}_#{new}").first(12)
+      end
+
+      # Returns an Array containing the indexes for the given column
+      def indexes_for(table, column)
+        column = column.to_s
+
+        indexes(table).select { |index| index.columns.include?(column) }
+      end
+
+      # Returns an Array containing the foreign keys for the given column.
+      def foreign_keys_for(table, column)
+        column = column.to_s
+
+        foreign_keys(table).select { |fk| fk.column == column }
+      end
+
+      # Copies all indexes for the old column to a new column.
+      #
+      # table - The table containing the columns and indexes.
+      # old - The old column.
+      # new - The new column.
+      def copy_indexes(table, old, new)
+        old = old.to_s
+        new = new.to_s
+
+        indexes_for(table, old).each do |index|
+          new_columns = index.columns.map do |column|
+            column == old ? new : column
+          end
+
+          # This is necessary as we can't properly rename indexes such as
+          # "ci_taggings_idx".
+          unless index.name.include?(old)
+            raise "The index #{index.name} can not be copied as it does not "\
+              "mention the old column. You have to rename this index manually first."
+          end
+
+          name = index.name.gsub(old, new)
+
+          options = {
+            unique: index.unique,
+            name: name,
+            length: index.lengths,
+            order: index.orders
+          }
+
+          # These options are not supported by MySQL, so we only add them if
+          # they were previously set.
+          options[:using] = index.using if index.using
+          options[:where] = index.where if index.where
+
+          unless index.opclasses.blank?
+            opclasses = index.opclasses.dup
+
+            # Copy the operator classes for the old column (if any) to the new
+            # column.
+            opclasses[new] = opclasses.delete(old) if opclasses[old]
+
+            options[:opclasses] = opclasses
+          end
+
+          add_concurrent_index(table, new_columns, options)
+        end
+      end
+
+      # Copies all foreign keys for the old column to the new column.
+      #
+      # table - The table containing the columns and indexes.
+      # old - The old column.
+      # new - The new column.
+      def copy_foreign_keys(table, old, new)
+        foreign_keys_for(table, old).each do |fk|
+          add_concurrent_foreign_key(fk.from_table,
+                                     fk.to_table,
+                                     column: new,
+                                     on_delete: fk.on_delete)
+        end
+      end
+
+      # Returns the column for the given table and column name.
+      def column_for(table, name)
+        name = name.to_s
+
+        columns(table).find { |column| column.name == name }
+      end
     end
   end
 end
diff --git a/lib/gitlab/database/multi_threaded_migration.rb b/lib/gitlab/database/multi_threaded_migration.rb
new file mode 100644
index 00000000000..7ae5a4c17c8
--- /dev/null
+++ b/lib/gitlab/database/multi_threaded_migration.rb
@@ -0,0 +1,52 @@
+module Gitlab
+  module Database
+    module MultiThreadedMigration
+      MULTI_THREAD_AR_CONNECTION = :thread_local_ar_connection
+
+      # This overwrites the default connection method so that every thread can
+      # use a thread-local connection, while still supporting all of Rails'
+      # migration methods.
+      def connection
+        Thread.current[MULTI_THREAD_AR_CONNECTION] ||
+          ActiveRecord::Base.connection
+      end
+
+      # Starts a thread-pool for N threads, along with N threads each using a
+      # single connection. The provided block is yielded from inside each
+      # thread.
+      #
+      # Example:
+      #
+      #     with_multiple_threads(4) do
+      #       execute('SELECT ...')
+      #     end
+      #
+      # thread_count - The number of threads to start.
+      #
+      # join - When set to true this method will join the threads, blocking the
+      #        caller until all threads have finished running.
+      #
+      # Returns an Array containing the started threads.
+      def with_multiple_threads(thread_count, join: true)
+        pool = Gitlab::Database.create_connection_pool(thread_count)
+
+        threads = Array.new(thread_count) do
+          Thread.new do
+            pool.with_connection do |connection|
+              begin
+                Thread.current[MULTI_THREAD_AR_CONNECTION] = connection
+                yield
+              ensure
+                Thread.current[MULTI_THREAD_AR_CONNECTION] = nil
+              end
+            end
+          end
+        end
+
+        threads.each(&:join) if join
+
+        threads
+      end
+    end
+  end
+end