From eda06ec1bda8ed2642459268d9b2047054460ea7 Mon Sep 17 00:00:00 2001 From: Marius Bobin Date: Tue, 17 Sep 2024 15:02:10 +0300 Subject: [PATCH] Redefine broken foreign keys on p_ci_pipelines Changelog: other --- ...e_id_for_p_ci_builds_and_p_ci_pipelines.rb | 43 +++++++++++++++++++ ...e_id_for_p_ci_builds_and_p_ci_pipelines.rb | 43 +++++++++++++++++++ ...i_pipeline_variables_and_p_ci_pipelines.rb | 42 ++++++++++++++++++ ...n_fk_for_p_ci_stages_and_p_ci_pipelines.rb | 42 ++++++++++++++++++ ...ds_execution_configs_and_p_ci_pipelines.rb | 42 ++++++++++++++++++ ...n_fk_for_p_ci_builds_and_p_ci_pipelines.rb | 42 ++++++++++++++++++ ...d_fk_to_p_ci_pipelines_from_p_ci_builds.rb | 40 +++++++++++++++++ db/schema_migrations/20240917110159 | 1 + db/schema_migrations/20240917111256 | 1 + db/schema_migrations/20240917112258 | 1 + db/schema_migrations/20240917112629 | 1 + db/schema_migrations/20240917113250 | 1 + db/schema_migrations/20240917114903 | 1 + db/schema_migrations/20240917115355 | 1 + db/structure.sql | 18 ++++++-- 15 files changed, 316 insertions(+), 3 deletions(-) create mode 100644 db/post_migrate/20240917110159_partition_fk_upstream_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917111256_partition_fk_auto_canceled_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917112258_partition_fk_for_p_ci_pipeline_variables_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917112629_partition_fk_for_p_ci_stages_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917113250_partition_fk_for_p_ci_builds_execution_configs_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917114903_partition_fk_for_p_ci_builds_and_p_ci_pipelines.rb create mode 100644 db/post_migrate/20240917115355_revert_add_fk_to_p_ci_pipelines_from_p_ci_builds.rb create mode 100644 db/schema_migrations/20240917110159 create mode 100644 db/schema_migrations/20240917111256 create mode 100644 db/schema_migrations/20240917112258 create mode 100644 db/schema_migrations/20240917112629 create mode 100644 db/schema_migrations/20240917113250 create mode 100644 db/schema_migrations/20240917114903 create mode 100644 db/schema_migrations/20240917115355 diff --git a/db/post_migrate/20240917110159_partition_fk_upstream_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb b/db/post_migrate/20240917110159_partition_fk_upstream_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..431c737fd03a1a --- /dev/null +++ b/db/post_migrate/20240917110159_partition_fk_upstream_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb @@ -0,0 +1,43 @@ +# frozen_string_literal: true + +class PartitionFkUpstreamPipelineIdForPCiBuildsAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_builds + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :upstream_pipeline_id + PARTITION_COLUMN = :upstream_pipeline_partition_id + TARGET_COLUMN = :id + TARGET_PARTITION_COLUMN = :partition_id + FK_NAME = :fk_rails_4540ead625_p + + def up + add_concurrent_partitioned_foreign_key( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + column: [PARTITION_COLUMN, COLUMN], + target_column: [TARGET_PARTITION_COLUMN, TARGET_COLUMN], + validate: false, + reverse_lock_order: true, + on_update: :cascade, + on_delete: :cascade, + name: FK_NAME + ) + end + + def down + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917111256_partition_fk_auto_canceled_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb b/db/post_migrate/20240917111256_partition_fk_auto_canceled_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..eb4818a5a11854 --- /dev/null +++ b/db/post_migrate/20240917111256_partition_fk_auto_canceled_pipeline_id_for_p_ci_builds_and_p_ci_pipelines.rb @@ -0,0 +1,43 @@ +# frozen_string_literal: true + +class PartitionFkAutoCanceledPipelineIdForPCiBuildsAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_builds + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :auto_canceled_by_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :auto_canceled_by_partition_id + PARTITION_TARGET_COLUMN = :partition_id + FK_NAME = :fk_rails_494e57ee78_p + + def up + add_concurrent_partitioned_foreign_key( + SOURCE_TABLE_NAME, + TARGET_TABLE_NAME, + column: [PARTITION_COLUMN, COLUMN], + target_column: [PARTITION_TARGET_COLUMN, TARGET_COLUMN], + validate: false, + reverse_lock_order: true, + on_update: :cascade, + on_delete: :nullify, + name: FK_NAME + ) + end + + def down + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917112258_partition_fk_for_p_ci_pipeline_variables_and_p_ci_pipelines.rb b/db/post_migrate/20240917112258_partition_fk_for_p_ci_pipeline_variables_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..74ecbf5acebf89 --- /dev/null +++ b/db/post_migrate/20240917112258_partition_fk_for_p_ci_pipeline_variables_and_p_ci_pipelines.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +class PartitionFkForPCiPipelineVariablesAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_pipeline_variables + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :pipeline_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :partition_id + FK_NAME = :fk_rails_507416c33a_p + + def up + add_concurrent_partitioned_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 + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917112629_partition_fk_for_p_ci_stages_and_p_ci_pipelines.rb b/db/post_migrate/20240917112629_partition_fk_for_p_ci_stages_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..272dd7ac4d7b91 --- /dev/null +++ b/db/post_migrate/20240917112629_partition_fk_for_p_ci_stages_and_p_ci_pipelines.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +class PartitionFkForPCiStagesAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_stages + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :pipeline_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :partition_id + FK_NAME = :fk_rails_5d4d96d44b_p + + def up + add_concurrent_partitioned_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 + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917113250_partition_fk_for_p_ci_builds_execution_configs_and_p_ci_pipelines.rb b/db/post_migrate/20240917113250_partition_fk_for_p_ci_builds_execution_configs_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..cd35afbcd31312 --- /dev/null +++ b/db/post_migrate/20240917113250_partition_fk_for_p_ci_builds_execution_configs_and_p_ci_pipelines.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +class PartitionFkForPCiBuildsExecutionConfigsAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_builds_execution_configs + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :pipeline_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :partition_id + FK_NAME = :fk_rails_e214655a86_p + + def up + add_concurrent_partitioned_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 + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917114903_partition_fk_for_p_ci_builds_and_p_ci_pipelines.rb b/db/post_migrate/20240917114903_partition_fk_for_p_ci_builds_and_p_ci_pipelines.rb new file mode 100644 index 00000000000000..d4c03c84ccc744 --- /dev/null +++ b/db/post_migrate/20240917114903_partition_fk_for_p_ci_builds_and_p_ci_pipelines.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +class PartitionFkForPCiBuildsAndPCiPipelines < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_builds + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :commit_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :partition_id + FK_NAME = :fk_rails_d739f46384_p + + def up + add_concurrent_partitioned_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 + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end +end diff --git a/db/post_migrate/20240917115355_revert_add_fk_to_p_ci_pipelines_from_p_ci_builds.rb b/db/post_migrate/20240917115355_revert_add_fk_to_p_ci_pipelines_from_p_ci_builds.rb new file mode 100644 index 00000000000000..3e9a1210c52954 --- /dev/null +++ b/db/post_migrate/20240917115355_revert_add_fk_to_p_ci_pipelines_from_p_ci_builds.rb @@ -0,0 +1,40 @@ +# frozen_string_literal: true + +class RevertAddFkToPCiPipelinesFromPCiBuilds < Gitlab::Database::Migration[2.2] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '17.5' + disable_ddl_transaction! + + SOURCE_TABLE_NAME = :p_ci_builds + TARGET_TABLE_NAME = :p_ci_pipelines + COLUMN = :commit_id + TARGET_COLUMN = :id + PARTITION_COLUMN = :partition_id + FK_NAME = :fk_d3130c9a7f_p_tmp + + def up + Gitlab::Database::PostgresPartitionedTable.each_partition(SOURCE_TABLE_NAME) do |partition| + with_lock_retries do + remove_foreign_key_if_exists( + partition.identifier, + TARGET_TABLE_NAME, + name: FK_NAME, + reverse_lock_order: true + ) + end + end + end + + def down + add_concurrent_partitioned_foreign_key(SOURCE_TABLE_NAME, TARGET_TABLE_NAME, + column: [PARTITION_COLUMN, COLUMN], + target_column: [PARTITION_COLUMN, TARGET_COLUMN], + name: FK_NAME, + on_update: :cascade, + on_delete: :cascade, + reverse_lock_order: true, + validate: false + ) + end +end diff --git a/db/schema_migrations/20240917110159 b/db/schema_migrations/20240917110159 new file mode 100644 index 00000000000000..55011511a8f6bb --- /dev/null +++ b/db/schema_migrations/20240917110159 @@ -0,0 +1 @@ +62e985a2bbe532c4b8f3ef21fffdfea7804beb38f17c0b3f35d2dcfdc3737a36 \ No newline at end of file diff --git a/db/schema_migrations/20240917111256 b/db/schema_migrations/20240917111256 new file mode 100644 index 00000000000000..0767392ace7ab9 --- /dev/null +++ b/db/schema_migrations/20240917111256 @@ -0,0 +1 @@ +e343f8491dbb575278a70a64cbbc8ff467e23957d783f726d8103b3451486d1a \ No newline at end of file diff --git a/db/schema_migrations/20240917112258 b/db/schema_migrations/20240917112258 new file mode 100644 index 00000000000000..e4fc22d00bb10c --- /dev/null +++ b/db/schema_migrations/20240917112258 @@ -0,0 +1 @@ +5362b5727cbe392faf1bf134916a9f0c196311659a47e29a50236127bf530839 \ No newline at end of file diff --git a/db/schema_migrations/20240917112629 b/db/schema_migrations/20240917112629 new file mode 100644 index 00000000000000..9c51013b09e454 --- /dev/null +++ b/db/schema_migrations/20240917112629 @@ -0,0 +1 @@ +251f54f8da0ff364e1c5390df8712dae57036e3b4c65f3c067348e299876e281 \ No newline at end of file diff --git a/db/schema_migrations/20240917113250 b/db/schema_migrations/20240917113250 new file mode 100644 index 00000000000000..a60ec29b9a114e --- /dev/null +++ b/db/schema_migrations/20240917113250 @@ -0,0 +1 @@ +79127b157358ca8fa5c936ed4022a3388f4221e541f7f615f543026063e9af58 \ No newline at end of file diff --git a/db/schema_migrations/20240917114903 b/db/schema_migrations/20240917114903 new file mode 100644 index 00000000000000..895df4274d9397 --- /dev/null +++ b/db/schema_migrations/20240917114903 @@ -0,0 +1 @@ +7c789154b378a74bd701c4f3acc91ce6fdf19e5a41de4c7ae0f384d7b089acb8 \ No newline at end of file diff --git a/db/schema_migrations/20240917115355 b/db/schema_migrations/20240917115355 new file mode 100644 index 00000000000000..4f5acc4f375096 --- /dev/null +++ b/db/schema_migrations/20240917115355 @@ -0,0 +1 @@ +ec6e87cf971f7a35d75136ffe4f80666428eff71e804493f9af687d09ba86292 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 4e305a6e396572..7103b754705d7b 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -34667,9 +34667,6 @@ ALTER TABLE ONLY environments ALTER TABLE p_ci_builds ADD CONSTRAINT fk_d3130c9a7f_p FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE ONLY ci_builds - ADD CONSTRAINT fk_d3130c9a7f_p_tmp FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY boards_epic_user_preferences ADD CONSTRAINT fk_d32c3d693c FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -35468,6 +35465,9 @@ ALTER TABLE ONLY packages_dependency_links ALTER TABLE ONLY work_item_related_link_restrictions ADD CONSTRAINT fk_rails_4513f0061c FOREIGN KEY (target_type_id) REFERENCES work_item_types(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_builds + ADD CONSTRAINT fk_rails_4540ead625_p FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY project_auto_devops ADD CONSTRAINT fk_rails_45436b12b2 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; @@ -35489,6 +35489,9 @@ ALTER TABLE ONLY vulnerability_feedback ALTER TABLE ONLY user_custom_attributes ADD CONSTRAINT fk_rails_47b91868a8 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_builds + ADD CONSTRAINT fk_rails_494e57ee78_p FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID; + ALTER TABLE ONLY upcoming_reconciliations ADD CONSTRAINT fk_rails_497b4938ac FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -35534,6 +35537,9 @@ ALTER TABLE ONLY resource_iteration_events ALTER TABLE ONLY status_page_settings ADD CONSTRAINT fk_rails_506e5ba391 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_pipeline_variables + ADD CONSTRAINT fk_rails_507416c33a_p FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY ci_pipeline_metadata ADD CONSTRAINT fk_rails_50c1e9ea10_p FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE; @@ -35636,6 +35642,9 @@ ALTER TABLE ONLY reviews ALTER TABLE ONLY ci_running_builds ADD CONSTRAINT fk_rails_5ca491d360 FOREIGN KEY (runner_id) REFERENCES ci_runners(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_stages + ADD CONSTRAINT fk_rails_5d4d96d44b_p FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY epic_issues ADD CONSTRAINT fk_rails_5d942936b4 FOREIGN KEY (epic_id) REFERENCES epics(id) ON DELETE CASCADE; @@ -36527,6 +36536,9 @@ ALTER TABLE p_ci_job_annotations ALTER TABLE ONLY packages_rpm_repository_files ADD CONSTRAINT fk_rails_d545cfaed2 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; +ALTER TABLE ONLY ci_builds + ADD CONSTRAINT fk_rails_d739f46384_p FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID; + ALTER TABLE ONLY packages_rpm_metadata ADD CONSTRAINT fk_rails_d79f02264b FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE; -- GitLab