diff options
Diffstat (limited to 'lib/gitlab/database')
| -rw-r--r-- | lib/gitlab/database/custom_structure.rb | 44 | ||||
| -rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 14 | ||||
| -rw-r--r-- | lib/gitlab/database/partitioning_migration_helpers.rb | 116 | ||||
| -rw-r--r-- | lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb | 140 | ||||
| -rw-r--r-- | lib/gitlab/database/partitioning_migration_helpers/table_management_helpers.rb | 198 | ||||
| -rw-r--r-- | lib/gitlab/database/schema_cleaner.rb | 18 | ||||
| -rw-r--r-- | lib/gitlab/database/schema_helpers.rb | 36 |
7 files changed, 435 insertions, 131 deletions
diff --git a/lib/gitlab/database/custom_structure.rb b/lib/gitlab/database/custom_structure.rb new file mode 100644 index 00000000000..c5a76c5a787 --- /dev/null +++ b/lib/gitlab/database/custom_structure.rb @@ -0,0 +1,44 @@ +# frozen_string_literal: true + +module Gitlab + module Database + class CustomStructure + CUSTOM_DUMP_FILE = 'db/gitlab_structure.sql' + + def dump + File.open(self.class.custom_dump_filepath, 'wb') do |io| + io << "-- this file tracks custom GitLab data, such as foreign keys referencing partitioned tables\n" + io << "-- more details can be found in the issue: https://gitlab.com/gitlab-org/gitlab/-/issues/201872\n" + io << "SET search_path=public;\n\n" + + dump_partitioned_foreign_keys(io) if partitioned_foreign_keys_exist? + end + end + + def self.custom_dump_filepath + Rails.root.join(CUSTOM_DUMP_FILE) + end + + private + + def dump_partitioned_foreign_keys(io) + io << "COPY partitioned_foreign_keys (#{partitioned_fk_columns.join(", ")}) FROM STDIN;\n" + + PartitioningMigrationHelpers::PartitionedForeignKey.find_each do |fk| + io << fk.attributes.values_at(*partitioned_fk_columns).join("\t") << "\n" + end + io << "\\.\n" + end + + def partitioned_foreign_keys_exist? + return false unless PartitioningMigrationHelpers::PartitionedForeignKey.table_exists? + + PartitioningMigrationHelpers::PartitionedForeignKey.exists? + end + + def partitioned_fk_columns + @partitioned_fk_columns ||= PartitioningMigrationHelpers::PartitionedForeignKey.column_names + end + end + end +end diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index 96be057f77e..fd09c31e994 100644 --- a/lib/gitlab/database/migration_helpers.rb +++ b/lib/gitlab/database/migration_helpers.rb @@ -3,6 +3,8 @@ module Gitlab module Database module MigrationHelpers + # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS + MAX_IDENTIFIER_NAME_LENGTH = 63 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 @@ -1209,6 +1211,8 @@ into similar problems in the future (e.g. when new tables are created). # # rubocop:disable Gitlab/RailsLogger def add_check_constraint(table, check, constraint_name, validate: true) + validate_check_constraint_name!(constraint_name) + # Transactions would result in ALTER TABLE locks being held for the # duration of the transaction, defeating the purpose of this method. if transaction_open? @@ -1244,6 +1248,8 @@ into similar problems in the future (e.g. when new tables are created). end def validate_check_constraint(table, constraint_name) + validate_check_constraint_name!(constraint_name) + unless check_constraint_exists?(table, constraint_name) raise missing_schema_object_message(table, "check constraint", constraint_name) end @@ -1256,6 +1262,8 @@ into similar problems in the future (e.g. when new tables are created). end def remove_check_constraint(table, constraint_name) + validate_check_constraint_name!(constraint_name) + # DROP CONSTRAINT requires an EXCLUSIVE lock # Use with_lock_retries to make sure that this will not timeout with_lock_retries do @@ -1330,6 +1338,12 @@ into similar problems in the future (e.g. when new tables are created). private + def validate_check_constraint_name!(constraint_name) + if constraint_name.to_s.length > MAX_IDENTIFIER_NAME_LENGTH + raise "The maximum allowed constraint name is #{MAX_IDENTIFIER_NAME_LENGTH} characters" + end + end + def statement_timeout_disabled? # This is a string of the form "100ms" or "0" when disabled connection.select_value('SHOW statement_timeout') == "0" diff --git a/lib/gitlab/database/partitioning_migration_helpers.rb b/lib/gitlab/database/partitioning_migration_helpers.rb index 55649ebbf8a..881177a195e 100644 --- a/lib/gitlab/database/partitioning_migration_helpers.rb +++ b/lib/gitlab/database/partitioning_migration_helpers.rb @@ -3,120 +3,8 @@ module Gitlab module Database module PartitioningMigrationHelpers - include SchemaHelpers - - def add_partitioned_foreign_key(from_table, to_table, column: nil, primary_key: :id, on_delete: :cascade) - cascade_delete = extract_cascade_option(on_delete) - - update_foreign_keys(from_table, to_table, column, primary_key, cascade_delete) do |current_keys, existing_key, specified_key| - if existing_key.nil? - unless specified_key.save - raise "failed to create foreign key: #{specified_key.errors.full_messages.to_sentence}" - end - - current_keys << specified_key - else - Rails.logger.warn "foreign key not added because it already exists: #{specified_key}" # rubocop:disable Gitlab/RailsLogger - current_keys - end - end - end - - def remove_partitioned_foreign_key(from_table, to_table, column: nil, primary_key: :id) - update_foreign_keys(from_table, to_table, column, primary_key) do |current_keys, existing_key, specified_key| - if existing_key - existing_key.destroy! - current_keys.delete(existing_key) - else - Rails.logger.warn "foreign key not removed because it doesn't exist: #{specified_key}" # rubocop:disable Gitlab/RailsLogger - end - - current_keys - end - end - - def fk_function_name(table) - object_name(table, 'fk_cascade_function') - end - - def fk_trigger_name(table) - object_name(table, 'fk_cascade_trigger') - end - - private - - def fk_from_spec(from_table, to_table, from_column, to_column, cascade_delete) - PartitionedForeignKey.new(from_table: from_table.to_s, to_table: to_table.to_s, from_column: from_column.to_s, - to_column: to_column.to_s, cascade_delete: cascade_delete) - end - - def update_foreign_keys(from_table, to_table, from_column, to_column, cascade_delete = nil) - if transaction_open? - raise 'partitioned foreign key operations can not be run inside a transaction block, ' \ - 'you can disable transaction blocks by calling disable_ddl_transaction! ' \ - 'in the body of your migration class' - end - - from_column ||= "#{to_table.to_s.singularize}_id" - specified_key = fk_from_spec(from_table, to_table, from_column, to_column, cascade_delete) - - current_keys = PartitionedForeignKey.by_referenced_table(to_table).to_a - existing_key = find_existing_key(current_keys, specified_key) - - final_keys = yield current_keys, existing_key, specified_key - - fn_name = fk_function_name(to_table) - trigger_name = fk_trigger_name(to_table) - - with_lock_retries do - drop_trigger(to_table, trigger_name, if_exists: true) - - if final_keys.empty? - drop_function(fn_name, if_exists: true) - else - create_or_replace_fk_function(fn_name, final_keys) - create_function_trigger(trigger_name, fn_name, fires: "AFTER DELETE ON #{to_table}") - end - end - end - - def extract_cascade_option(on_delete) - case on_delete - when :cascade then true - when :nullify then false - else raise ArgumentError, "invalid option #{on_delete} for :on_delete" - end - end - - def with_lock_retries(&block) - Gitlab::Database::WithLockRetries.new({ - klass: self.class, - logger: Gitlab::BackgroundMigration::Logger - }).run(&block) - end - - def find_existing_key(keys, key) - keys.find { |k| k.from_table == key.from_table && k.from_column == key.from_column } - end - - def create_or_replace_fk_function(fn_name, fk_specs) - create_trigger_function(fn_name, replace: true) do - cascade_statements = build_cascade_statements(fk_specs) - cascade_statements << 'RETURN OLD;' - - cascade_statements.join("\n") - end - end - - def build_cascade_statements(foreign_keys) - foreign_keys.map do |fks| - if fks.cascade_delete? - "DELETE FROM #{fks.from_table} WHERE #{fks.from_column} = OLD.#{fks.to_column};" - else - "UPDATE #{fks.from_table} SET #{fks.from_column} = NULL WHERE #{fks.from_column} = OLD.#{fks.to_column};" - end - end - end + include ForeignKeyHelpers + include TableManagementHelpers end end end diff --git a/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb b/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb new file mode 100644 index 00000000000..9e687009cd7 --- /dev/null +++ b/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb @@ -0,0 +1,140 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module PartitioningMigrationHelpers + module ForeignKeyHelpers + include ::Gitlab::Database::SchemaHelpers + + # Creates a "foreign key" that references a partitioned table. Because foreign keys referencing partitioned + # tables are not supported in PG11, this does not create a true database foreign key, but instead implements the + # same functionality at the database level by using triggers. + # + # Example: + # + # add_partitioned_foreign_key :issues, :projects + # + # Available options: + # + # :column - name of the referencing column (otherwise inferred from the referenced table name) + # :primary_key - name of the primary key in the referenced table (defaults to id) + # :on_delete - supports either :cascade for ON DELETE CASCADE or :nullify for ON DELETE SET NULL + # + def add_partitioned_foreign_key(from_table, to_table, column: nil, primary_key: :id, on_delete: :cascade) + cascade_delete = extract_cascade_option(on_delete) + + update_foreign_keys(from_table, to_table, column, primary_key, cascade_delete) do |current_keys, existing_key, specified_key| + if existing_key.nil? + unless specified_key.save + raise "failed to create foreign key: #{specified_key.errors.full_messages.to_sentence}" + end + + current_keys << specified_key + else + Rails.logger.warn "foreign key not added because it already exists: #{specified_key}" # rubocop:disable Gitlab/RailsLogger + current_keys + end + end + end + + # Drops a "foreign key" that references a partitioned table. This method ONLY applies to foreign keys previously + # created through the `add_partitioned_foreign_key` method. Standard database foreign keys should be managed + # through the familiar Rails helpers. + # + # Example: + # + # remove_partitioned_foreign_key :issues, :projects + # + # Available options: + # + # :column - name of the referencing column (otherwise inferred from the referenced table name) + # :primary_key - name of the primary key in the referenced table (defaults to id) + # + def remove_partitioned_foreign_key(from_table, to_table, column: nil, primary_key: :id) + update_foreign_keys(from_table, to_table, column, primary_key) do |current_keys, existing_key, specified_key| + if existing_key + existing_key.delete + current_keys.delete(existing_key) + else + Rails.logger.warn "foreign key not removed because it doesn't exist: #{specified_key}" # rubocop:disable Gitlab/RailsLogger + end + + current_keys + end + end + + private + + def fk_function_name(table) + object_name(table, 'fk_cascade_function') + end + + def fk_trigger_name(table) + object_name(table, 'fk_cascade_trigger') + end + + def fk_from_spec(from_table, to_table, from_column, to_column, cascade_delete) + PartitionedForeignKey.new(from_table: from_table.to_s, to_table: to_table.to_s, from_column: from_column.to_s, + to_column: to_column.to_s, cascade_delete: cascade_delete) + end + + def update_foreign_keys(from_table, to_table, from_column, to_column, cascade_delete = nil) + assert_not_in_transaction_block(scope: 'partitioned foreign key') + + from_column ||= "#{to_table.to_s.singularize}_id" + specified_key = fk_from_spec(from_table, to_table, from_column, to_column, cascade_delete) + + current_keys = PartitionedForeignKey.by_referenced_table(to_table).to_a + existing_key = find_existing_key(current_keys, specified_key) + + final_keys = yield current_keys, existing_key, specified_key + + fn_name = fk_function_name(to_table) + trigger_name = fk_trigger_name(to_table) + + with_lock_retries do + drop_trigger(to_table, trigger_name, if_exists: true) + + if final_keys.empty? + drop_function(fn_name, if_exists: true) + else + create_or_replace_fk_function(fn_name, final_keys) + create_trigger(trigger_name, fn_name, fires: "AFTER DELETE ON #{to_table}") + end + end + end + + def extract_cascade_option(on_delete) + case on_delete + when :cascade then true + when :nullify then false + else raise ArgumentError, "invalid option #{on_delete} for :on_delete" + end + end + + def find_existing_key(keys, key) + keys.find { |k| k.from_table == key.from_table && k.from_column == key.from_column } + end + + def create_or_replace_fk_function(fn_name, fk_specs) + create_trigger_function(fn_name, replace: true) do + cascade_statements = build_cascade_statements(fk_specs) + cascade_statements << 'RETURN OLD;' + + cascade_statements.join("\n") + end + end + + def build_cascade_statements(foreign_keys) + foreign_keys.map do |fks| + if fks.cascade_delete? + "DELETE FROM #{fks.from_table} WHERE #{fks.from_column} = OLD.#{fks.to_column};" + else + "UPDATE #{fks.from_table} SET #{fks.from_column} = NULL WHERE #{fks.from_column} = OLD.#{fks.to_column};" + end + end + end + end + end + end +end diff --git a/lib/gitlab/database/partitioning_migration_helpers/table_management_helpers.rb b/lib/gitlab/database/partitioning_migration_helpers/table_management_helpers.rb new file mode 100644 index 00000000000..f77fbe98df1 --- /dev/null +++ b/lib/gitlab/database/partitioning_migration_helpers/table_management_helpers.rb @@ -0,0 +1,198 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module PartitioningMigrationHelpers + module TableManagementHelpers + include ::Gitlab::Database::SchemaHelpers + + WHITELISTED_TABLES = %w[audit_events].freeze + ERROR_SCOPE = 'table partitioning' + + # Creates a partitioned copy of an existing table, using a RANGE partitioning strategy on a timestamp column. + # One partition is created per month between the given `min_date` and `max_date`. + # + # A copy of the original table is required as PG currently does not support partitioning existing tables. + # + # Example: + # + # partition_table_by_date :audit_events, :created_at, min_date: Date.new(2020, 1), max_date: Date.new(2020, 6) + # + # Required options are: + # :min_date - a date specifying the lower bounds of the partition range + # :max_date - a date specifying the upper bounds of the partitioning range + # + def partition_table_by_date(table_name, column_name, min_date:, max_date:) + assert_table_is_whitelisted(table_name) + assert_not_in_transaction_block(scope: ERROR_SCOPE) + + raise "max_date #{max_date} must be greater than min_date #{min_date}" if min_date >= max_date + + primary_key = connection.primary_key(table_name) + raise "primary key not defined for #{table_name}" if primary_key.nil? + + partition_column = find_column_definition(table_name, column_name) + raise "partition column #{column_name} does not exist on #{table_name}" if partition_column.nil? + + new_table_name = partitioned_table_name(table_name) + create_range_partitioned_copy(new_table_name, table_name, partition_column, primary_key) + create_daterange_partitions(new_table_name, partition_column.name, min_date, max_date) + create_sync_trigger(table_name, new_table_name, primary_key) + end + + # Clean up a partitioned copy of an existing table. This deletes the partitioned table and all partitions. + # + # Example: + # + # drop_partitioned_table_for :audit_events + # + def drop_partitioned_table_for(table_name) + assert_table_is_whitelisted(table_name) + assert_not_in_transaction_block(scope: ERROR_SCOPE) + + with_lock_retries do + trigger_name = sync_trigger_name(table_name) + drop_trigger(table_name, trigger_name) + end + + function_name = sync_function_name(table_name) + drop_function(function_name) + + part_table_name = partitioned_table_name(table_name) + drop_table(part_table_name) + end + + private + + def assert_table_is_whitelisted(table_name) + return if WHITELISTED_TABLES.include?(table_name.to_s) + + raise "partitioning helpers are in active development, and #{table_name} is not whitelisted for use, " \ + "for more information please contact the database team" + end + + def partitioned_table_name(table) + tmp_table_name("#{table}_part") + end + + def sync_function_name(table) + object_name(table, 'table_sync_function') + end + + def sync_trigger_name(table) + object_name(table, 'table_sync_trigger') + end + + def find_column_definition(table, column) + connection.columns(table).find { |c| c.name == column.to_s } + end + + def create_range_partitioned_copy(table_name, template_table_name, partition_column, primary_key) + if table_exists?(table_name) + # rubocop:disable Gitlab/RailsLogger + Rails.logger.warn "Partitioned table not created because it already exists" \ + " (this may be due to an aborted migration or similar): table_name: #{table_name} " + # rubocop:enable Gitlab/RailsLogger + return + end + + tmp_column_name = object_name(partition_column.name, 'partition_key') + transaction do + execute(<<~SQL) + CREATE TABLE #{table_name} ( + LIKE #{template_table_name} INCLUDING ALL EXCLUDING INDEXES, + #{tmp_column_name} #{partition_column.sql_type} NOT NULL, + PRIMARY KEY (#{[primary_key, tmp_column_name].join(", ")}) + ) PARTITION BY RANGE (#{tmp_column_name}) + SQL + + remove_column(table_name, partition_column.name) + rename_column(table_name, tmp_column_name, partition_column.name) + change_column_default(table_name, primary_key, nil) + + if column_of_type?(table_name, primary_key, :integer) + # Default to int8 primary keys to prevent overflow + change_column(table_name, primary_key, :bigint) + end + end + end + + def column_of_type?(table_name, column, type) + find_column_definition(table_name, column).type == type + end + + def create_daterange_partitions(table_name, column_name, min_date, max_date) + min_date = min_date.beginning_of_month.to_date + max_date = max_date.next_month.beginning_of_month.to_date + + create_range_partition_safely("#{table_name}_000000", table_name, 'MINVALUE', to_sql_date_literal(min_date)) + + while min_date < max_date + partition_name = "#{table_name}_#{min_date.strftime('%Y%m')}" + next_date = min_date.next_month + lower_bound = to_sql_date_literal(min_date) + upper_bound = to_sql_date_literal(next_date) + + create_range_partition_safely(partition_name, table_name, lower_bound, upper_bound) + min_date = next_date + end + end + + def to_sql_date_literal(date) + connection.quote(date.strftime('%Y-%m-%d')) + end + + def create_range_partition_safely(partition_name, table_name, lower_bound, upper_bound) + if table_exists?(partition_name) + # rubocop:disable Gitlab/RailsLogger + Rails.logger.warn "Partition not created because it already exists" \ + " (this may be due to an aborted migration or similar): partition_name: #{partition_name}" + # rubocop:enable Gitlab/RailsLogger + return + end + + create_range_partition(partition_name, table_name, lower_bound, upper_bound) + end + + def create_sync_trigger(source_table, target_table, unique_key) + function_name = sync_function_name(source_table) + trigger_name = sync_trigger_name(source_table) + + with_lock_retries do + create_sync_function(function_name, target_table, unique_key) + create_comment('FUNCTION', function_name, "Partitioning migration: table sync for #{source_table} table") + + create_trigger(trigger_name, function_name, fires: "AFTER INSERT OR UPDATE OR DELETE ON #{source_table}") + end + end + + def create_sync_function(name, target_table, unique_key) + delimiter = ",\n " + column_names = connection.columns(target_table).map(&:name) + set_statements = build_set_statements(column_names, unique_key) + insert_values = column_names.map { |name| "NEW.#{name}" } + + create_trigger_function(name, replace: false) do + <<~SQL + IF (TG_OP = 'DELETE') THEN + DELETE FROM #{target_table} where #{unique_key} = OLD.#{unique_key}; + ELSIF (TG_OP = 'UPDATE') THEN + UPDATE #{target_table} + SET #{set_statements.join(delimiter)} + WHERE #{target_table}.#{unique_key} = NEW.#{unique_key}; + ELSIF (TG_OP = 'INSERT') THEN + INSERT INTO #{target_table} (#{column_names.join(delimiter)}) + VALUES (#{insert_values.join(delimiter)}); + END IF; + RETURN NULL; + SQL + end + end + + def build_set_statements(column_names, unique_key) + column_names.reject { |name| name == unique_key }.map { |column_name| "#{column_name} = NEW.#{column_name}" } + end + end + end + end +end diff --git a/lib/gitlab/database/schema_cleaner.rb b/lib/gitlab/database/schema_cleaner.rb index c1436d3e7ca..ae9d77e635e 100644 --- a/lib/gitlab/database/schema_cleaner.rb +++ b/lib/gitlab/database/schema_cleaner.rb @@ -12,27 +12,15 @@ module Gitlab def clean(io) structure = original_schema.dup - # Postgres compat fix for PG 9.6 (which doesn't support (AS datatype) syntax for sequences) - structure.gsub!(/CREATE SEQUENCE [^.]+\.\S+\n(\s+AS integer\n)/) { |m| m.gsub(Regexp.last_match[1], '') } - - # Also a PG 9.6 compatibility fix, see below. - structure.gsub!(/^CREATE EXTENSION IF NOT EXISTS plpgsql.*/, '') - structure.gsub!(/^COMMENT ON EXTENSION.*/, '') - # Remove noise + structure.gsub!(/^COMMENT ON EXTENSION.*/, '') structure.gsub!(/^SET.+/, '') structure.gsub!(/^SELECT pg_catalog\.set_config\('search_path'.+/, '') structure.gsub!(/^--.*/, "\n") - structure.gsub!(/\n{3,}/, "\n\n") - io << "SET search_path=public;\n\n" + structure = "SET search_path=public;\n" + structure - # Adding plpgsql explicitly is again a compatibility fix for PG 9.6 - # In more recent versions of pg_dump, the extension isn't explicitly dumped anymore. - # We use PG 9.6 still on CI and for schema checks - here this is still the case. - io << <<~SQL.strip - CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; - SQL + structure.gsub!(/\n{3,}/, "\n\n") io << structure diff --git a/lib/gitlab/database/schema_helpers.rb b/lib/gitlab/database/schema_helpers.rb index f8d01c78ae8..8e544307d81 100644 --- a/lib/gitlab/database/schema_helpers.rb +++ b/lib/gitlab/database/schema_helpers.rb @@ -16,12 +16,12 @@ module Gitlab SQL end - def create_function_trigger(name, fn_name, fires: nil) + def create_trigger(name, function_name, fires: nil) execute(<<~SQL) CREATE TRIGGER #{name} #{fires} FOR EACH ROW - EXECUTE PROCEDURE #{fn_name}() + EXECUTE PROCEDURE #{function_name}() SQL end @@ -35,6 +35,16 @@ module Gitlab execute("DROP TRIGGER #{exists_clause} #{name} ON #{table_name}") end + def create_comment(type, name, text) + execute("COMMENT ON #{type} #{name} IS '#{text}'") + end + + def tmp_table_name(base) + hashed_base = Digest::SHA256.hexdigest(base).first(10) + + "#{base}_#{hashed_base}" + end + def object_name(table, type) identifier = "#{table}_#{type}" hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10) @@ -42,8 +52,30 @@ module Gitlab "#{type}_#{hashed_identifier}" end + def with_lock_retries(&block) + Gitlab::Database::WithLockRetries.new({ + klass: self.class, + logger: Gitlab::BackgroundMigration::Logger + }).run(&block) + end + + def assert_not_in_transaction_block(scope:) + return unless transaction_open? + + raise "#{scope} operations can not be run inside a transaction block, " \ + "you can disable transaction blocks by calling disable_ddl_transaction! " \ + "in the body of your migration class" + end + private + def create_range_partition(partition_name, table_name, lower_bound, upper_bound) + execute(<<~SQL) + CREATE TABLE #{partition_name} PARTITION OF #{table_name} + FOR VALUES FROM (#{lower_bound}) TO (#{upper_bound}) + SQL + end + def optional_clause(flag, clause) flag ? clause : "" end |