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 0000000000000000000000000000000000000000..f99d2914aa4a24b98d27a186bd5d82777b82ae75 --- /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 0000000000000000000000000000000000000000..4fec5e016a4212ca969cf6cc8e99be3914e2b9f2 --- /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 0000000000000000000000000000000000000000..5577a18c144a732818509693b7aa93fc07f9f761 --- /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/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 0000000000000000000000000000000000000000..e04647d7c92503e9042e7a00597b94a7953be8d8 --- /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 0000000000000000000000000000000000000000..ef9ecc3033f4557fb0511777df1c37578b9be923 --- /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 0000000000000000000000000000000000000000..dd16d3d7787fa0575369728c79e3e0a62ca582f2 --- /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 0000000000000000000000000000000000000000..a886949aea74b4202a08716fb2d2235799d40f53 --- /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 0000000000000000000000000000000000000000..6ea8cb213ab6d762aaadbc0c44c9f06dd94ba937 --- /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 0000000000000000000000000000000000000000..8afd7de808b964a4369e6bbd1112d9b78627992c --- /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/20221216131710 b/db/schema_migrations/20221216131710 new file mode 100644 index 0000000000000000000000000000000000000000..723eda5194c8f81c580a7b29e18f0f279f149d17 --- /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 0000000000000000000000000000000000000000..9dcb4412aecc9209034da7ae6d6cfb8492a219f9 --- /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 0000000000000000000000000000000000000000..4c773777ff35a863441b00dcc71999bf5fdb4cdc --- /dev/null +++ b/db/schema_migrations/20221216131958 @@ -0,0 +1 @@ +0a012ad57aa6833f41e8927573aeff716ce36724ed6c1ecca8fc0d77186b4c88 \ No newline at end of file diff --git a/db/schema_migrations/20221216144854 b/db/schema_migrations/20221216144854 new file mode 100644 index 0000000000000000000000000000000000000000..6deba7583f29d3c820aa4f91f0d329cfe4a7c777 --- /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 0000000000000000000000000000000000000000..16fa7b669e6acec9cbaada3a940056c755c76edc --- /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 0000000000000000000000000000000000000000..e6690af1e15d2eaa42b31537d7f31b17db11d47f --- /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 0000000000000000000000000000000000000000..e2056eb9b553a5940c7baa66288543d9045775ab --- /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 0000000000000000000000000000000000000000..d590076786d8cdbb844b5be68c8691d4d6609760 --- /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 0000000000000000000000000000000000000000..84d5b3b36b3d592d69324e2a0b2755b8dd1c4906 --- /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 2aad46280a3987d46861913be7b900c8c5545b92..c9ca500760bf96e0baeaee02d6992a1098080612 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); @@ -33072,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; @@ -34035,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; @@ -34065,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 9e23cca7c3ff94c4786ee419264e4328b11d4f0b..65d5da000877dcdb782622c1b2389dc29dac54c4 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