Skip to content

Add runner_machine_id foreign key to p_ci_builds_metadata

Pedro Pombeiro requested to merge pedropombeiro/386755/2-add-fk-on-ci_builds_metadata into master

What does this MR do and why?

Describe in detail what your merge request does and why.

This follows up on !108165 (merged) now that the async index has been created, to do the following main closing actions:

  • Create the index on the partition metadata table p_ci_builds_metadata;
  • Add foreign key constraint concurrently on runner_machine_id.

The rationale is discussed here: !108024 (comment 1227479248)

NOTE: This MR is probably best reviewed commit-by-commit.

Closes #386755 (closed)

Screenshots or screen recordings

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

Database migration output

up 
main: == 20230104222438 AddPartitionIndexToBuildsMetadata: migrating ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_name_exists?(:p_ci_builds_metadata, "index_p_ci_builds_metadata_on_runner_machine_id_where_not_null")
main:    -> 0.0112s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?("public.ci_builds_metadata", :runner_machine_id, {:name=>"index_42f5b5f39f", :where=>"runner_machine_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index("public.ci_builds_metadata", :runner_machine_id, {:name=>"index_42f5b5f39f", :where=>"runner_machine_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0059s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- add_index(:p_ci_builds_metadata, :runner_machine_id, {:name=>"index_p_ci_builds_metadata_on_runner_machine_id_where_not_null", :where=>"runner_machine_id IS NOT NULL"})
main:    -> 0.0021s
main: == 20230104222438 AddPartitionIndexToBuildsMetadata: migrated (0.1329s) =======

main: == 20230104222514 AddForeignKeyToBuildsMetadata: migrating ====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE public.ci_builds_metadata\nADD CONSTRAINT fk_rails_fae01b2700\nFOREIGN KEY (runner_machine_id)\nREFERENCES ci_runner_machines (id)\n\nON DELETE SET NULL\nNOT VALID;\n")
main:    -> 0.0062s
main: -- execute("ALTER TABLE public.ci_builds_metadata VALIDATE CONSTRAINT fk_rails_fae01b2700;")
main:    -> 0.0031s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- add_foreign_key(:p_ci_builds_metadata, :ci_runner_machines, {:column=>:runner_machine_id, :on_delete=>:nullify, :name=>"fk_rails_fae01b2700", :validate=>true})
main:    -> 0.0019s
main: == 20230104222514 AddForeignKeyToBuildsMetadata: migrated (0.0584s) ===========

ci: == 20230104222438 AddPartitionIndexToBuildsMetadata: migrating ================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_name_exists?(:p_ci_builds_metadata, "index_p_ci_builds_metadata_on_runner_machine_id_where_not_null")
ci:    -> 0.0018s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?("public.ci_builds_metadata", :runner_machine_id, {:name=>"index_42f5b5f39f", :where=>"runner_machine_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0029s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index("public.ci_builds_metadata", :runner_machine_id, {:name=>"index_42f5b5f39f", :where=>"runner_machine_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.4624s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- add_index(:p_ci_builds_metadata, :runner_machine_id, {:name=>"index_p_ci_builds_metadata_on_runner_machine_id_where_not_null", :where=>"runner_machine_id IS NOT NULL"})
ci:    -> 0.0019s
ci: == 20230104222438 AddPartitionIndexToBuildsMetadata: migrated (0.4881s) =======

ci: == 20230104222514 AddForeignKeyToBuildsMetadata: migrating ====================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE public.ci_builds_metadata\nADD CONSTRAINT fk_rails_fae01b2700\nFOREIGN KEY (runner_machine_id)\nREFERENCES ci_runner_machines (id)\n\nON DELETE SET NULL\nNOT VALID;\n")
ci:    -> 0.0046s
ci: -- execute("ALTER TABLE public.ci_builds_metadata VALIDATE CONSTRAINT fk_rails_fae01b2700;")
ci:    -> 0.0027s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- add_foreign_key(:p_ci_builds_metadata, :ci_runner_machines, {:column=>:runner_machine_id, :on_delete=>:nullify, :name=>"fk_rails_fae01b2700", :validate=>true})
ci:    -> 0.0012s
ci: == 20230104222514 AddForeignKeyToBuildsMetadata: migrated (0.0328s) ===========
down 
main: == 20230104222514 AddForeignKeyToBuildsMetadata: reverting ====================
main: -- remove_foreign_key(:p_ci_builds_metadata, {:column=>:runner_machine_id})
main:    -> 0.0031s
main: == 20230104222514 AddForeignKeyToBuildsMetadata: reverted (0.2094s) ===========

  11.27s user 5.67s system 75% cpu 22.443 total
ci: == 20230104222514 AddForeignKeyToBuildsMetadata: reverting ====================
ci: -- remove_foreign_key(:p_ci_builds_metadata, {:column=>:runner_machine_id})
ci:    -> 0.0026s
ci: == 20230104222514 AddForeignKeyToBuildsMetadata: reverted (0.2072s) ===========

  11.16s user 5.67s system 67% cpu 24.767 total
main: == 20230104222438 AddPartitionIndexToBuildsMetadata: reverting ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_name_exists?(:p_ci_builds_metadata, "index_p_ci_builds_metadata_on_runner_machine_id_where_not_null")
main:    -> 0.0111s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_index(:p_ci_builds_metadata, {:name=>"index_p_ci_builds_metadata_on_runner_machine_id_where_not_null"})
main:    -> 0.0008s
main: == 20230104222438 AddPartitionIndexToBuildsMetadata: reverted (0.2178s) =======

  12.06s user 6.40s system 65% cpu 28.041 total
ci: == 20230104222438 AddPartitionIndexToBuildsMetadata: reverting ================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_name_exists?(:p_ci_builds_metadata, "index_p_ci_builds_metadata_on_runner_machine_id_where_not_null")
ci:    -> 0.0114s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_index(:p_ci_builds_metadata, {:name=>"index_p_ci_builds_metadata_on_runner_machine_id_where_not_null"})
ci:    -> 0.0019s
ci: == 20230104222438 AddPartitionIndexToBuildsMetadata: reverted (0.2263s) =======

How to set up and validate locally

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

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

Merge request reports