From c50a3a6dd18fba2546e527d21ad4c4dbe384efdb Mon Sep 17 00:00:00 2001 From: hustewart Date: Wed, 15 Oct 2025 17:04:28 -0400 Subject: [PATCH] Replace `shard_id` FK with LFK for snippet_repositories Contributes to https://gitlab.com/gitlab-org/gitlab/-/issues/575428 **Problem** When an organization's PG data is moved from one cell to another, the snippet_repositories.shard_id will be nullified, to be set with a new shard_id that is applicable to the new cell. **Solution** Replace FK with LFK for `shard_id` Changelog: changed --- config/gitlab_loose_foreign_keys.yml | 4 ++++ ...120211937_track_shards_record_deletions.rb | 15 +++++++++++++ ...shards_snippet_repositories_shard_id_fk.rb | 21 +++++++++++++++++++ db/schema_migrations/20251120211937 | 1 + db/schema_migrations/20251120211938 | 1 + db/structure.sql | 5 ++--- spec/models/snippet_repository_spec.rb | 7 +++++++ 7 files changed, 51 insertions(+), 3 deletions(-) create mode 100644 db/post_migrate/20251120211937_track_shards_record_deletions.rb create mode 100644 db/post_migrate/20251120211938_remove_shards_snippet_repositories_shard_id_fk.rb create mode 100644 db/schema_migrations/20251120211937 create mode 100644 db/schema_migrations/20251120211938 diff --git a/config/gitlab_loose_foreign_keys.yml b/config/gitlab_loose_foreign_keys.yml index faa24f1e2723a3..1a5156a97c448f 100644 --- a/config/gitlab_loose_foreign_keys.yml +++ b/config/gitlab_loose_foreign_keys.yml @@ -882,6 +882,10 @@ slsa_attestations: column: build_id on_delete: async_delete worker_class: LooseForeignKeys::CiPipelinesBuildsCleanupCronWorker +snippet_repositories: + - table: shards + column: shard_id + on_delete: async_nullify snippets: - table: organizations column: organization_id diff --git a/db/post_migrate/20251120211937_track_shards_record_deletions.rb b/db/post_migrate/20251120211937_track_shards_record_deletions.rb new file mode 100644 index 00000000000000..e3869762cbfa48 --- /dev/null +++ b/db/post_migrate/20251120211937_track_shards_record_deletions.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class TrackShardsRecordDeletions < Gitlab::Database::Migration[2.3] + include Gitlab::Database::MigrationHelpers::LooseForeignKeyHelpers + + milestone '18.7' + + def up + track_record_deletions(:shards) + end + + def down + untrack_record_deletions(:shards) + end +end diff --git a/db/post_migrate/20251120211938_remove_shards_snippet_repositories_shard_id_fk.rb b/db/post_migrate/20251120211938_remove_shards_snippet_repositories_shard_id_fk.rb new file mode 100644 index 00000000000000..dce8332c2878e1 --- /dev/null +++ b/db/post_migrate/20251120211938_remove_shards_snippet_repositories_shard_id_fk.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class RemoveShardsSnippetRepositoriesShardIdFk < Gitlab::Database::Migration[2.3] + milestone '18.7' + disable_ddl_transaction! + + FOREIGN_KEY_NAME = "fk_rails_f21f899728" + + def up + with_lock_retries do + remove_foreign_key_if_exists(:snippet_repositories, :shards, + name: FOREIGN_KEY_NAME, reverse_lock_order: true) + end + end + + def down + add_concurrent_foreign_key(:snippet_repositories, :shards, + name: FOREIGN_KEY_NAME, column: :shard_id, + target_column: :id, on_delete: :restrict) + end +end diff --git a/db/schema_migrations/20251120211937 b/db/schema_migrations/20251120211937 new file mode 100644 index 00000000000000..f6f690a50065b7 --- /dev/null +++ b/db/schema_migrations/20251120211937 @@ -0,0 +1 @@ +37e606811330be1526e070f8f84f71ca068f6688e2fce36104439038494435e3 \ No newline at end of file diff --git a/db/schema_migrations/20251120211938 b/db/schema_migrations/20251120211938 new file mode 100644 index 00000000000000..a64ff3aedfee10 --- /dev/null +++ b/db/schema_migrations/20251120211938 @@ -0,0 +1 @@ +29916a6fc3e48222a85e8b4fcaa736b67ec6349f7c94f09196ee3ced6e38c8d8 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 39a58ec5708501..cea90ac93c066d 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -49160,6 +49160,8 @@ CREATE TRIGGER set_sharding_key_for_note_metadata_on_insert_and_update BEFORE IN CREATE TRIGGER set_sharding_key_for_suggestions_on_insert_and_update BEFORE INSERT OR UPDATE ON suggestions FOR EACH ROW EXECUTE FUNCTION sync_sharding_key_with_notes_table(); +CREATE TRIGGER shards_loose_fk_trigger AFTER DELETE ON shards REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); + CREATE TRIGGER slsa_attestations_loose_fk_trigger AFTER DELETE ON slsa_attestations REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); CREATE TRIGGER sync_project_authorizations_to_migration AFTER INSERT OR DELETE OR UPDATE ON project_authorizations FOR EACH ROW EXECUTE FUNCTION sync_project_authorizations_to_migration_table(); @@ -54429,9 +54431,6 @@ ALTER TABLE ONLY jira_connect_subscriptions ALTER TABLE ONLY requirements ADD CONSTRAINT fk_rails_f212e67e63 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; -ALTER TABLE ONLY snippet_repositories - ADD CONSTRAINT fk_rails_f21f899728 FOREIGN KEY (shard_id) REFERENCES shards(id) ON DELETE RESTRICT; - ALTER TABLE ONLY elastic_reindexing_subtasks ADD CONSTRAINT fk_rails_f2cc190164 FOREIGN KEY (elastic_reindexing_task_id) REFERENCES elastic_reindexing_tasks(id) ON DELETE CASCADE; diff --git a/spec/models/snippet_repository_spec.rb b/spec/models/snippet_repository_spec.rb index 79ec37925f6c5f..75f46e41413dc1 100644 --- a/spec/models/snippet_repository_spec.rb +++ b/spec/models/snippet_repository_spec.rb @@ -441,4 +441,11 @@ def blob_at(snippet, path) def first_blob(snippet) snippet.repository.blob_at('master', snippet.repository.ls_files(snippet.default_branch).first) end + + context 'with loose foreign key on snippet_repositories.shard_id' do + it_behaves_like 'cleanup by a loose foreign key' do + let_it_be(:parent) { create(:shard) } + let_it_be(:model) { create(:snippet_repository, shard: parent) } + end + end end -- GitLab