diff options
Diffstat (limited to 'lib/gitlab/database/migration_helpers.rb')
| -rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 258 |
1 files changed, 129 insertions, 129 deletions
diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index e2cbf91f281..57a413f8e04 100644 --- a/lib/gitlab/database/migration_helpers.rb +++ b/lib/gitlab/database/migration_helpers.rb @@ -6,31 +6,45 @@ module Gitlab BACKGROUND_MIGRATION_BATCH_SIZE = 1000 # Number of rows to process per job BACKGROUND_MIGRATION_JOB_BUFFER_SIZE = 1000 # Number of jobs to bulk queue at a time + PERMITTED_TIMESTAMP_COLUMNS = %i[created_at updated_at deleted_at].to_set.freeze + DEFAULT_TIMESTAMP_COLUMNS = %i[created_at updated_at].freeze + # Adds `created_at` and `updated_at` columns with timezone information. # # This method is an improved version of Rails' built-in method `add_timestamps`. # + # By default, adds `created_at` and `updated_at` columns, but these can be specified as: + # + # add_timestamps_with_timezone(:my_table, columns: [:created_at, :deleted_at]) + # + # This allows you to create just the timestamps you need, saving space. + # # Available options are: - # default - The default value for the column. - # null - When set to `true` the column will allow NULL values. + # :default - The default value for the column. + # :null - When set to `true` the column will allow NULL values. # The default is to not allow NULL values. + # :columns - the column names to create. Must be one + # of `Gitlab::Database::MigrationHelpers::PERMITTED_TIMESTAMP_COLUMNS`. + # Default value: `DEFAULT_TIMESTAMP_COLUMNS` + # + # All options are optional. def add_timestamps_with_timezone(table_name, options = {}) options[:null] = false if options[:null].nil? + columns = options.fetch(:columns, DEFAULT_TIMESTAMP_COLUMNS) + default_value = options[:default] - [:created_at, :updated_at].each do |column_name| - if options[:default] && transaction_open? - raise '`add_timestamps_with_timezone` with default value cannot be run inside a transaction. ' \ - 'You can disable transactions by calling `disable_ddl_transaction!` ' \ - 'in the body of your migration class' - end + validate_not_in_transaction!(:add_timestamps_with_timezone, 'with default value') if default_value + + columns.each do |column_name| + validate_timestamp_column_name!(column_name) # If default value is presented, use `add_column_with_default` method instead. - if options[:default] + if default_value add_column_with_default( table_name, column_name, :datetime_with_timezone, - default: options[:default], + default: default_value, allow_null: options[:null] ) else @@ -39,10 +53,22 @@ module Gitlab end end - # Creates a new index, concurrently when supported + # To be used in the `#down` method of migrations that + # use `#add_timestamps_with_timezone`. # - # On PostgreSQL this method creates an index concurrently, on MySQL this - # creates a regular index. + # Available options are: + # :columns - the column names to remove. Must be one + # Default value: `DEFAULT_TIMESTAMP_COLUMNS` + # + # All options are optional. + def remove_timestamps(table_name, options = {}) + columns = options.fetch(:columns, DEFAULT_TIMESTAMP_COLUMNS) + columns.each do |column_name| + remove_column(table_name, column_name) + end + end + + # Creates a new index, concurrently # # Example: # @@ -56,12 +82,10 @@ module Gitlab 'in the body of your migration class' end - if Database.postgresql? - options = options.merge({ algorithm: :concurrently }) - end + options = options.merge({ algorithm: :concurrently }) if index_exists?(table_name, column_name, options) - Rails.logger.warn "Index not created because it already exists (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" + Rails.logger.warn "Index not created because it already exists (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" # rubocop:disable Gitlab/RailsLogger return end @@ -70,9 +94,7 @@ module Gitlab end end - # Removes an existed index, concurrently when supported - # - # On PostgreSQL this method removes an index concurrently. + # Removes an existed index, concurrently # # Example: # @@ -91,7 +113,7 @@ module Gitlab end unless index_exists?(table_name, column_name, options) - Rails.logger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" + Rails.logger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" # rubocop:disable Gitlab/RailsLogger return end @@ -100,9 +122,7 @@ module Gitlab end end - # Removes an existing index, concurrently when supported - # - # On PostgreSQL this method removes an index concurrently. + # Removes an existing index, concurrently # # Example: # @@ -121,7 +141,7 @@ module Gitlab end unless index_exists_by_name?(table_name, index_name) - Rails.logger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, index_name: #{index_name}" + Rails.logger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, index_name: #{index_name}" # rubocop:disable Gitlab/RailsLogger return end @@ -132,8 +152,6 @@ module Gitlab # Only available on Postgresql >= 9.2 def supports_drop_index_concurrently? - return false unless Database.postgresql? - version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i version >= 90200 @@ -141,14 +159,15 @@ module Gitlab # Adds a foreign key with only minimal locking on the tables involved. # - # This method only requires minimal locking when using PostgreSQL. When - # using MySQL this method will use Rails' default `add_foreign_key`. + # This method only requires minimal locking # # source - The source table containing the foreign key. # target - The target table the key points to. # column - The name of the column to create the foreign key on. # on_delete - The action to perform when associated data is removed, # defaults to "CASCADE". + # + # rubocop:disable Gitlab/RailsLogger def add_concurrent_foreign_key(source, target, column:, on_delete: :cascade, name: nil) # Transactions would result in ALTER TABLE locks being held for the # duration of the transaction, defeating the purpose of this method. @@ -156,27 +175,7 @@ module Gitlab raise 'add_concurrent_foreign_key can not be run inside a transaction' end - # While MySQL does allow disabling of foreign keys it has no equivalent - # of PostgreSQL's "VALIDATE CONSTRAINT". As a result we'll just fall - # back to the normal foreign key procedure. - if Database.mysql? - if foreign_key_exists?(source, target, column: column) - Rails.logger.warn "Foreign key not created because it exists already " \ - "(this may be due to an aborted migration or similar): " \ - "source: #{source}, target: #{target}, column: #{column}" - return - end - - key_options = { column: column, on_delete: on_delete } - - # The MySQL adapter tries to create a foreign key without a name when - # `:name` is nil, instead of generating a name for us. - key_options[:name] = name if name - - return add_foreign_key(source, target, key_options) - else - on_delete = 'SET NULL' if on_delete == :nullify - end + on_delete = 'SET NULL' if on_delete == :nullify key_name = name || concurrent_foreign_key_name(source, column) @@ -208,6 +207,7 @@ module Gitlab execute("ALTER TABLE #{source} VALIDATE CONSTRAINT #{key_name};") end end + # rubocop:enable Gitlab/RailsLogger def foreign_key_exists?(source, target = nil, column: nil) foreign_keys(source).any? do |key| @@ -233,7 +233,7 @@ module Gitlab # Long-running migrations may take more than the timeout allowed by # the database. Disable the session's statement timeout to ensure - # migrations don't get killed prematurely. (PostgreSQL only) + # migrations don't get killed prematurely. # # There are two possible ways to disable the statement timeout: # @@ -245,15 +245,6 @@ module Gitlab # otherwise the statement will still be disabled until connection is dropped # or `RESET ALL` is executed def disable_statement_timeout - # bypass disabled_statement logic when not using postgres, but still execute block when one is given - unless Database.postgresql? - if block_given? - yield - end - - return - end - if block_given? begin execute('SET statement_timeout TO 0') @@ -479,7 +470,7 @@ module Gitlab # 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 + change_column_default(table, new, old_col.default) unless old_col.default.nil? install_rename_triggers(table, old, new) @@ -491,6 +482,16 @@ module Gitlab copy_foreign_keys(table, old, new) end + def undo_rename_column_concurrently(table, old, new) + trigger_name = rename_trigger_name(table, old, new) + + check_trigger_permissions!(table) + + remove_rename_triggers_for_postgresql(table, trigger_name) + + remove_column(table, new) + end + # Installs triggers in a table that keep a new column in sync with an old # one. # @@ -503,13 +504,12 @@ module Gitlab quoted_old = quote_column_name(old_column) quoted_new = quote_column_name(new_column) - 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 + install_rename_triggers_for_postgresql( + trigger_name, + quoted_table, + quoted_old, + quoted_new + ) end # Changes the type of a column concurrently. @@ -552,15 +552,40 @@ module Gitlab check_trigger_permissions!(table) - if Database.postgresql? - remove_rename_triggers_for_postgresql(table, trigger_name) - else - remove_rename_triggers_for_mysql(trigger_name) - end + remove_rename_triggers_for_postgresql(table, trigger_name) remove_column(table, old) end + def undo_cleanup_concurrent_column_rename(table, old, new, type: nil) + if transaction_open? + raise 'undo_cleanup_concurrent_column_rename can not be run inside a transaction' + end + + check_trigger_permissions!(table) + + new_column = column_for(table, new) + + add_column(table, old, type || new_column.type, + limit: new_column.limit, + precision: new_column.precision, + scale: new_column.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, old, new_column.default) unless new_column.default.nil? + + install_rename_triggers(table, old, new) + + update_column_in_batches(table, old, Arel::Table.new(table)[new]) + + change_column_null(table, old, false) unless new_column.null + + copy_indexes(table, new, old) + copy_foreign_keys(table, new, old) + end + # Changes the column type of a table using a background migration. # # Because this method uses a background migration it's more suitable for @@ -761,31 +786,16 @@ module Gitlab 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 + DROP TRIGGER IF EXISTS #{trigger} ON #{table} - FOR EACH ROW - SET NEW.#{new} = NEW.#{old} EOF execute <<-EOF.strip_heredoc - CREATE TRIGGER #{trigger}_update - BEFORE UPDATE + CREATE TRIGGER #{trigger} + BEFORE INSERT OR UPDATE ON #{table} FOR EACH ROW - SET NEW.#{new} = NEW.#{old} + EXECUTE PROCEDURE #{trigger}() EOF end @@ -795,12 +805,6 @@ module Gitlab execute("DROP FUNCTION IF EXISTS #{trigger}()") end - # Removes the triggers used for renaming a MySQL column concurrently. - def remove_rename_triggers_for_mysql(trigger) - execute("DROP TRIGGER IF EXISTS #{trigger}_insert") - execute("DROP TRIGGER IF EXISTS #{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) @@ -850,8 +854,6 @@ module Gitlab 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 @@ -891,26 +893,16 @@ module Gitlab end # This will replace the first occurrence 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 + # the replacement using `regexp_replace` 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]) + replace = Arel::Nodes::NamedFunction.new( + "regexp_replace", [column, quoted_pattern, 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 + Arel::Nodes::SqlLiteral.new(replace.to_sql) end def remove_foreign_key_if_exists(*args) @@ -952,11 +944,7 @@ database (#{dbname}) using a super user and running: ALTER #{user} WITH SUPERUSER -For MySQL you instead need to run: - - GRANT ALL PRIVILEGES ON #{dbname}.* TO #{user}@'%' - -Both queries will grant the user super user permissions, ensuring you don't run +This query will grant the user super user permissions, ensuring you don't run into similar problems in the future (e.g. when new tables are created). EOF end @@ -1059,10 +1047,6 @@ into similar problems in the future (e.g. when new tables are created). # This will include indexes using an expression on the column, for example: # `CREATE INDEX CONCURRENTLY index_name ON table (LOWER(column));` # - # For mysql, it falls back to the default ActiveRecord implementation that - # will not find custom indexes. But it will select by name without passing - # a column. - # # We can remove this when upgrading to Rails 5 with an updated `index_exists?`: # - https://github.com/rails/rails/commit/edc2b7718725016e988089b5fb6d6fb9d6e16882 # @@ -1073,10 +1057,8 @@ into similar problems in the future (e.g. when new tables are created). # does not find indexes without passing a column name. if indexes(table).map(&:name).include?(index.to_s) true - elsif Gitlab::Database.postgresql? - postgres_exists_by_name?(table, index) else - false + postgres_exists_by_name?(table, index) end end @@ -1092,8 +1074,26 @@ into similar problems in the future (e.g. when new tables are created). connection.select_value(index_sql).to_i > 0 end - def mysql_compatible_index_length - Gitlab::Database.mysql? ? 20 : nil + private + + def validate_timestamp_column_name!(column_name) + return if PERMITTED_TIMESTAMP_COLUMNS.member?(column_name) + + raise <<~MESSAGE + Illegal timestamp column name! Got #{column_name}. + Must be one of: #{PERMITTED_TIMESTAMP_COLUMNS.to_a} + MESSAGE + end + + def validate_not_in_transaction!(method_name, modifier = nil) + return unless transaction_open? + + raise <<~ERROR + #{["`#{method_name}`", modifier].compact.join(' ')} cannot be run inside a transaction. + + You can disable transactions by calling `disable_ddl_transaction!` in the body of + your migration class + ERROR end end end |