From e0e904a226edf273df1fd8a2d6a47775b380f4cf Mon Sep 17 00:00:00 2001 From: Marius Bobin Date: Thu, 19 Jan 2023 14:41:01 +0200 Subject: [PATCH] Update ci_resources FK to include partition_id Changelog: added --- ..._resources_on_partition_id_and_build_id.rb | 17 ++++++++++ ..._resources_on_partition_id_and_build_id.rb | 32 +++++++++++++++++++ ..._ci_resources_partition_id_and_build_id.rb | 15 +++++++++ ...k_to_ci_builds_ci_resources_on_build_id.rb | 30 +++++++++++++++++ db/schema_migrations/20230119123256 | 1 + db/schema_migrations/20230119123257 | 1 + db/schema_migrations/20230119123258 | 1 + db/schema_migrations/20230119123259 | 1 + db/structure.sql | 4 ++- spec/db/schema_spec.rb | 2 +- 10 files changed, 102 insertions(+), 2 deletions(-) create mode 100644 db/post_migrate/20230119123256_add_fk_index_to_ci_resources_on_partition_id_and_build_id.rb create mode 100644 db/post_migrate/20230119123257_add_fk_to_ci_resources_on_partition_id_and_build_id.rb create mode 100644 db/post_migrate/20230119123258_validate_fk_on_ci_resources_partition_id_and_build_id.rb create mode 100644 db/post_migrate/20230119123259_remove_fk_to_ci_builds_ci_resources_on_build_id.rb create mode 100644 db/schema_migrations/20230119123256 create mode 100644 db/schema_migrations/20230119123257 create mode 100644 db/schema_migrations/20230119123258 create mode 100644 db/schema_migrations/20230119123259 diff --git a/db/post_migrate/20230119123256_add_fk_index_to_ci_resources_on_partition_id_and_build_id.rb b/db/post_migrate/20230119123256_add_fk_index_to_ci_resources_on_partition_id_and_build_id.rb new file mode 100644 index 00000000000000..8c1b8c81235750 --- /dev/null +++ b/db/post_migrate/20230119123256_add_fk_index_to_ci_resources_on_partition_id_and_build_id.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddFkIndexToCiResourcesOnPartitionIdAndBuildId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :index_ci_resources_on_partition_id_build_id + TABLE_NAME = :ci_resources + 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/20230119123257_add_fk_to_ci_resources_on_partition_id_and_build_id.rb b/db/post_migrate/20230119123257_add_fk_to_ci_resources_on_partition_id_and_build_id.rb new file mode 100644 index 00000000000000..8279def779e81c --- /dev/null +++ b/db/post_migrate/20230119123257_add_fk_to_ci_resources_on_partition_id_and_build_id.rb @@ -0,0 +1,32 @@ +# frozen_string_literal: true + +class AddFkToCiResourcesOnPartitionIdAndBuildId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_resources + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_e169a8e3d5_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: :nullify, + 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/20230119123258_validate_fk_on_ci_resources_partition_id_and_build_id.rb b/db/post_migrate/20230119123258_validate_fk_on_ci_resources_partition_id_and_build_id.rb new file mode 100644 index 00000000000000..3df7f27d921186 --- /dev/null +++ b/db/post_migrate/20230119123258_validate_fk_on_ci_resources_partition_id_and_build_id.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class ValidateFkOnCiResourcesPartitionIdAndBuildId < Gitlab::Database::Migration[2.1] + TABLE_NAME = :ci_resources + FK_NAME = :fk_e169a8e3d5_p + COLUMNS = [:partition_id, :build_id] + + def up + validate_foreign_key(TABLE_NAME, COLUMNS, name: FK_NAME) + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20230119123259_remove_fk_to_ci_builds_ci_resources_on_build_id.rb b/db/post_migrate/20230119123259_remove_fk_to_ci_builds_ci_resources_on_build_id.rb new file mode 100644 index 00000000000000..fcf6989f30da4b --- /dev/null +++ b/db/post_migrate/20230119123259_remove_fk_to_ci_builds_ci_resources_on_build_id.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class RemoveFkToCiBuildsCiResourcesOnBuildId < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :ci_resources + TARGET_TABLE_NAME = :ci_builds + COLUMN = :build_id + TARGET_COLUMN = :id + FK_NAME = :fk_e169a8e3d5 + + def up + with_lock_retries do + remove_foreign_key_if_exists(SOURCE_TABLE_NAME, name: FK_NAME) + end + end + + def down + add_concurrent_foreign_key( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + column: COLUMN, + target_column: TARGET_COLUMN, + validate: true, + reverse_lock_order: true, + on_delete: :nullify, + name: FK_NAME + ) + end +end diff --git a/db/schema_migrations/20230119123256 b/db/schema_migrations/20230119123256 new file mode 100644 index 00000000000000..3a2db3c1d7641a --- /dev/null +++ b/db/schema_migrations/20230119123256 @@ -0,0 +1 @@ +bbf6542b726466ae98323f1e7dd636874e01228ec584166ab617a917822b3fa1 \ No newline at end of file diff --git a/db/schema_migrations/20230119123257 b/db/schema_migrations/20230119123257 new file mode 100644 index 00000000000000..78d75e164c0657 --- /dev/null +++ b/db/schema_migrations/20230119123257 @@ -0,0 +1 @@ +e69eabf71bfdfc9c5aa50829d08b3ef1473e5359d01e08e1bdc94fcbb7c58e6e \ No newline at end of file diff --git a/db/schema_migrations/20230119123258 b/db/schema_migrations/20230119123258 new file mode 100644 index 00000000000000..0bb155c2106248 --- /dev/null +++ b/db/schema_migrations/20230119123258 @@ -0,0 +1 @@ +6af88109e5186a6a2f18418f441e232757ee0b03cb8af62e72c86ca4d12075c9 \ No newline at end of file diff --git a/db/schema_migrations/20230119123259 b/db/schema_migrations/20230119123259 new file mode 100644 index 00000000000000..c4ffc37f790cc0 --- /dev/null +++ b/db/schema_migrations/20230119123259 @@ -0,0 +1 @@ +49e256cdd550386c989cb6edea22873547b96120cfd8b5652de532dbbe21928c \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 7cac710290a857..98960d0986a45f 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -29242,6 +29242,8 @@ CREATE UNIQUE INDEX index_ci_resource_groups_on_project_id_and_key ON ci_resourc CREATE INDEX index_ci_resources_on_build_id ON ci_resources USING btree (build_id); +CREATE INDEX index_ci_resources_on_partition_id_build_id ON ci_resources USING btree (partition_id, build_id); + CREATE UNIQUE INDEX index_ci_resources_on_resource_group_id_and_build_id ON ci_resources USING btree (resource_group_id, build_id); CREATE INDEX index_ci_runner_machines_on_contacted_at_desc_and_id_desc ON ci_runner_machines USING btree (contacted_at DESC, id DESC); @@ -34341,7 +34343,7 @@ ALTER TABLE ONLY issues ADD CONSTRAINT fk_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES epics(id) ON DELETE SET NULL; ALTER TABLE ONLY ci_resources - ADD CONSTRAINT fk_e169a8e3d5 FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE SET NULL; + ADD CONSTRAINT fk_e169a8e3d5_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE ONLY ci_sources_pipelines ADD CONSTRAINT fk_e1bad85861 FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE; diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index 0875bb4e92a921..8ce1dc2152d853 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -46,7 +46,7 @@ ci_pending_builds: %w[partition_id], ci_pipeline_variables: %w[partition_id], ci_pipelines: %w[partition_id], - ci_resources: %w[partition_id], + ci_resources: %w[partition_id build_id], ci_runner_projects: %w[runner_id], ci_running_builds: %w[partition_id], ci_sources_pipelines: %w[partition_id source_partition_id], -- GitLab