From f4eae5470f2a7be701974550f24a3ad5092ea184 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Tue, 7 Oct 2025 13:43:46 -0400 Subject: [PATCH 1/3] Create trigger for achievement_uploads Addresses https://gitlab.com/gitlab-org/gitlab/-/issues/398199 Changelog: other --- ...ding_key_trigger_on_achievement_uploads.rb | 31 +++++++++++++++++++ db/schema_migrations/20251007172025 | 1 + db/structure.sql | 18 +++++++++++ 3 files changed, 50 insertions(+) create mode 100644 db/migrate/20251007172025_add_sharding_key_trigger_on_achievement_uploads.rb create mode 100644 db/schema_migrations/20251007172025 diff --git a/db/migrate/20251007172025_add_sharding_key_trigger_on_achievement_uploads.rb b/db/migrate/20251007172025_add_sharding_key_trigger_on_achievement_uploads.rb new file mode 100644 index 00000000000000..17679bd74580b2 --- /dev/null +++ b/db/migrate/20251007172025_add_sharding_key_trigger_on_achievement_uploads.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +class AddShardingKeyTriggerOnAchievementUploads < Gitlab::Database::Migration[2.3] + milestone '18.6' + + TABLE_NAME = :achievement_uploads + SHARDING_KEY = :namespace_id + PARENT_TABLE = :achievements + PARENT_SHARDING_KEY = :namespace_id + FOREIGN_KEY = :model_id + + def up + install_sharding_key_assignment_trigger( + table: TABLE_NAME, + sharding_key: SHARDING_KEY, + parent_table: PARENT_TABLE, + parent_sharding_key: PARENT_SHARDING_KEY, + foreign_key: FOREIGN_KEY + ) + end + + def down + remove_sharding_key_assignment_trigger( + table: TABLE_NAME, + sharding_key: SHARDING_KEY, + parent_table: PARENT_TABLE, + parent_sharding_key: PARENT_SHARDING_KEY, + foreign_key: FOREIGN_KEY + ) + end +end diff --git a/db/schema_migrations/20251007172025 b/db/schema_migrations/20251007172025 new file mode 100644 index 00000000000000..8037303f638f43 --- /dev/null +++ b/db/schema_migrations/20251007172025 @@ -0,0 +1 @@ +99387f4442c28028b5b9c97a3f1e175281337edd3ee18d311de9be2407e71b4e \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 0039b5ff853051..abce6a247fb684 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -3962,6 +3962,22 @@ RETURN NEW; END $$; +CREATE FUNCTION trigger_cca6a43d90dd() RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN +IF NEW."namespace_id" IS NULL THEN + SELECT "namespace_id" + INTO NEW."namespace_id" + FROM "achievements" + WHERE "achievements"."id" = NEW."model_id"; +END IF; + +RETURN NEW; + +END +$$; + CREATE FUNCTION trigger_cd50823537a3() RETURNS trigger LANGUAGE plpgsql AS $$ @@ -47035,6 +47051,8 @@ CREATE TRIGGER trigger_cac7c0698291 BEFORE INSERT OR UPDATE ON evidences FOR EAC CREATE TRIGGER trigger_catalog_resource_sync_event_on_project_update AFTER UPDATE ON projects FOR EACH ROW WHEN ((((old.name)::text IS DISTINCT FROM (new.name)::text) OR (old.description IS DISTINCT FROM new.description) OR (old.visibility_level IS DISTINCT FROM new.visibility_level))) EXECUTE FUNCTION insert_catalog_resource_sync_event(); +CREATE TRIGGER trigger_cca6a43d90dd BEFORE INSERT OR UPDATE ON achievement_uploads FOR EACH ROW EXECUTE FUNCTION trigger_cca6a43d90dd(); + CREATE TRIGGER trigger_cd50823537a3 BEFORE INSERT OR UPDATE ON issuable_slas FOR EACH ROW EXECUTE FUNCTION trigger_cd50823537a3(); CREATE TRIGGER trigger_cdfa6500a121 BEFORE INSERT OR UPDATE ON snippet_statistics FOR EACH ROW EXECUTE FUNCTION trigger_cdfa6500a121(); -- GitLab From a58fcaa94c14bcb952ace2f0b8ceb31f047fdc08 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Tue, 7 Oct 2025 14:17:52 -0400 Subject: [PATCH 2/3] Add NOT NULL (NOT VALID) constraint on achievement_uploads sharding key Addresses https://gitlab.com/gitlab-org/gitlab/-/issues/398199 Changelog: other --- ...id_to_achievement_uploads_on_namespace_id.rb | 17 +++++++++++++++++ db/schema_migrations/20251007175326 | 1 + db/structure.sql | 3 +++ 3 files changed, 21 insertions(+) create mode 100644 db/migrate/20251007175326_add_not_null_not_valid_to_achievement_uploads_on_namespace_id.rb create mode 100644 db/schema_migrations/20251007175326 diff --git a/db/migrate/20251007175326_add_not_null_not_valid_to_achievement_uploads_on_namespace_id.rb b/db/migrate/20251007175326_add_not_null_not_valid_to_achievement_uploads_on_namespace_id.rb new file mode 100644 index 00000000000000..6e9c37c1869439 --- /dev/null +++ b/db/migrate/20251007175326_add_not_null_not_valid_to_achievement_uploads_on_namespace_id.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddNotNullNotValidToAchievementUploadsOnNamespaceId < Gitlab::Database::Migration[2.3] + disable_ddl_transaction! + milestone '18.6' + + TABLE_NAME = :achievement_uploads + SHARDING_KEY = :namespace_id + + def up + add_not_null_constraint(TABLE_NAME, SHARDING_KEY, validate: false) + end + + def down + remove_not_null_constraint(TABLE_NAME, SHARDING_KEY) + end +end diff --git a/db/schema_migrations/20251007175326 b/db/schema_migrations/20251007175326 new file mode 100644 index 00000000000000..c06d113bd0affd --- /dev/null +++ b/db/schema_migrations/20251007175326 @@ -0,0 +1 @@ +292e5e10df39cf8642aca1245a908f9849cd0fa3b5e4df33d804e469e5eac5ea \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index abce6a247fb684..e54f12be4ec87b 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -33128,6 +33128,9 @@ ALTER TABLE merge_request_cleanup_schedules ALTER TABLE merge_request_context_commit_diff_files ADD CONSTRAINT check_90390c308c CHECK ((project_id IS NOT NULL)) NOT VALID; +ALTER TABLE achievement_uploads + ADD CONSTRAINT check_9dac0178ee CHECK ((namespace_id IS NOT NULL)) NOT VALID; + ALTER TABLE related_epic_links ADD CONSTRAINT check_a6d9d7c276 CHECK ((issue_link_id IS NOT NULL)) NOT VALID; -- GitLab From 42f30bde1168d7a99c059f6ae1454b9aa0593dff Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Tue, 7 Oct 2025 21:15:39 -0400 Subject: [PATCH 3/3] Backfill uploads pertaining to achievements with sharding key Addresses https://gitlab.com/gitlab-org/gitlab/-/issues/398199 Delete orphan uploads where the achievement (parent) has been deleted. Then updated the uploads rows pertaining to achievements that are missing a sharding key - that will trigger a backfill. Changelog: other --- ...kfill_achievement_uploads_sharding_key.yml | 8 ++ ...ckfill_achievement_uploads_sharding_key.rb | 24 ++++ db/schema_migrations/20251007193409 | 1 + ...ckfill_achievement_uploads_sharding_key.rb | 25 ++++ ...l_achievement_uploads_sharding_key_spec.rb | 134 ++++++++++++++++++ ...l_achievement_uploads_sharding_key_spec.rb | 26 ++++ 6 files changed, 218 insertions(+) create mode 100644 db/docs/batched_background_migrations/backfill_achievement_uploads_sharding_key.yml create mode 100644 db/post_migrate/20251007193409_queue_backfill_achievement_uploads_sharding_key.rb create mode 100644 db/schema_migrations/20251007193409 create mode 100644 lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key.rb create mode 100644 spec/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key_spec.rb create mode 100644 spec/migrations/20251007193409_queue_backfill_achievement_uploads_sharding_key_spec.rb diff --git a/db/docs/batched_background_migrations/backfill_achievement_uploads_sharding_key.yml b/db/docs/batched_background_migrations/backfill_achievement_uploads_sharding_key.yml new file mode 100644 index 00000000000000..92469b0b257b2c --- /dev/null +++ b/db/docs/batched_background_migrations/backfill_achievement_uploads_sharding_key.yml @@ -0,0 +1,8 @@ +--- +migration_job_name: BackfillAchievementUploadsShardingKey +description: Remove orphaned uploads and backfill sharding key +feature_category: user_profile +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/207893 +milestone: '18.6' +queued_migration_version: 20251007193409 +finalized_by: # version of the migration that finalized this BBM diff --git a/db/post_migrate/20251007193409_queue_backfill_achievement_uploads_sharding_key.rb b/db/post_migrate/20251007193409_queue_backfill_achievement_uploads_sharding_key.rb new file mode 100644 index 00000000000000..6b7caedc599954 --- /dev/null +++ b/db/post_migrate/20251007193409_queue_backfill_achievement_uploads_sharding_key.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +class QueueBackfillAchievementUploadsShardingKey < Gitlab::Database::Migration[2.3] + milestone '18.6' + restrict_gitlab_migration gitlab_schema: :gitlab_main_org + + MIGRATION = "BackfillAchievementUploadsShardingKey" + BATCH_SIZE = 1000 + SUB_BATCH_SIZE = 100 + + def up + queue_batched_background_migration( + MIGRATION, + :uploads, + :id, + batch_size: BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :uploads, :id, []) + end +end diff --git a/db/schema_migrations/20251007193409 b/db/schema_migrations/20251007193409 new file mode 100644 index 00000000000000..b6aaded43127a8 --- /dev/null +++ b/db/schema_migrations/20251007193409 @@ -0,0 +1 @@ +01817e6a08ed13e6b5f72465379628f0f4bfdf90d4771afbff5fc0eba814f7c8 \ No newline at end of file diff --git a/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key.rb b/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key.rb new file mode 100644 index 00000000000000..8cc099d769f5bc --- /dev/null +++ b/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +module Gitlab + module BackgroundMigration + class BackfillAchievementUploadsShardingKey < BatchedMigrationJob + operation_name :backfill_uploads_sharding_key + feature_category :user_profile + + def perform + each_sub_batch do |sub_batch| + sub_batch + .joins('LEFT JOIN achievements ON achievements.id = uploads.model_id') + .where(uploads: { model_type: 'Achievements::Achievement' }) + .where(achievements: { id: nil }) + .delete_all + + sub_batch + .where(model_type: 'Achievements::Achievement') + .where(namespace_id: nil) + .update_all('created_at = created_at') + end + end + end + end +end diff --git a/spec/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key_spec.rb b/spec/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key_spec.rb new file mode 100644 index 00000000000000..6c7052fef04006 --- /dev/null +++ b/spec/lib/gitlab/background_migration/backfill_achievement_uploads_sharding_key_spec.rb @@ -0,0 +1,134 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::BackgroundMigration::BackfillAchievementUploadsShardingKey, feature_category: :user_profile do + let(:connection) { ApplicationRecord.connection } + + let(:achievements) { table(:achievements) } + let(:achievement_uploads) { table(:achievement_uploads) } + let(:namespaces) { table(:namespaces) } + let(:namespace_uploads) { table(:namespace_uploads) } + let(:uploads) { table(:uploads) } + let(:migration_args) do + { + start_id: uploads.minimum(:id), + end_id: uploads.maximum(:id), + batch_table: :uploads, + batch_column: :id, + sub_batch_size: 1, + pause_ms: 0, + connection: ApplicationRecord.connection + } + end + + let!(:organization) { table(:organizations).create!(name: 'organization', path: 'organization') } + let!(:namespace) do + namespaces.create!(name: 'name', path: 'path', type: 'Group', organization_id: organization.id) + end + + it 'performs' do + drop_constraint_and_trigger + + achievement0 = achievements.create!( + name: 'a0', + namespace_id: namespace.id + ) + upload0 = uploads.create!( + size: 100, + path: 'foo', + uploader: 'bar', + model_type: 'Achievements::Achievement', + model_id: achievement0.id + ) + expect(upload0.reload.namespace_id).to be_nil + expect(achievement_uploads.where(id: upload0.id).first.namespace_id).to be_nil + + recreate_constraint_and_trigger + + achievement1 = achievements.create!( + name: 'a1', + namespace_id: namespace.id + ) + upload1 = uploads.create!( + size: 100, + path: 'foo', + uploader: 'bar', + model_type: 'Achievements::Achievement', + model_id: achievement1.id, + namespace_id: namespace.id + ) + expect { achievement1.destroy! } + .to change { achievements.count }.by(-1) + .and not_change { uploads.count } + .and not_change { achievement_uploads.count } + + achievement2 = achievements.create!( + name: 'a2', + namespace_id: namespace.id + ) + upload2 = uploads.create!( + size: 100, + path: 'foo', + uploader: 'bar', + model_type: 'Achievements::Achievement', + model_id: achievement2.id, + namespace_id: namespace.id + ) + + create_another_partition_rows + + expect { described_class.new(**migration_args).perform } + .to change { uploads.count }.by(-1) + .and change { achievement_uploads.count }.by(-1) + expect(upload0.reload.namespace_id).to be_nil + expect(achievement_uploads.where(id: upload0.id).first.namespace_id).to eq(namespace.id) + expect(uploads.where(id: upload1.id)).to be_empty + expect(upload2.reload.namespace_id).to eq(namespace.id) + end + + private + + def create_another_partition_rows + namespace2 = namespaces.create!(name: 'name', path: 'path', type: 'Group', organization_id: organization.id) + uploads.create!( + size: 100, + path: 'foo', + uploader: 'bar', + model_type: 'Namespace', + model_id: namespace2.id + ) + expect { namespace2.destroy! } + .to change { namespaces.count }.by(-1) + .and not_change { uploads.count } + .and not_change { namespace_uploads.count } + + uploads.create!( + size: 100, + path: 'foo', + uploader: 'bar', + model_type: 'Namespace', + model_id: namespace.id + ) + end + + def drop_constraint_and_trigger + connection.execute( + <<~SQL + DROP TRIGGER IF EXISTS trigger_cca6a43d90dd ON achievement_uploads; + ALTER TABLE achievement_uploads DROP CONSTRAINT IF EXISTS check_9dac0178ee; + SQL + ) + end + + def recreate_constraint_and_trigger + connection.execute( + <<~SQL + CREATE TRIGGER trigger_cca6a43d90dd BEFORE INSERT OR UPDATE + ON achievement_uploads FOR EACH ROW EXECUTE FUNCTION trigger_cca6a43d90dd(); + ALTER TABLE achievement_uploads + ADD CONSTRAINT check_9dac0178ee CHECK ((namespace_id IS NOT NULL)) NOT VALID; + SQL + ) + end +end diff --git a/spec/migrations/20251007193409_queue_backfill_achievement_uploads_sharding_key_spec.rb b/spec/migrations/20251007193409_queue_backfill_achievement_uploads_sharding_key_spec.rb new file mode 100644 index 00000000000000..bde3ac21450a6e --- /dev/null +++ b/spec/migrations/20251007193409_queue_backfill_achievement_uploads_sharding_key_spec.rb @@ -0,0 +1,26 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_migration! + +RSpec.describe QueueBackfillAchievementUploadsShardingKey, migration: :gitlab_main_org, feature_category: :user_profile do + let!(:batched_migration) { described_class::MIGRATION } + + it 'schedules a new batched migration' do + reversible_migration do |migration| + migration.before -> { + expect(batched_migration).not_to have_scheduled_batched_migration + } + + migration.after -> { + expect(batched_migration).to have_scheduled_batched_migration( + gitlab_schema: :gitlab_main_org, + table_name: :uploads, + column_name: :id, + batch_size: described_class::BATCH_SIZE, + sub_batch_size: described_class::SUB_BATCH_SIZE + ) + } + end + end +end -- GitLab