From 67f987db45a3cce43d19f29a1e1763d73d5d6e08 Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Tue, 20 Dec 2022 14:56:43 +0100 Subject: [PATCH 1/2] Add FK including partition_id to ci_build_report_results Changelog: added --- ...rt_results_on_partition_id_and_build_id.rb | 17 +++++++++ ...rt_results_on_partition_id_and_build_id.rb | 37 +++++++++++++++++++ db/schema_migrations/20230130102855 | 1 + db/schema_migrations/20230130102856 | 1 + db/structure.sql | 5 +++ 5 files changed, 61 insertions(+) create mode 100644 db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb create mode 100644 db/post_migrate/20230130102856_add_fk_to_ci_build_report_results_on_partition_id_and_build_id.rb create mode 100644 db/schema_migrations/20230130102855 create mode 100644 db/schema_migrations/20230130102856 diff --git a/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb b/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb new file mode 100644 index 00000000000000..3bf13ae20f4d04 --- /dev/null +++ b/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddFkIndexToCiBuildReportResultsOnPartitionIdAndBuildId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_build_report_results_on_partition_id_build_id + TABLE_NAME = :ci_build_report_results + COLUMNS = [:partition_id, :build_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/20230130102856_add_fk_to_ci_build_report_results_on_partition_id_and_build_id.rb b/db/post_migrate/20230130102856_add_fk_to_ci_build_report_results_on_partition_id_and_build_id.rb new file mode 100644 index 00000000000000..ef301af2c9fb06 --- /dev/null +++ b/db/post_migrate/20230130102856_add_fk_to_ci_build_report_results_on_partition_id_and_build_id.rb @@ -0,0 +1,37 @@ +# frozen_string_literal: true + +class AddFkToCiBuildReportResultsOnPartitionIdAndBuildId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_build_report_results + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_rails_16cb1ff064_p + PARTITION_COLUMN = :partition_id + + def up + add_concurrent_foreign_key( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + column: [PARTITION_COLUMN, COLUMN], + target_column: [PARTITION_COLUMN, 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/20230130102855 b/db/schema_migrations/20230130102855 new file mode 100644 index 00000000000000..91f9164a2540a3 --- /dev/null +++ b/db/schema_migrations/20230130102855 @@ -0,0 +1 @@ +1e6d539713dd9d6b6b8afd5fcb6db87e75eb96fccf91a94c00700760fb5e963f \ No newline at end of file diff --git a/db/schema_migrations/20230130102856 b/db/schema_migrations/20230130102856 new file mode 100644 index 00000000000000..f258d0e1eae411 --- /dev/null +++ b/db/schema_migrations/20230130102856 @@ -0,0 +1 @@ +4dc770e4a78c0f3e3aa5af43e017744a85b4974c1315508ac2244c7b12f4e0b3 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index eedf798f980b33..f480bea7d8d1b7 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28918,6 +28918,8 @@ CREATE UNIQUE INDEX index_ci_build_pending_states_on_build_id ON ci_build_pendin CREATE INDEX index_ci_build_pending_states_on_partition_id_build_id ON ci_build_pending_states USING btree (partition_id, build_id); +CREATE INDEX index_ci_build_report_results_on_partition_id_build_id ON ci_build_report_results USING btree (partition_id, build_id); + CREATE INDEX index_ci_build_report_results_on_project_id ON ci_build_report_results USING btree (project_id); CREATE UNIQUE INDEX index_ci_build_trace_chunks_on_build_id_and_chunk_index ON ci_build_trace_chunks USING btree (build_id, chunk_index); @@ -34473,6 +34475,9 @@ ALTER TABLE ONLY users_security_dashboard_projects ALTER TABLE ONLY ci_build_report_results ADD CONSTRAINT fk_rails_16cb1ff064 FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_build_report_results + ADD CONSTRAINT fk_rails_16cb1ff064_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY project_deploy_tokens ADD CONSTRAINT fk_rails_170e03cbaf FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; -- GitLab From e2657f1473fd1e1823d58f372cdbff716caa4a06 Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Wed, 1 Feb 2023 15:49:39 +0100 Subject: [PATCH 2/2] Apply code review feedback --- ...x_to_ci_build_report_results_on_partition_id_and_build_id.rb | 2 +- db/structure.sql | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb b/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb index 3bf13ae20f4d04..4fe71ba46d972a 100644 --- a/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb +++ b/db/post_migrate/20230130102855_add_fk_index_to_ci_build_report_results_on_partition_id_and_build_id.rb @@ -8,7 +8,7 @@ class AddFkIndexToCiBuildReportResultsOnPartitionIdAndBuildId < Gitlab::Database COLUMNS = [:partition_id, :build_id] def up - add_concurrent_index(TABLE_NAME, COLUMNS, name: INDEX_NAME) + add_concurrent_index(TABLE_NAME, COLUMNS, unique: true, name: INDEX_NAME) end def down diff --git a/db/structure.sql b/db/structure.sql index f480bea7d8d1b7..5c6cd92935d1e7 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28918,7 +28918,7 @@ CREATE UNIQUE INDEX index_ci_build_pending_states_on_build_id ON ci_build_pendin CREATE INDEX index_ci_build_pending_states_on_partition_id_build_id ON ci_build_pending_states USING btree (partition_id, build_id); -CREATE INDEX index_ci_build_report_results_on_partition_id_build_id ON ci_build_report_results USING btree (partition_id, build_id); +CREATE UNIQUE INDEX index_ci_build_report_results_on_partition_id_build_id ON ci_build_report_results USING btree (partition_id, build_id); CREATE INDEX index_ci_build_report_results_on_project_id ON ci_build_report_results USING btree (project_id); -- GitLab