From 0e00b6414d454845979412d1f832cc6a2ef53ba8 Mon Sep 17 00:00:00 2001 From: Marius Bobin Date: Fri, 27 Jan 2023 14:45:44 +0200 Subject: [PATCH] Add partitioned index and FK for ci_sources_pipelines and ci_builds Changelog: added --- ...n_source_partition_id_and_source_job_id.rb | 17 +++++++++ ...n_source_partition_id_and_source_job_id.rb | 37 +++++++++++++++++++ db/schema_migrations/20230127123947 | 1 + db/schema_migrations/20230127123948 | 1 + db/structure.sql | 5 +++ 5 files changed, 61 insertions(+) create mode 100644 db/post_migrate/20230127123947_add_fk_index_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb create mode 100644 db/post_migrate/20230127123948_add_fk_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb create mode 100644 db/schema_migrations/20230127123947 create mode 100644 db/schema_migrations/20230127123948 diff --git a/db/post_migrate/20230127123947_add_fk_index_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb b/db/post_migrate/20230127123947_add_fk_index_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb new file mode 100644 index 00000000000000..f2a9d362cc4681 --- /dev/null +++ b/db/post_migrate/20230127123947_add_fk_index_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddFkIndexToCiSourcesPipelinesOnSourcePartitionIdAndSourceJobId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_sources_pipelines_on_source_partition_id_source_job_id + TABLE_NAME = :ci_sources_pipelines + COLUMNS = [:source_partition_id, :source_job_id] + + def up + add_concurrent_index(TABLE_NAME, COLUMNS, name: INDEX_NAME) + end + + def down + remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME) + end +end diff --git a/db/post_migrate/20230127123948_add_fk_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb b/db/post_migrate/20230127123948_add_fk_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb new file mode 100644 index 00000000000000..7adf97268ff408 --- /dev/null +++ b/db/post_migrate/20230127123948_add_fk_to_ci_sources_pipelines_on_source_partition_id_and_source_job_id.rb @@ -0,0 +1,37 @@ +# frozen_string_literal: true + +class AddFkToCiSourcesPipelinesOnSourcePartitionIdAndSourceJobId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_sources_pipelines + TARGET_TABLE_NAME = :ci_builds + COLUMN = :source_job_id + TARGET_COLUMN = :id + FK_NAME = :fk_be5624bf37_p + PARTITION_COLUMN = :source_partition_id + + def up + add_concurrent_foreign_key( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + column: [PARTITION_COLUMN, COLUMN], + target_column: [:partition_id, TARGET_COLUMN], + validate: false, + reverse_lock_order: true, + on_update: :cascade, + on_delete: :cascade, + name: FK_NAME + ) + end + + def down + with_lock_retries do + remove_foreign_key_if_exists( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end +end diff --git a/db/schema_migrations/20230127123947 b/db/schema_migrations/20230127123947 new file mode 100644 index 00000000000000..cc3981f1302016 --- /dev/null +++ b/db/schema_migrations/20230127123947 @@ -0,0 +1 @@ +dd57ab40a4263df49d8f52f8f737c5cc62101f932752cbb984cb6072d766d6f0 \ No newline at end of file diff --git a/db/schema_migrations/20230127123948 b/db/schema_migrations/20230127123948 new file mode 100644 index 00000000000000..0fa5c113d579dd --- /dev/null +++ b/db/schema_migrations/20230127123948 @@ -0,0 +1 @@ +aec7695c7e1cd2eb61625c1c08f7d8ee955bd729a8d70ea2753afcb7b545bfe6 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index c0617ec779b143..e8b549881d1eae 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -29259,6 +29259,8 @@ CREATE INDEX index_ci_sources_pipelines_on_project_id ON ci_sources_pipelines US CREATE INDEX index_ci_sources_pipelines_on_source_job_id ON ci_sources_pipelines USING btree (source_job_id); +CREATE INDEX index_ci_sources_pipelines_on_source_partition_id_source_job_id ON ci_sources_pipelines USING btree (source_partition_id, source_job_id); + CREATE INDEX index_ci_sources_pipelines_on_source_pipeline_id ON ci_sources_pipelines USING btree (source_pipeline_id); CREATE INDEX index_ci_sources_pipelines_on_source_project_id ON ci_sources_pipelines USING btree (source_project_id); @@ -34125,6 +34127,9 @@ ALTER TABLE ONLY snippets ALTER TABLE ONLY ci_sources_pipelines ADD CONSTRAINT fk_be5624bf37 FOREIGN KEY (source_job_id) REFERENCES ci_builds(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_sources_pipelines + ADD CONSTRAINT fk_be5624bf37_p FOREIGN KEY (source_partition_id, source_job_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY packages_maven_metadata ADD CONSTRAINT fk_be88aed360 FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE; -- GitLab