Skip to content

Add reference from p_ci_runner_machine_builds to ci_runner_machines

Pedro Pombeiro (OOO from July 16th till Aug 7th) requested to merge pedropombeiro/392651/add-runner_machine_id_fk into master

What does this MR do and why?

Describe in detail what your merge request does and why.

This MR adds a missing loose foreign key to p_ci_runner_machine_builds pointing to ci_runner_machines so that deleted ci_runner_machines trigger a deletion of the join record in p_ci_runner_machine_builds. It also truncates the p_ci_runner_machine_builds and ci_runner_machines tables.

To be able to add the missing foreign key, we need to do the following work:

  • Drop the FK constraint from p_ci_builds_metadata to ci_runner_machines, so that we can
  • Truncate the ci_runner_machines table;
  • Truncate the p_ci_runner_machine_builds table.

Context: !112544 (comment 1297852982)

Closes #392651 (closed), #390858 (closed), and #391965 (closed)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

image

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Migration

up 
main: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: migrating ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key("p_ci_builds_metadata", "ci_runner_machines", {:name=>"fk_rails_fae01b2700"})
main:    -> 0.0032s
main: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: migrated (0.1735s)

main: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: migrating ====================
main: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: migrated (0.0023s) ===========

main: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: migrating ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_name_exists?(:p_ci_runner_machine_builds, "index_p_ci_runner_machine_builds_on_runner_machine_id")
main:    -> 0.0011s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?("gitlab_partitions_dynamic.ci_runner_machine_builds_100", :runner_machine_id, {:unique=>false, :name=>"index_c8ef2fc2a4", :algorithm=>:concurrently})
main:    -> 0.0022s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- add_index("gitlab_partitions_dynamic.ci_runner_machine_builds_100", :runner_machine_id, {:unique=>false, :name=>"index_c8ef2fc2a4", :algorithm=>:concurrently})
main:    -> 0.0058s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- add_index(:p_ci_runner_machine_builds, :runner_machine_id, {:unique=>false, :name=>"index_p_ci_runner_machine_builds_on_runner_machine_id"})
main:    -> 0.0014s
main: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: migrated (0.0344s)

main: == 20230307085644 TrackCiRunnerMachineRecordChanges: migrating ================
main: -- execute("CREATE TRIGGER ci_runner_machines_loose_fk_trigger\nAFTER DELETE ON ci_runner_machines REFERENCING OLD TABLE AS old_table\nFOR EACH STATEMENT\nEXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records();\n")
main:    -> 0.0005s
main: == 20230307085644 TrackCiRunnerMachineRecordChanges: migrated (0.0029s) =======

ci: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: migrating ===
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key("p_ci_builds_metadata", "ci_runner_machines", {:name=>"fk_rails_fae01b2700"})
ci:    -> 0.0030s
ci: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: migrated (0.0213s)

ci: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: migrating ====================
ci: -- execute("TRUNCATE TABLE ci_runner_machines, p_ci_runner_machine_builds")
ci:    -> 0.0112s
ci: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: migrated (0.0181s) ===========

ci: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: migrating ==
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_name_exists?(:p_ci_runner_machine_builds, "index_p_ci_runner_machine_builds_on_runner_machine_id")
ci:    -> 0.0010s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?("gitlab_partitions_dynamic.ci_runner_machine_builds_100", :runner_machine_id, {:unique=>false, :name=>"index_c8ef2fc2a4", :algorithm=>:concurrently})
ci:    -> 0.0019s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index("gitlab_partitions_dynamic.ci_runner_machine_builds_100", :runner_machine_id, {:unique=>false, :name=>"index_c8ef2fc2a4", :algorithm=>:concurrently})
ci:    -> 0.0027s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- add_index(:p_ci_runner_machine_builds, :runner_machine_id, {:unique=>false, :name=>"index_p_ci_runner_machine_builds_on_runner_machine_id"})
ci:    -> 0.0011s
ci: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: migrated (0.0264s)

ci: == 20230307085644 TrackCiRunnerMachineRecordChanges: migrating ================
ci: -- execute("CREATE TRIGGER ci_runner_machines_loose_fk_trigger\nAFTER DELETE ON ci_runner_machines REFERENCING OLD TABLE AS old_table\nFOR EACH STATEMENT\nEXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records();\n")
ci:    -> 0.0005s
ci: == 20230307085644 TrackCiRunnerMachineRecordChanges: migrated (0.0072s) =======
down 
main: == 20230307085644 TrackCiRunnerMachineRecordChanges: reverting ================
main: -- execute("DROP TRIGGER IF EXISTS ci_runner_machines_loose_fk_trigger ON ci_runner_machines")
main:    -> 0.0018s
main: == 20230307085644 TrackCiRunnerMachineRecordChanges: reverted (0.0065s) =======

  10.49s user 5.29s system 75% cpu 20.971 total
ci: == 20230307085644 TrackCiRunnerMachineRecordChanges: reverting ================
ci: -- execute("DROP TRIGGER IF EXISTS ci_runner_machines_loose_fk_trigger ON ci_runner_machines")
ci:    -> 0.0017s
ci: == 20230307085644 TrackCiRunnerMachineRecordChanges: reverted (0.0112s) =======

  11.10s user 6.25s system 72% cpu 24.089 total
main: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: reverting ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_name_exists?(:p_ci_runner_machine_builds, "index_p_ci_runner_machine_builds_on_runner_machine_id")
main:    -> 0.0077s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_index(:p_ci_runner_machine_builds, {:name=>"index_p_ci_runner_machine_builds_on_runner_machine_id"})
main:    -> 0.0008s
main: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: reverted (0.1873s)

  11.15s user 6.11s system 74% cpu 23.098 total
ci: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: reverting ==
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_name_exists?(:p_ci_runner_machine_builds, "index_p_ci_runner_machine_builds_on_runner_machine_id")
ci:    -> 0.0062s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_index(:p_ci_runner_machine_builds, {:name=>"index_p_ci_runner_machine_builds_on_runner_machine_id"})
ci:    -> 0.0009s
ci: == 20230302123301 AddRunnerMachineForeignKeyToRunnerMachineBuilds: reverted (0.1432s)

  10.61s user 5.68s system 78% cpu 20.798 total
main: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: reverting ====================
main: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: reverted (0.0039s) ===========

  11.40s user 5.79s system 86% cpu 19.924 total
ci: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: reverting ====================
ci: == 20230302123259 EnsureCiRunnerMachinesIsEmpty: reverted (0.0079s) ===========

  10.94s user 5.93s system 80% cpu 20.836 total
main: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: reverting ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE p_ci_builds_metadata\nADD CONSTRAINT fk_rails_fae01b2700 FOREIGN KEY (runner_machine_id)\n  REFERENCES ci_runner_machines(id) ON DELETE SET NULL\n")
main:    -> 0.0072s
main: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: reverted (0.1660s)

  10.69s user 5.92s system 76% cpu 21.854 total
ci: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: reverting ===
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE p_ci_builds_metadata\nADD CONSTRAINT fk_rails_fae01b2700 FOREIGN KEY (runner_machine_id)\n  REFERENCES ci_runner_machines(id) ON DELETE SET NULL\n")
ci:    -> 0.0099s
ci: == 20230302123258 DropRunnerMachinesConstraintOnCiBuildsMetadata: reverted (0.3846s)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Pedro Pombeiro (OOO from July 16th till Aug 7th)

Merge request reports