diff options
Diffstat (limited to 'lib/gitlab/database')
| -rw-r--r-- | lib/gitlab/database/count.rb | 12 | ||||
| -rw-r--r-- | lib/gitlab/database/count/exact_count_strategy.rb | 4 | ||||
| -rw-r--r-- | lib/gitlab/database/count/reltuples_count_strategy.rb | 4 | ||||
| -rw-r--r-- | lib/gitlab/database/count/tablesample_count_strategy.rb | 4 | ||||
| -rw-r--r-- | lib/gitlab/database/date_time.rb | 18 | ||||
| -rw-r--r-- | lib/gitlab/database/median.rb | 45 | ||||
| -rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 203 | ||||
| -rw-r--r-- | lib/gitlab/database/rename_reserved_paths_migration/v1/rename_base.rb | 12 |
8 files changed, 96 insertions, 206 deletions
diff --git a/lib/gitlab/database/count.rb b/lib/gitlab/database/count.rb index f3d37ccd72a..eac61254bdf 100644 --- a/lib/gitlab/database/count.rb +++ b/lib/gitlab/database/count.rb @@ -37,16 +37,14 @@ module Gitlab # @return [Hash] of Model -> count mapping def self.approximate_counts(models, strategies: [TablesampleCountStrategy, ReltuplesCountStrategy, ExactCountStrategy]) strategies.each_with_object({}) do |strategy, counts_by_model| - if strategy.enabled? - models_with_missing_counts = models - counts_by_model.keys + models_with_missing_counts = models - counts_by_model.keys - break counts_by_model if models_with_missing_counts.empty? + break counts_by_model if models_with_missing_counts.empty? - counts = strategy.new(models_with_missing_counts).count + counts = strategy.new(models_with_missing_counts).count - counts.each do |model, count| - counts_by_model[model] = count - end + counts.each do |model, count| + counts_by_model[model] = count end end end diff --git a/lib/gitlab/database/count/exact_count_strategy.rb b/lib/gitlab/database/count/exact_count_strategy.rb index fa6951eda22..0b8fe640bf8 100644 --- a/lib/gitlab/database/count/exact_count_strategy.rb +++ b/lib/gitlab/database/count/exact_count_strategy.rb @@ -23,10 +23,6 @@ module Gitlab rescue *CONNECTION_ERRORS {} end - - def self.enabled? - true - end end end end diff --git a/lib/gitlab/database/count/reltuples_count_strategy.rb b/lib/gitlab/database/count/reltuples_count_strategy.rb index 695f6fa766e..6cd90c01ab2 100644 --- a/lib/gitlab/database/count/reltuples_count_strategy.rb +++ b/lib/gitlab/database/count/reltuples_count_strategy.rb @@ -31,10 +31,6 @@ module Gitlab {} end - def self.enabled? - Gitlab::Database.postgresql? - end - private # Models using single-type inheritance (STI) don't work with diff --git a/lib/gitlab/database/count/tablesample_count_strategy.rb b/lib/gitlab/database/count/tablesample_count_strategy.rb index 7777f31f702..e9387a91a14 100644 --- a/lib/gitlab/database/count/tablesample_count_strategy.rb +++ b/lib/gitlab/database/count/tablesample_count_strategy.rb @@ -28,10 +28,6 @@ module Gitlab {} end - def self.enabled? - Gitlab::Database.postgresql? && Feature.enabled?(:tablesample_counts) - end - private def perform_count(model, estimate) diff --git a/lib/gitlab/database/date_time.rb b/lib/gitlab/database/date_time.rb index 79d2caff151..1392b397012 100644 --- a/lib/gitlab/database/date_time.rb +++ b/lib/gitlab/database/date_time.rb @@ -7,8 +7,7 @@ module Gitlab # the first of the `start_time_attrs` that isn't NULL. `SELECT` the resulting interval # along with an alias specified by the `as` parameter. # - # Note: For MySQL, the interval is returned in seconds. - # For PostgreSQL, the interval is returned as an INTERVAL type. + # Note: the interval is returned as an INTERVAL type. def subtract_datetimes(query_so_far, start_time_attrs, end_time_attrs, as) diff_fn = subtract_datetimes_diff(query_so_far, start_time_attrs, end_time_attrs) @@ -16,17 +15,10 @@ module Gitlab end def subtract_datetimes_diff(query_so_far, start_time_attrs, end_time_attrs) - if Gitlab::Database.postgresql? - Arel::Nodes::Subtraction.new( - Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs)), - Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs))) - elsif Gitlab::Database.mysql? - Arel::Nodes::NamedFunction.new( - "TIMESTAMPDIFF", - [Arel.sql('second'), - Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs)), - Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs))]) - end + Arel::Nodes::Subtraction.new( + Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(end_time_attrs)), + Arel::Nodes::NamedFunction.new("COALESCE", Array.wrap(start_time_attrs)) + ) end end end diff --git a/lib/gitlab/database/median.rb b/lib/gitlab/database/median.rb index b8d895dee7d..391c1e85a7d 100644 --- a/lib/gitlab/database/median.rb +++ b/lib/gitlab/database/median.rb @@ -17,13 +17,9 @@ module Gitlab def extract_median(results) result = results.compact.first - if Gitlab::Database.postgresql? - result = result.first.presence + result = result.first.presence - result['median']&.to_f if result - elsif Gitlab::Database.mysql? - result.to_a.flatten.first - end + result['median']&.to_f if result end def extract_medians(results) @@ -34,31 +30,6 @@ module Gitlab end end - def mysql_median_datetime_sql(arel_table, query_so_far, column_sym) - query = arel_table.from - .from(arel_table.project(Arel.sql('*')).order(arel_table[column_sym]).as(arel_table.table_name)) - .project(average([arel_table[column_sym]], 'median')) - .where( - Arel::Nodes::Between.new( - Arel.sql("(select @row_id := @row_id + 1)"), - Arel::Nodes::And.new( - [Arel.sql('@ct/2.0'), - Arel.sql('@ct/2.0 + 1')] - ) - ) - ). - # Disallow negative values - where(arel_table[column_sym].gteq(0)) - - [ - Arel.sql("CREATE TEMPORARY TABLE IF NOT EXISTS #{query_so_far.to_sql}"), - Arel.sql("set @ct := (select count(1) from #{arel_table.table_name});"), - Arel.sql("set @row_id := 0;"), - query.to_sql, - Arel.sql("DROP TEMPORARY TABLE IF EXISTS #{arel_table.table_name};") - ] - end - def pg_median_datetime_sql(arel_table, query_so_far, column_sym, partition_column = nil) # Create a CTE with the column we're operating on, row number (after sorting by the column # we're operating on), and count of the table we're operating on (duplicated across) all rows @@ -113,18 +84,8 @@ module Gitlab private - def median_queries(arel_table, query_so_far, column_sym, partition_column = nil) - if Gitlab::Database.postgresql? - pg_median_datetime_sql(arel_table, query_so_far, column_sym, partition_column) - elsif Gitlab::Database.mysql? - raise NotSupportedError, "partition_column is not supported for MySQL" if partition_column - - mysql_median_datetime_sql(arel_table, query_so_far, column_sym) - end - end - def execute_queries(arel_table, query_so_far, column_sym, partition_column = nil) - queries = median_queries(arel_table, query_so_far, column_sym, partition_column) + queries = pg_median_datetime_sql(arel_table, query_so_far, column_sym, partition_column) Array.wrap(queries).map { |query| ActiveRecord::Base.connection.execute(query) } end diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index 0c5f33e1b2a..4bd09163bf2 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,9 +82,7 @@ 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}" # rubocop:disable Gitlab/RailsLogger @@ -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: # @@ -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: # @@ -141,8 +161,7 @@ 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. @@ -158,27 +177,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) @@ -236,7 +235,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: # @@ -248,15 +247,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') @@ -506,13 +496,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. @@ -555,11 +544,7 @@ 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 @@ -772,38 +757,12 @@ module Gitlab 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 IF EXISTS #{trigger} ON #{table}") 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) @@ -853,8 +812,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 @@ -894,26 +851,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) @@ -955,11 +902,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 @@ -1062,10 +1005,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 # @@ -1076,10 +1015,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 @@ -1095,8 +1032,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 diff --git a/lib/gitlab/database/rename_reserved_paths_migration/v1/rename_base.rb b/lib/gitlab/database/rename_reserved_paths_migration/v1/rename_base.rb index 60afa4bcd52..565f34b78b7 100644 --- a/lib/gitlab/database/rename_reserved_paths_migration/v1/rename_base.rb +++ b/lib/gitlab/database/rename_reserved_paths_migration/v1/rename_base.rb @@ -51,14 +51,10 @@ module Gitlab quoted_old_full_path = quote_string(old_full_path) quoted_old_wildcard_path = quote_string("#{old_full_path}/%") - filter = if Database.mysql? - "lower(routes.path) = lower('#{quoted_old_full_path}') "\ - "OR routes.path LIKE '#{quoted_old_wildcard_path}'" - else - "routes.id IN "\ - "( SELECT routes.id FROM routes WHERE lower(routes.path) = lower('#{quoted_old_full_path}') "\ - "UNION SELECT routes.id FROM routes WHERE routes.path ILIKE '#{quoted_old_wildcard_path}' )" - end + filter = + "routes.id IN "\ + "( SELECT routes.id FROM routes WHERE lower(routes.path) = lower('#{quoted_old_full_path}') "\ + "UNION SELECT routes.id FROM routes WHERE routes.path ILIKE '#{quoted_old_wildcard_path}' )" replace_statement = replace_sql(Route.arel_table[:path], old_full_path, |