From b326bd51e275cd6a7eace53b5159f5d752f47c40 Mon Sep 17 00:00:00 2001 From: Zhaochen Li Date: Thu, 9 Oct 2025 16:52:15 +1100 Subject: [PATCH 1/3] Swap bigint columns on merge_requests stage one --- ...ge_requests_bigint_conversion_stage_one.rb | 87 ++++++++++ ...xes_and_fks_on_merge_requests_stage_one.rb | 148 ++++++++++++++++++ db/schema_migrations/20251009054629 | 1 + db/schema_migrations/20251009054630 | 1 + 4 files changed, 237 insertions(+) create mode 100644 db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb create mode 100644 db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb create mode 100644 db/schema_migrations/20251009054629 create mode 100644 db/schema_migrations/20251009054630 diff --git a/db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb b/db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb new file mode 100644 index 00000000000000..4c28739fe9f7e0 --- /dev/null +++ b/db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb @@ -0,0 +1,87 @@ +# frozen_string_literal: true + +# This migration does not need to no-op when failed. +# Type validation prevents accidental schema modifications. +class SwapColumnsForMergeRequestsBigintConversionStageOne < Gitlab::Database::Migration[2.3] + include Gitlab::Database::MigrationHelpers::Swapping + include Gitlab::Database::MigrationHelpers::ConvertToBigint + + disable_ddl_transaction! + milestone '18.6' + + TABLE_NAME = 'merge_requests' + COLUMNS = %w[assignee_id merge_user_id updated_by_id milestone_id source_project_id].freeze + + INDEXES = %w[ + index_merge_requests_on_assignee_id + index_merge_requests_on_merge_user_id + index_merge_requests_on_updated_by_id + index_merge_requests_on_milestone_id + idx_merge_requests_on_source_project_and_branch_state_opened + index_merge_requests_on_source_project_id_and_source_branch + ].freeze + + FOREIGN_KEYS = %w[ + fk_6149611a04 + fk_641731faff + fk_6a5165a692 + fk_ad525e1f87 + fk_source_project + ].freeze + + def up + return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('bigint') + + swap + end + + def down + return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('integer') + + swap + end + + def swap + # rubocop:disable Migration/WithLockRetriesDisallowedMethod -- custom implementation + with_lock_retries(raise_on_exhaustion: true) do + COLUMNS.each do |column| + swap_columns(TABLE_NAME, column, convert_to_bigint_column(column)) + end + + reset_all_trigger_functions(TABLE_NAME) + + INDEXES.each do |index| + bigint_idx_name = bigint_index_name(index) + swap_indexes(TABLE_NAME, index, bigint_idx_name) + end + + FOREIGN_KEYS.each do |foreign_key| + bigint_fk_temp_name = tmp_name(foreign_key) + swap_foreign_keys(TABLE_NAME, foreign_key, bigint_fk_temp_name) + end + end + # rubocop:enable Migration/WithLockRetriesDisallowedMethod + end + + def tmp_name(name) + "#{name}_tmp" + end + + def skip_migration_as_bigint_columns_non_exist + unless COLUMNS.all? { |column| column_exists?(TABLE_NAME, convert_to_bigint_column(column)) } + say "No conversion columns found - migration skipped" + return true + end + + false + end + + def skip_migration_as_bigint_columns_type_non_match(column_type) + unless COLUMNS.all? { |column| column_for(TABLE_NAME, convert_to_bigint_column(column)).sql_type == column_type } + say "Columns are converted - migration skipped" + return true + end + + false + end +end diff --git a/db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb b/db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb new file mode 100644 index 00000000000000..ec2270dc2a5105 --- /dev/null +++ b/db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb @@ -0,0 +1,148 @@ +# frozen_string_literal: true + +class DropTmpBigintIndexesAndFksOnMergeRequestsStageOne < Gitlab::Database::Migration[2.3] + include Gitlab::Database::MigrationHelpers::ConvertToBigint + + disable_ddl_transaction! + milestone '18.6' + + TABLE_NAME = 'merge_requests' + COLUMNS = %w[assignee_id merge_user_id updated_by_id milestone_id source_project_id].freeze + + INDEXES = [ + { + name: 'index_merge_requests_on_assignee_id', + columns: [:assignee_id_convert_to_bigint] + }, + { + name: 'index_merge_requests_on_merge_user_id', + columns: [:merge_user_id_convert_to_bigint], + options: { where: "merge_user_id_convert_to_bigint IS NOT NULL" } + }, + { + name: 'index_merge_requests_on_updated_by_id', + columns: [:updated_by_id_convert_to_bigint], + options: { where: "updated_by_id_convert_to_bigint IS NOT NULL" } + }, + { + name: 'index_merge_requests_on_milestone_id', + columns: [:milestone_id_convert_to_bigint] + }, + { + name: 'idx_merge_requests_on_source_project_and_branch_state_opened', + columns: [:source_project_id_convert_to_bigint, :source_branch], + options: { where: "state_id = 1" } + }, + { + name: 'index_merge_requests_on_source_project_id_and_source_branch', + columns: [:source_project_id_convert_to_bigint, :source_branch] + } + ].freeze + + FOREIGN_KEYS = [ + { + source_table: :merge_requests, + column: :assignee_id_convert_to_bigint, + target_table: :users, + target_column: :id, + on_delete: :nullify, + name: :fk_6149611a04 + }, + { + source_table: :merge_requests, + column: :updated_by_id_convert_to_bigint, + target_table: :users, + target_column: :id, + on_delete: :nullify, + name: :fk_641731faff + }, + { + source_table: :merge_requests, + column: :milestone_id_convert_to_bigint, + target_table: :milestones, + target_column: :id, + on_delete: :nullify, + name: :fk_6a5165a692 + }, + { + source_table: :merge_requests, + column: :merge_user_id_convert_to_bigint, + target_table: :users, + target_column: :id, + on_delete: :nullify, + name: :fk_ad525e1f87 + }, + { + source_table: :merge_requests, + column: :source_project_id_convert_to_bigint, + target_table: :projects, + target_column: :id, + on_delete: :nullify, + name: :fk_source_project + } + ].freeze + + def up + return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('integer') + + INDEXES.each do |index| + remove_concurrent_index_by_name(TABLE_NAME, bigint_index_name(index[:name])) + end + + # rubocop:disable Migration/WithLockRetriesDisallowedMethod -- custom implementation + with_lock_retries(raise_on_exhaustion: true) do + FOREIGN_KEYS.each do |foreign_key| + remove_foreign_key_if_exists( + foreign_key[:source_table], + foreign_key[:target_table], + name: tmp_name(foreign_key[:name]) + ) + end + end + # rubocop:enable Migration/WithLockRetriesDisallowedMethod + end + + def down + return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('bigint') + + INDEXES.each do |index| + options = index[:options] || {} + add_concurrent_index TABLE_NAME, index[:columns], name: bigint_index_name(index[:name]), **options + end + + FOREIGN_KEYS.each do |fk| + add_concurrent_foreign_key( + fk[:source_table], + fk[:target_table], + column: fk[:column], + target_column: fk[:target_column], + name: tmp_name(fk[:name]), + on_delete: fk[:on_delete], + validate: true, + reverse_lock_order: true + ) + end + end + + def tmp_name(name) + "#{name}_tmp" + end + + def skip_migration_as_bigint_columns_non_exist + unless COLUMNS.all? { |column| column_exists?(TABLE_NAME, convert_to_bigint_column(column)) } + say "No conversion columns found - migration skipped" + return true + end + + false + end + + def skip_migration_as_bigint_columns_type_non_match(column_type) + unless COLUMNS.all? { |column| column_for(TABLE_NAME, convert_to_bigint_column(column)).sql_type == column_type } + say "Columns are converted - migration skipped" + return true + end + + false + end +end diff --git a/db/schema_migrations/20251009054629 b/db/schema_migrations/20251009054629 new file mode 100644 index 00000000000000..77e8a51b6a7c6b --- /dev/null +++ b/db/schema_migrations/20251009054629 @@ -0,0 +1 @@ +3a4f39cf4690dee075b5f44e38483e4a26a28f7a27523de6e4dee094560fa4df \ No newline at end of file diff --git a/db/schema_migrations/20251009054630 b/db/schema_migrations/20251009054630 new file mode 100644 index 00000000000000..1c2bc556a8b579 --- /dev/null +++ b/db/schema_migrations/20251009054630 @@ -0,0 +1 @@ +be4b18e8b14f0a7483eb59c5904d49a0174dc59b2cde1e76a6485225712b42a5 \ No newline at end of file -- GitLab From a3aab9cffe3696366835acb0969fa3c810dcce21 Mon Sep 17 00:00:00 2001 From: Zhaochen Li Date: Thu, 16 Oct 2025 12:33:54 +1100 Subject: [PATCH 2/3] Address comments --- ...erge_requests_bigint_conversion_stage_one.rb} | 7 +++++++ ...dexes_and_fks_on_merge_requests_stage_one.rb} | 16 +++++++++++++--- db/schema_migrations/20251009054629 | 1 - db/schema_migrations/20251009054630 | 1 - db/schema_migrations/20251016010314 | 1 + db/schema_migrations/20251016010315 | 1 + 6 files changed, 22 insertions(+), 5 deletions(-) rename db/post_migrate/{20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb => 20251016010314_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb} (90%) rename db/post_migrate/{20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb => 20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb} (91%) delete mode 100644 db/schema_migrations/20251009054629 delete mode 100644 db/schema_migrations/20251009054630 create mode 100644 db/schema_migrations/20251016010314 create mode 100644 db/schema_migrations/20251016010315 diff --git a/db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb b/db/post_migrate/20251016010314_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb similarity index 90% rename from db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb rename to db/post_migrate/20251016010314_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb index 4c28739fe9f7e0..c9a5e6b86ef1a7 100644 --- a/db/post_migrate/20251009054629_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb +++ b/db/post_migrate/20251016010314_swap_columns_for_merge_requests_bigint_conversion_stage_one.rb @@ -5,6 +5,7 @@ class SwapColumnsForMergeRequestsBigintConversionStageOne < Gitlab::Database::Migration[2.3] include Gitlab::Database::MigrationHelpers::Swapping include Gitlab::Database::MigrationHelpers::ConvertToBigint + include Gitlab::Database::MigrationHelpers::WraparoundAutovacuum disable_ddl_transaction! milestone '18.6' @@ -42,6 +43,12 @@ def down end def swap + unless can_execute_on?(:merge_requests) + raise StandardError, + "Wraparound prevention vacuum detected on merge_requests table" \ + "Please try again later." + end + # rubocop:disable Migration/WithLockRetriesDisallowedMethod -- custom implementation with_lock_retries(raise_on_exhaustion: true) do COLUMNS.each do |column| diff --git a/db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb b/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb similarity index 91% rename from db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb rename to db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb index ec2270dc2a5105..b4963c8a9b9b42 100644 --- a/db/post_migrate/20251009054630_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb +++ b/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb @@ -2,6 +2,7 @@ class DropTmpBigintIndexesAndFksOnMergeRequestsStageOne < Gitlab::Database::Migration[2.3] include Gitlab::Database::MigrationHelpers::ConvertToBigint + include Gitlab::Database::MigrationHelpers::WraparoundAutovacuum disable_ddl_transaction! milestone '18.6' @@ -83,6 +84,7 @@ class DropTmpBigintIndexesAndFksOnMergeRequestsStageOne < Gitlab::Database::Migr ].freeze def up + vacuum_detection return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('integer') INDEXES.each do |index| @@ -103,7 +105,8 @@ def up end def down - return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('bigint') + vacuum_detection + return if skip_migration_as_bigint_columns_non_exist || skip_migration_as_bigint_columns_type_non_match('integer') INDEXES.each do |index| options = index[:options] || {} @@ -118,8 +121,7 @@ def down target_column: fk[:target_column], name: tmp_name(fk[:name]), on_delete: fk[:on_delete], - validate: true, - reverse_lock_order: true + validate: true ) end end @@ -145,4 +147,12 @@ def skip_migration_as_bigint_columns_type_non_match(column_type) false end + + def vacuum_detection + return if can_execute_on?(:merge_requests) + + raise StandardError, + "Wraparound prevention vacuum detected on merge_requests table" \ + "Please try again later." + end end diff --git a/db/schema_migrations/20251009054629 b/db/schema_migrations/20251009054629 deleted file mode 100644 index 77e8a51b6a7c6b..00000000000000 --- a/db/schema_migrations/20251009054629 +++ /dev/null @@ -1 +0,0 @@ -3a4f39cf4690dee075b5f44e38483e4a26a28f7a27523de6e4dee094560fa4df \ No newline at end of file diff --git a/db/schema_migrations/20251009054630 b/db/schema_migrations/20251009054630 deleted file mode 100644 index 1c2bc556a8b579..00000000000000 --- a/db/schema_migrations/20251009054630 +++ /dev/null @@ -1 +0,0 @@ -be4b18e8b14f0a7483eb59c5904d49a0174dc59b2cde1e76a6485225712b42a5 \ No newline at end of file diff --git a/db/schema_migrations/20251016010314 b/db/schema_migrations/20251016010314 new file mode 100644 index 00000000000000..267b780819c212 --- /dev/null +++ b/db/schema_migrations/20251016010314 @@ -0,0 +1 @@ +472f6d28aca8116b1f5fc6072f6f4cdcf41c72503104e186b1fc65db1949598c \ No newline at end of file diff --git a/db/schema_migrations/20251016010315 b/db/schema_migrations/20251016010315 new file mode 100644 index 00000000000000..b90fe8e4c87cda --- /dev/null +++ b/db/schema_migrations/20251016010315 @@ -0,0 +1 @@ +4a87f1585b6288a912bf6081d7d8bf3e732f32d28951d7cac0f0b0ab95afe4dd \ No newline at end of file -- GitLab From be1241fcd1dfa347db1c711d65ebade7895390b1 Mon Sep 17 00:00:00 2001 From: Zhaochen Li Date: Mon, 20 Oct 2025 13:33:25 +1100 Subject: [PATCH 3/3] Add reverse_lock_order --- ...mp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb b/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb index b4963c8a9b9b42..3cc12eade88e40 100644 --- a/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb +++ b/db/post_migrate/20251016010315_drop_tmp_bigint_indexes_and_fks_on_merge_requests_stage_one.rb @@ -97,7 +97,8 @@ def up remove_foreign_key_if_exists( foreign_key[:source_table], foreign_key[:target_table], - name: tmp_name(foreign_key[:name]) + name: tmp_name(foreign_key[:name]), + reverse_lock_order: true ) end end @@ -121,7 +122,8 @@ def down target_column: fk[:target_column], name: tmp_name(fk[:name]), on_delete: fk[:on_delete], - validate: true + validate: true, + reverse_lock_order: true ) end end -- GitLab