diff options
Diffstat (limited to 'lib/gitlab/database/migration_helpers.rb')
| -rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 302 |
1 files changed, 301 insertions, 1 deletions
diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index fc445ab9483..e76c9abbe04 100644 --- a/lib/gitlab/database/migration_helpers.rb +++ b/lib/gitlab/database/migration_helpers.rb @@ -26,6 +26,30 @@ module Gitlab add_index(table_name, column_name, options) end + # Removes an existed index, concurrently when supported + # + # On PostgreSQL this method removes an index concurrently. + # + # Example: + # + # remove_concurrent_index :users, :some_column + # + # See Rails' `remove_index` for more info on the available arguments. + def remove_concurrent_index(table_name, column_name, options = {}) + if transaction_open? + raise 'remove_concurrent_index can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + if Database.postgresql? + options = options.merge({ algorithm: :concurrently }) + disable_statement_timeout + end + + remove_index(table_name, options.merge({ column: column_name })) + end + # Adds a foreign key with only minimal locking on the tables involved. # # This method only requires minimal locking when using PostgreSQL. When @@ -65,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 @@ -90,6 +115,14 @@ module Gitlab execute('SET statement_timeout TO 0') if Database.postgresql? end + def true_value + Database.true_value + end + + def false_value + Database.false_value + end + # Updates the value of a column in batches. # # This method updates the table in batches of 5% of the total row count. @@ -226,6 +259,273 @@ 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 + + old_col = column_for(table, old) + new_type = type || old_col.type + + add_column(table, new, new_type, + limit: old_col.limit, + precision: old_col.precision, + scale: old_col.scale) + + # We set the default value _after_ adding the column so we don't end up + # updating any existing data with the default value. This isn't + # necessary since we copy over old values further down. + change_column_default(table, new, old_col.default) if old_col.default + + 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 + + update_column_in_batches(table, new, Arel::Table.new(table)[old]) + + change_column_null(table, new, false) unless old_col.null + + 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 + + # This will replace the first occurance of a string in a column with + # the replacement + # On postgresql we can use `regexp_replace` for that. + # On mysql we find the location of the pattern, and overwrite it + # with the replacement + def replace_sql(column, pattern, replacement) + quoted_pattern = Arel::Nodes::Quoted.new(pattern.to_s) + quoted_replacement = Arel::Nodes::Quoted.new(replacement.to_s) + + if Database.mysql? + locate = Arel::Nodes::NamedFunction. + new('locate', [quoted_pattern, column]) + insert_in_place = Arel::Nodes::NamedFunction. + new('insert', [column, locate, pattern.size, quoted_replacement]) + + Arel::Nodes::SqlLiteral.new(insert_in_place.to_sql) + else + replace = Arel::Nodes::NamedFunction. + new("regexp_replace", [column, quoted_pattern, quoted_replacement]) + Arel::Nodes::SqlLiteral.new(replace.to_sql) + end + end end end end |