Skip to content

Update approval_policy_rule_id fk to nullify on delete

Sashi Kumar Kumaresan requested to merge sk/416262-merged-mr-index into master

What does this MR do and why?

As a part of Use database read model for merge request appr... (&9971) we are introducing approval_policy_rules table to store the values from YAML and refer it to approval_merge_request_rules table. Currently, the approval_policy_rule_id foreign key is set to ON DELETE CASCADE. This will cause the approval rules to be deleted when a policy is deleted. But, after Stop deleting merged MRs merge request approval... (#432769 - closed) we no longer want to delete the approval rules for MRs that are merged for compliance reasons. So this MR updates the foreign key to ON DELETE SET NULL similar to Stop deleting merged MRs approval rules (!141036 - merged)

Migration output

main: == [advisory_lock_connection] object_id: 133480, pg_backend_pid: 21332
main: == 20240918130318 ReplaceFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrating 
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_approval_merge_request_rules_on_approval_policy_rule_id FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules (id) ON DELETE SET NULL NOT VALID;")
main:    -> 0.0026s
main: == 20240918130318 ReplaceFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrated (0.1139s) 
main: == [advisory_lock_connection] object_id: 141460, pg_backend_pid: 21337
main: == 20240918130409 ValidateFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrating 
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0112s
main: -- execute("ALTER TABLE approval_merge_request_rules VALIDATE CONSTRAINT fk_approval_merge_request_rules_on_approval_policy_rule_id;")
main:    -> 0.0037s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20240918130409 ValidateFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrated (0.0333s) 
main: == [advisory_lock_connection] object_id: 150620, pg_backend_pid: 21342
main: == 20240918130437 RemoveOldFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrating 
main: -- remove_foreign_key(:approval_merge_request_rules, {:column=>:approval_policy_rule_id, :on_delete=>:cascade, :name=>"fk_73fec3d7e5"})
main:    -> 0.0054s
main: == 20240918130437 RemoveOldFkOnApprovalMergeRequestRulesApprovalPolicyRuleId: migrated (0.0214s)

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.

Merge request reports