From c1e5fc6298df4875a0a889cbd0b31f57e505d62e Mon Sep 17 00:00:00 2001 From: David Kim Date: Tue, 23 Sep 2025 18:13:06 +1000 Subject: [PATCH 1/4] Add parent foreign keys to merge_requests_merge_data table Changelog: added --- ..._parent_fk_to_merge_requests_merge_data.rb | 28 +++++++++++++++++++ db/schema_migrations/20250923075100 | 1 + db/structure.sql | 9 ++++++ 3 files changed, 38 insertions(+) create mode 100644 db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb create mode 100644 db/schema_migrations/20250923075100 diff --git a/db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb b/db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb new file mode 100644 index 00000000000000..e94cb1fb6e5d79 --- /dev/null +++ b/db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +class AddParentFkToMergeRequestsMergeData < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + disable_ddl_transaction! + milestone '18.5' + + TABLE_NAME = :merge_requests_merge_data + + def up + # NOTE: Foreign keys on partitions have already been created and validated asynchronously in 18.4. + # This migration adds foreign keys to the main partitioned tables. + add_concurrent_partitioned_foreign_key TABLE_NAME, :merge_requests, column: :merge_request_id, on_delete: :cascade + add_concurrent_partitioned_foreign_key TABLE_NAME, :projects, column: :project_id, on_delete: :cascade + add_concurrent_partitioned_foreign_key TABLE_NAME, :users, column: :merge_user_id, on_delete: :nullify + end + + def down + # NOTE: Dropping foreign keys on the parent partitioned table will also remove foreign keys on partitions + # which we created and validated asynchronously + with_lock_retries do + remove_foreign_key TABLE_NAME, column: :merge_request_id + remove_foreign_key TABLE_NAME, column: :project_id + remove_foreign_key TABLE_NAME, column: :merge_user_id + end + end +end diff --git a/db/schema_migrations/20250923075100 b/db/schema_migrations/20250923075100 new file mode 100644 index 00000000000000..0dec5c55bb7ffe --- /dev/null +++ b/db/schema_migrations/20250923075100 @@ -0,0 +1 @@ +4c14b13b839cdccf6cd7cd4f1b508f7868cee49c3627a64b109fb84789f7ee73 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 3243bc8a57e939..0827475a422cfa 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -50044,6 +50044,9 @@ ALTER TABLE ONLY packages_terraform_module_metadata ALTER TABLE ONLY container_registry_protection_tag_rules ADD CONSTRAINT fk_rails_343879fca2 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; +ALTER TABLE merge_requests_merge_data + ADD CONSTRAINT fk_rails_34941e4a91 FOREIGN KEY (merge_user_id) REFERENCES users(id) ON DELETE SET NULL; + ALTER TABLE ONLY group_features ADD CONSTRAINT fk_rails_356514082b FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -50257,6 +50260,9 @@ ALTER TABLE ONLY aws_roles ALTER TABLE ONLY packages_debian_publications ADD CONSTRAINT fk_rails_4fc8ebd03e FOREIGN KEY (distribution_id) REFERENCES packages_debian_project_distributions(id) ON DELETE CASCADE; +ALTER TABLE merge_requests_merge_data + ADD CONSTRAINT fk_rails_4fd2676ef4 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + ALTER TABLE ONLY merge_request_diff_files ADD CONSTRAINT fk_rails_501aa0a391 FOREIGN KEY (merge_request_diff_id) REFERENCES merge_request_diffs(id) ON DELETE CASCADE; @@ -50332,6 +50338,9 @@ ALTER TABLE ONLY packages_debian_project_architectures ALTER TABLE ONLY virtual_registries_container_registry_upstreams ADD CONSTRAINT fk_rails_583c557285 FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE; +ALTER TABLE merge_requests_merge_data + ADD CONSTRAINT fk_rails_593f9b7924 FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE; + ALTER TABLE ONLY analytics_cycle_analytics_group_stages ADD CONSTRAINT fk_rails_5a22f40223 FOREIGN KEY (start_event_label_id) REFERENCES labels(id) ON DELETE CASCADE; -- GitLab From de20f25ef02b1e45bcad626303ab00214cc5383d Mon Sep 17 00:00:00 2001 From: David Kim Date: Wed, 24 Sep 2025 14:35:23 +1000 Subject: [PATCH 2/4] Split foreign key creations --- ...parent_fk_to_merge_requests_merge_data.rb} | 6 +---- ..._parent_fk_to_merge_requests_merge_data.rb | 24 +++++++++++++++++++ ..._parent_fk_to_merge_requests_merge_data.rb | 24 +++++++++++++++++++ db/schema_migrations/20250923075101 | 1 + db/schema_migrations/20250923075102 | 1 + 5 files changed, 51 insertions(+), 5 deletions(-) rename db/post_migrate/{20250923075100_add_parent_fk_to_merge_requests_merge_data.rb => 20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb} (65%) create mode 100644 db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb create mode 100644 db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb create mode 100644 db/schema_migrations/20250923075101 create mode 100644 db/schema_migrations/20250923075102 diff --git a/db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb b/db/post_migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb similarity index 65% rename from db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb rename to db/post_migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb index e94cb1fb6e5d79..a8e6d9360eedf5 100644 --- a/db/post_migrate/20250923075100_add_parent_fk_to_merge_requests_merge_data.rb +++ b/db/post_migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb @@ -1,6 +1,6 @@ # frozen_string_literal: true -class AddParentFkToMergeRequestsMergeData < Gitlab::Database::Migration[2.3] +class AddMergeRequestsParentFkToMergeRequestsMergeData < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers disable_ddl_transaction! @@ -12,8 +12,6 @@ def up # NOTE: Foreign keys on partitions have already been created and validated asynchronously in 18.4. # This migration adds foreign keys to the main partitioned tables. add_concurrent_partitioned_foreign_key TABLE_NAME, :merge_requests, column: :merge_request_id, on_delete: :cascade - add_concurrent_partitioned_foreign_key TABLE_NAME, :projects, column: :project_id, on_delete: :cascade - add_concurrent_partitioned_foreign_key TABLE_NAME, :users, column: :merge_user_id, on_delete: :nullify end def down @@ -21,8 +19,6 @@ def down # which we created and validated asynchronously with_lock_retries do remove_foreign_key TABLE_NAME, column: :merge_request_id - remove_foreign_key TABLE_NAME, column: :project_id - remove_foreign_key TABLE_NAME, column: :merge_user_id end end end diff --git a/db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb b/db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb new file mode 100644 index 00000000000000..9616acfd562709 --- /dev/null +++ b/db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +class AddProjectsParentFkToMergeRequestsMergeData < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + disable_ddl_transaction! + milestone '18.5' + + TABLE_NAME = :merge_requests_merge_data + + def up + # NOTE: Foreign keys on partitions have already been created and validated asynchronously in 18.4. + # This migration adds foreign keys to the main partitioned tables. + add_concurrent_partitioned_foreign_key TABLE_NAME, :projects, column: :project_id, on_delete: :cascade + end + + def down + # NOTE: Dropping foreign keys on the parent partitioned table will also remove foreign keys on partitions + # which we created and validated asynchronously + with_lock_retries do + remove_foreign_key TABLE_NAME, column: :project_id + end + end +end diff --git a/db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb b/db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb new file mode 100644 index 00000000000000..ebcdfeec620778 --- /dev/null +++ b/db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +class AddUsersParentFkToMergeRequestsMergeData < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + disable_ddl_transaction! + milestone '18.5' + + TABLE_NAME = :merge_requests_merge_data + + def up + # NOTE: Foreign keys on partitions have already been created and validated asynchronously in 18.4. + # This migration adds foreign keys to the main partitioned tables. + add_concurrent_partitioned_foreign_key TABLE_NAME, :users, column: :merge_user_id, on_delete: :nullify + end + + def down + # NOTE: Dropping foreign keys on the parent partitioned table will also remove foreign keys on partitions + # which we created and validated asynchronously + with_lock_retries do + remove_foreign_key TABLE_NAME, column: :merge_user_id + end + end +end diff --git a/db/schema_migrations/20250923075101 b/db/schema_migrations/20250923075101 new file mode 100644 index 00000000000000..f4fe9b0e620a15 --- /dev/null +++ b/db/schema_migrations/20250923075101 @@ -0,0 +1 @@ +2e70cc28f6ee906bafbc451566045abdf3602123ea122c5aeed3f617fd7859e8 \ No newline at end of file diff --git a/db/schema_migrations/20250923075102 b/db/schema_migrations/20250923075102 new file mode 100644 index 00000000000000..9c9b0eb9fd1f4f --- /dev/null +++ b/db/schema_migrations/20250923075102 @@ -0,0 +1 @@ +63eb9418c73576fbfc1c214ff03c1c5ae88edc90dc9ffee4d92de77523922031 \ No newline at end of file -- GitLab From 16c3737693c30d0223a106a744794bd72ddbab92 Mon Sep 17 00:00:00 2001 From: David Kim Date: Thu, 25 Sep 2025 13:14:00 +1000 Subject: [PATCH 3/4] Make them into regular migrations --- ...0_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb | 0 ...3075101_add_projects_parent_fk_to_merge_requests_merge_data.rb | 0 ...0923075102_add_users_parent_fk_to_merge_requests_merge_data.rb | 0 3 files changed, 0 insertions(+), 0 deletions(-) rename db/{post_migrate => migrate}/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb (100%) rename db/{post_migrate => migrate}/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb (100%) rename db/{post_migrate => migrate}/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb (100%) diff --git a/db/post_migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb b/db/migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb similarity index 100% rename from db/post_migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb rename to db/migrate/20250923075100_add_merge_requests_parent_fk_to_merge_requests_merge_data.rb diff --git a/db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb b/db/migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb similarity index 100% rename from db/post_migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb rename to db/migrate/20250923075101_add_projects_parent_fk_to_merge_requests_merge_data.rb diff --git a/db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb b/db/migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb similarity index 100% rename from db/post_migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb rename to db/migrate/20250923075102_add_users_parent_fk_to_merge_requests_merge_data.rb -- GitLab From deb3865a88ad29feaa477201ab1bb3f4354f418a Mon Sep 17 00:00:00 2001 From: David Kim Date: Mon, 29 Sep 2025 14:52:38 +1000 Subject: [PATCH 4/4] Remove temporarily skipped FK and sharding key checks --- spec/db/schema_spec.rb | 1 - spec/lib/gitlab/database/sharding_key_spec.rb | 3 +-- 2 files changed, 1 insertion(+), 3 deletions(-) diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index 4780037712a52c..0a17383d385f3a 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -148,7 +148,6 @@ # merge_request_diff_commits_b5377a7a34 is the temporary table for the merge_request_diff_commits partitioning # backfill. It will get foreign keys after the partitioning is finished. merge_request_diff_commits_b5377a7a34: %w[merge_request_diff_id commit_author_id committer_id project_id], - merge_requests_merge_data: %w[merge_request_id project_id merge_user_id], namespaces: %w[owner_id parent_id], namespace_descendants: %w[namespace_id], notes: %w[author_id commit_id noteable_id updated_by_id resolved_by_id confirmed_by_id discussion_id], diff --git a/spec/lib/gitlab/database/sharding_key_spec.rb b/spec/lib/gitlab/database/sharding_key_spec.rb index 2fe1806080bb68..1e0cdf8275e6b9 100644 --- a/spec/lib/gitlab/database/sharding_key_spec.rb +++ b/spec/lib/gitlab/database/sharding_key_spec.rb @@ -119,8 +119,7 @@ 'packages_nuget_symbols.project_id', 'packages_package_files.project_id', 'merge_request_commits_metadata.project_id', - 'sbom_vulnerability_scans.project_id', - 'merge_requests_merge_data.project_id' # This is temporary to fix production incident + 'sbom_vulnerability_scans.project_id' ] end -- GitLab