From 3ce950227490bf3266ced6cc2ed028594d380b20 Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Mon, 12 Dec 2022 16:13:28 +0100 Subject: [PATCH 1/2] Add index and foreign_keys including partition_id This is required in order to partition ci_builds. Changelog: added --- ...6131710_add_index_to_ci_unit_test_failure.rb | 17 +++++++++++++++++ ...31935_add_index_to_ci_build_pending_state.rb | 17 +++++++++++++++++ ...6131958_add_index_to_ci_build_trace_chunk.rb | 17 +++++++++++++++++ db/schema_migrations/20221216131710 | 1 + db/schema_migrations/20221216131935 | 1 + db/schema_migrations/20221216131958 | 1 + db/structure.sql | 6 ++++++ 7 files changed, 60 insertions(+) create mode 100644 db/post_migrate/20221216131710_add_index_to_ci_unit_test_failure.rb create mode 100644 db/post_migrate/20221216131935_add_index_to_ci_build_pending_state.rb create mode 100644 db/post_migrate/20221216131958_add_index_to_ci_build_trace_chunk.rb create mode 100644 db/schema_migrations/20221216131710 create mode 100644 db/schema_migrations/20221216131935 create mode 100644 db/schema_migrations/20221216131958 diff --git a/db/post_migrate/20221216131710_add_index_to_ci_unit_test_failure.rb b/db/post_migrate/20221216131710_add_index_to_ci_unit_test_failure.rb new file mode 100644 index 00000000000000..f99d2914aa4a24 --- /dev/null +++ b/db/post_migrate/20221216131710_add_index_to_ci_unit_test_failure.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddIndexToCiUnitTestFailure < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_unit_test_failures_on_partition_id_build_id + TABLE_NAME = :ci_unit_test_failures + 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/20221216131935_add_index_to_ci_build_pending_state.rb b/db/post_migrate/20221216131935_add_index_to_ci_build_pending_state.rb new file mode 100644 index 00000000000000..4fec5e016a4212 --- /dev/null +++ b/db/post_migrate/20221216131935_add_index_to_ci_build_pending_state.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddIndexToCiBuildPendingState < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_build_pending_states_on_partition_id_build_id + TABLE_NAME = :ci_build_pending_states + 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/20221216131958_add_index_to_ci_build_trace_chunk.rb b/db/post_migrate/20221216131958_add_index_to_ci_build_trace_chunk.rb new file mode 100644 index 00000000000000..5577a18c144a73 --- /dev/null +++ b/db/post_migrate/20221216131958_add_index_to_ci_build_trace_chunk.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddIndexToCiBuildTraceChunk < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_build_trace_chunks_on_partition_id_build_id + TABLE_NAME = :ci_build_trace_chunks + 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/schema_migrations/20221216131710 b/db/schema_migrations/20221216131710 new file mode 100644 index 00000000000000..723eda5194c8f8 --- /dev/null +++ b/db/schema_migrations/20221216131710 @@ -0,0 +1 @@ +88ec0ad09f04b5c95cc75afdcee64d488abfbf6cf86ab0b7215623a227865432 \ No newline at end of file diff --git a/db/schema_migrations/20221216131935 b/db/schema_migrations/20221216131935 new file mode 100644 index 00000000000000..9dcb4412aecc92 --- /dev/null +++ b/db/schema_migrations/20221216131935 @@ -0,0 +1 @@ +fe479c2d3784ede41207beff4fad21004f74ade8f8c08f6a9b154429ceef6395 \ No newline at end of file diff --git a/db/schema_migrations/20221216131958 b/db/schema_migrations/20221216131958 new file mode 100644 index 00000000000000..4c773777ff35a8 --- /dev/null +++ b/db/schema_migrations/20221216131958 @@ -0,0 +1 @@ +0a012ad57aa6833f41e8927573aeff716ce36724ed6c1ecca8fc0d77186b4c88 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 2aad46280a3987..04b8504a7b4fdd 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28619,10 +28619,14 @@ CREATE UNIQUE INDEX index_ci_build_needs_on_build_id_and_name ON ci_build_needs CREATE UNIQUE INDEX index_ci_build_pending_states_on_build_id ON ci_build_pending_states USING btree (build_id); +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_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); +CREATE INDEX index_ci_build_trace_chunks_on_partition_id_build_id ON ci_build_trace_chunks USING btree (partition_id, build_id); + CREATE INDEX index_ci_build_trace_metadata_on_trace_artifact_id ON ci_build_trace_metadata USING btree (trace_artifact_id); CREATE INDEX p_ci_builds_metadata_build_id_idx ON ONLY p_ci_builds_metadata USING btree (build_id) WHERE (has_exposed_artifacts IS TRUE); @@ -28939,6 +28943,8 @@ CREATE INDEX index_ci_triggers_on_project_id ON ci_triggers USING btree (project CREATE INDEX index_ci_unit_test_failures_on_build_id ON ci_unit_test_failures USING btree (build_id); +CREATE INDEX index_ci_unit_test_failures_on_partition_id_build_id ON ci_unit_test_failures USING btree (partition_id, build_id); + CREATE UNIQUE INDEX index_ci_unit_tests_on_project_id_and_key_hash ON ci_unit_tests USING btree (project_id, key_hash); CREATE INDEX index_ci_variables_on_key ON ci_variables USING btree (key); -- GitLab From 25f6992e55d7452d7fbf4c8fe9f25d959aa56d1f Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Fri, 16 Dec 2022 16:19:37 +0100 Subject: [PATCH 2/2] dd FKs including partition_id --- ...add_foreign_key_to_ci_unit_test_failure.rb | 30 +++++++++++++++++++ ...d_foreign_key_to_ci_build_pending_state.rb | 30 +++++++++++++++++++ ...add_foreign_key_to_ci_build_trace_chunk.rb | 30 +++++++++++++++++++ ...unit_test_failure_partition_id_build_id.rb | 16 ++++++++++ ...ild_pending_state_partition_id_build_id.rb | 16 ++++++++++ ...build_trace_chunk_partition_id_build_id.rb | 16 ++++++++++ db/schema_migrations/20221216144854 | 1 + db/schema_migrations/20221216150304 | 1 + db/schema_migrations/20221216150321 | 1 + db/schema_migrations/20221216150919 | 1 + db/schema_migrations/20221216151459 | 1 + db/schema_migrations/20221216151649 | 1 + db/structure.sql | 9 ++++++ spec/db/schema_spec.rb | 2 +- 14 files changed, 154 insertions(+), 1 deletion(-) create mode 100644 db/post_migrate/20221216144854_add_foreign_key_to_ci_unit_test_failure.rb create mode 100644 db/post_migrate/20221216150304_add_foreign_key_to_ci_build_pending_state.rb create mode 100644 db/post_migrate/20221216150321_add_foreign_key_to_ci_build_trace_chunk.rb create mode 100644 db/post_migrate/20221216150919_validate_foreign_key_on_ci_unit_test_failure_partition_id_build_id.rb create mode 100644 db/post_migrate/20221216151459_validate_foreign_key_on_ci_build_pending_state_partition_id_build_id.rb create mode 100644 db/post_migrate/20221216151649_validate_foreign_key_on_ci_build_trace_chunk_partition_id_build_id.rb create mode 100644 db/schema_migrations/20221216144854 create mode 100644 db/schema_migrations/20221216150304 create mode 100644 db/schema_migrations/20221216150321 create mode 100644 db/schema_migrations/20221216150919 create mode 100644 db/schema_migrations/20221216151459 create mode 100644 db/schema_migrations/20221216151649 diff --git a/db/post_migrate/20221216144854_add_foreign_key_to_ci_unit_test_failure.rb b/db/post_migrate/20221216144854_add_foreign_key_to_ci_unit_test_failure.rb new file mode 100644 index 00000000000000..e04647d7c92503 --- /dev/null +++ b/db/post_migrate/20221216144854_add_foreign_key_to_ci_unit_test_failure.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class AddForeignKeyToCiUnitTestFailure < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_unit_test_failures + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_0f09856e1f_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, + name: FK_NAME + ) + end + + def down + with_lock_retries do + remove_foreign_key_if_exists(SOURCE_TABLE_NAME, name: FK_NAME) + end + end +end diff --git a/db/post_migrate/20221216150304_add_foreign_key_to_ci_build_pending_state.rb b/db/post_migrate/20221216150304_add_foreign_key_to_ci_build_pending_state.rb new file mode 100644 index 00000000000000..ef9ecc3033f455 --- /dev/null +++ b/db/post_migrate/20221216150304_add_foreign_key_to_ci_build_pending_state.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class AddForeignKeyToCiBuildPendingState < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_build_pending_states + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_rails_0bbbfeaf9d_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, + name: FK_NAME + ) + end + + def down + with_lock_retries do + remove_foreign_key_if_exists(SOURCE_TABLE_NAME, name: FK_NAME) + end + end +end diff --git a/db/post_migrate/20221216150321_add_foreign_key_to_ci_build_trace_chunk.rb b/db/post_migrate/20221216150321_add_foreign_key_to_ci_build_trace_chunk.rb new file mode 100644 index 00000000000000..dd16d3d7787fa0 --- /dev/null +++ b/db/post_migrate/20221216150321_add_foreign_key_to_ci_build_trace_chunk.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class AddForeignKeyToCiBuildTraceChunk < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_build_trace_chunks + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_rails_1013b761f2_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, + name: FK_NAME + ) + end + + def down + with_lock_retries do + remove_foreign_key_if_exists(SOURCE_TABLE_NAME, name: FK_NAME) + end + end +end diff --git a/db/post_migrate/20221216150919_validate_foreign_key_on_ci_unit_test_failure_partition_id_build_id.rb b/db/post_migrate/20221216150919_validate_foreign_key_on_ci_unit_test_failure_partition_id_build_id.rb new file mode 100644 index 00000000000000..a886949aea74b4 --- /dev/null +++ b/db/post_migrate/20221216150919_validate_foreign_key_on_ci_unit_test_failure_partition_id_build_id.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class ValidateForeignKeyOnCiUnitTestFailurePartitionIdBuildId < Gitlab::Database::Migration[2.1] + TABLE_NAME = :ci_unit_test_failures + FK_NAME = :fk_0f09856e1f_p + PARTITION_COLUMN = :partition_id + COLUMN = :build_id + + def up + validate_foreign_key(TABLE_NAME, [PARTITION_COLUMN, COLUMN], name: FK_NAME) + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20221216151459_validate_foreign_key_on_ci_build_pending_state_partition_id_build_id.rb b/db/post_migrate/20221216151459_validate_foreign_key_on_ci_build_pending_state_partition_id_build_id.rb new file mode 100644 index 00000000000000..6ea8cb213ab6d7 --- /dev/null +++ b/db/post_migrate/20221216151459_validate_foreign_key_on_ci_build_pending_state_partition_id_build_id.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class ValidateForeignKeyOnCiBuildPendingStatePartitionIdBuildId < Gitlab::Database::Migration[2.1] + TABLE_NAME = :ci_build_pending_states + FK_NAME = :fk_rails_0bbbfeaf9d_p + PARTITION_COLUMN = :partition_id + COLUMN = :build_id + + def up + validate_foreign_key(TABLE_NAME, [PARTITION_COLUMN, COLUMN], name: FK_NAME) + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20221216151649_validate_foreign_key_on_ci_build_trace_chunk_partition_id_build_id.rb b/db/post_migrate/20221216151649_validate_foreign_key_on_ci_build_trace_chunk_partition_id_build_id.rb new file mode 100644 index 00000000000000..8afd7de808b964 --- /dev/null +++ b/db/post_migrate/20221216151649_validate_foreign_key_on_ci_build_trace_chunk_partition_id_build_id.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class ValidateForeignKeyOnCiBuildTraceChunkPartitionIdBuildId < Gitlab::Database::Migration[2.1] + TABLE_NAME = :ci_build_trace_chunks + FK_NAME = :fk_rails_1013b761f2_p + PARTITION_COLUMN = :partition_id + COLUMN = :build_id + + def up + validate_foreign_key(TABLE_NAME, [PARTITION_COLUMN, COLUMN], name: FK_NAME) + end + + def down + # no-op + end +end diff --git a/db/schema_migrations/20221216144854 b/db/schema_migrations/20221216144854 new file mode 100644 index 00000000000000..6deba7583f29d3 --- /dev/null +++ b/db/schema_migrations/20221216144854 @@ -0,0 +1 @@ +6a5cae1c59bf32796feee4d7adf113d36a0aea1e0324eb082a848870edaf98c7 \ No newline at end of file diff --git a/db/schema_migrations/20221216150304 b/db/schema_migrations/20221216150304 new file mode 100644 index 00000000000000..16fa7b669e6ace --- /dev/null +++ b/db/schema_migrations/20221216150304 @@ -0,0 +1 @@ +74d618f1cf30125936386257bb3bef793afefffc0b7f45f854c2f0632bae56e7 \ No newline at end of file diff --git a/db/schema_migrations/20221216150321 b/db/schema_migrations/20221216150321 new file mode 100644 index 00000000000000..e6690af1e15d2e --- /dev/null +++ b/db/schema_migrations/20221216150321 @@ -0,0 +1 @@ +501fe17068407fa590ba7f85acf0073ee701d37ad9f9d84ecbeaa8c282fd2957 \ No newline at end of file diff --git a/db/schema_migrations/20221216150919 b/db/schema_migrations/20221216150919 new file mode 100644 index 00000000000000..e2056eb9b553a5 --- /dev/null +++ b/db/schema_migrations/20221216150919 @@ -0,0 +1 @@ +1dda2456e1b2a8ddd5e45d3115219ef9d35a9a11a7d498d1ff394b687d7c9761 \ No newline at end of file diff --git a/db/schema_migrations/20221216151459 b/db/schema_migrations/20221216151459 new file mode 100644 index 00000000000000..d590076786d8cd --- /dev/null +++ b/db/schema_migrations/20221216151459 @@ -0,0 +1 @@ +3f4e9fdd522498b79512e37f022d73e4f0495d0c6419c783d574a630d07d31c4 \ No newline at end of file diff --git a/db/schema_migrations/20221216151649 b/db/schema_migrations/20221216151649 new file mode 100644 index 00000000000000..84d5b3b36b3d59 --- /dev/null +++ b/db/schema_migrations/20221216151649 @@ -0,0 +1 @@ +28d64bca23a251a185241c1f735a663a8da116a5e6dd44451ce94ea2b1945f7d \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 04b8504a7b4fdd..c9ca500760bf96 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -33078,6 +33078,9 @@ ALTER TABLE ONLY lists ALTER TABLE ONLY ci_unit_test_failures ADD CONSTRAINT fk_0f09856e1f FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_unit_test_failures + ADD CONSTRAINT fk_0f09856e1f_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON DELETE CASCADE; + ALTER TABLE ONLY deployment_approvals ADD CONSTRAINT fk_0f58311058 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; @@ -34041,6 +34044,9 @@ ALTER TABLE ONLY related_epic_links ALTER TABLE ONLY ci_build_pending_states ADD CONSTRAINT fk_rails_0bbbfeaf9d FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_build_pending_states + ADD CONSTRAINT fk_rails_0bbbfeaf9d_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON DELETE CASCADE; + ALTER TABLE ONLY audit_events_external_audit_event_destinations ADD CONSTRAINT fk_rails_0bc80a4edc FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -34071,6 +34077,9 @@ ALTER TABLE ONLY merge_request_context_commits ALTER TABLE ONLY ci_build_trace_chunks ADD CONSTRAINT fk_rails_1013b761f2 FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_build_trace_chunks + ADD CONSTRAINT fk_rails_1013b761f2_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON DELETE CASCADE; + ALTER TABLE ONLY vulnerability_exports ADD CONSTRAINT fk_rails_1019162882 FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE; diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index 9e23cca7c3ff94..65d5da000877dc 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -166,7 +166,7 @@ context 'columns ending with _id' do let(:column_names) { columns.map(&:name) } let(:column_names_with_id) { column_names.select { |column_name| column_name.ends_with?('_id') } } - let(:foreign_keys_columns) { all_foreign_keys.map(&:column).uniq } # we can have FK and loose FK present at the same time + let(:foreign_keys_columns) { all_foreign_keys.reject { |fk| fk.name&.end_with?("_p") }.map(&:column).uniq } # we can have FK and loose FK present at the same time let(:ignored_columns) { ignored_fk_columns(table) } it 'do have the foreign keys' do -- GitLab