Add approval_policy_rule_id index and foreign key

What does this MR do and why?

This MR creates the approval_policy_rules foreign key synchronously on tables related to security policies. The index was created asynchronously in Add approval_policy_rule_id to approval rules a... (!155232 - merged)

Migration Output

main: == [advisory_lock_connection] object_id: 221260, pg_backend_pid: 40649
main: == 20240604111157 AddApprovalPolicyRulesFkOnApprovalGroupRules: migrating =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- index_exists?(:approval_group_rules, :approval_policy_rule_id, {:name=>"index_approval_group_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:approval_group_rules, :approval_policy_rule_id, {:name=>"index_approval_group_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0027s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE approval_group_rules ADD CONSTRAINT fk_2a74c6e52d FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("ALTER TABLE approval_group_rules VALIDATE CONSTRAINT fk_2a74c6e52d;")
main:    -> 0.0020s
main: == 20240604111157 AddApprovalPolicyRulesFkOnApprovalGroupRules: migrated (0.0612s) 
main: == [advisory_lock_connection] object_id: 221260, pg_backend_pid: 40649


main: == [advisory_lock_connection] object_id: 244920, pg_backend_pid: 40654
main: == 20240604111324 AddApprovalPolicyRulesFkOnApprovalProjectRules: migrating ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- index_exists?(:approval_project_rules, :approval_policy_rule_id, {:name=>"index_approval_project_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0065s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:approval_project_rules, :approval_policy_rule_id, {:name=>"index_approval_project_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE approval_project_rules ADD CONSTRAINT fk_773289d10b FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0010s
main: -- execute("ALTER TABLE approval_project_rules VALIDATE CONSTRAINT fk_773289d10b;")
main:    -> 0.0016s
main: == 20240604111324 AddApprovalPolicyRulesFkOnApprovalProjectRules: migrated (0.0480s) 
main: == [advisory_lock_connection] object_id: 244920, pg_backend_pid: 40654


main: == [advisory_lock_connection] object_id: 291100, pg_backend_pid: 40659
main: == 20240604111411 AddApprovalPolicyRulesFkOnApprovalMergeRequestRules: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0008s
main: -- index_exists?(:approval_merge_request_rules, :approval_policy_rule_id, {:name=>"index_approval_merge_request_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0089s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:approval_merge_request_rules, :approval_policy_rule_id, {:name=>"index_approval_merge_request_rules_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE approval_merge_request_rules ADD CONSTRAINT fk_73fec3d7e5 FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("ALTER TABLE approval_merge_request_rules VALIDATE CONSTRAINT fk_73fec3d7e5;")
main:    -> 0.0019s
main: == 20240604111411 AddApprovalPolicyRulesFkOnApprovalMergeRequestRules: migrated (0.0673s) 
main: == [advisory_lock_connection] object_id: 291100, pg_backend_pid: 40659


main: == [advisory_lock_connection] object_id: 332180, pg_backend_pid: 40664
main: == 20240604111512 AddApprovalPolicyRulesFkOnSoftwareLicensePolicies: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_exists?(:software_license_policies, :approval_policy_rule_id, {:name=>"index_software_license_policies_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0054s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:software_license_policies, :approval_policy_rule_id, {:name=>"index_software_license_policies_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0021s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE software_license_policies ADD CONSTRAINT fk_dca6a58d53 FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0013s
main: -- execute("ALTER TABLE software_license_policies VALIDATE CONSTRAINT fk_dca6a58d53;")
main:    -> 0.0027s
main: == 20240604111512 AddApprovalPolicyRulesFkOnSoftwareLicensePolicies: migrated (0.0471s) 
main: == [advisory_lock_connection] object_id: 332180, pg_backend_pid: 40664


main: == [advisory_lock_connection] object_id: 370040, pg_backend_pid: 40669
main: == 20240604111555 AddApprovalPolicyRulesFkOnScanResultPolicyViolations: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:scan_result_policy_violations, :approval_policy_rule_id, {:name=>"index_scan_result_policy_violations_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0065s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- add_index(:scan_result_policy_violations, :approval_policy_rule_id, {:name=>"index_scan_result_policy_violations_on_approval_policy_rule_id", :algorithm=>:concurrently})
main:    -> 0.0037s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE scan_result_policy_violations ADD CONSTRAINT fk_3d58aa6aee FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0022s
main: -- execute("ALTER TABLE scan_result_policy_violations VALIDATE CONSTRAINT fk_3d58aa6aee;")
main:    -> 0.0013s
main: == 20240604111555 AddApprovalPolicyRulesFkOnScanResultPolicyViolations: migrated (0.0553s) 
main: == [advisory_lock_connection] object_id: 370040, pg_backend_pid: 40669

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Addresses #416260 (closed)