Add trigger to update issues.correct_work_item_type_id. Also add invalid FK

What does this MR do and why?

We are going to backfill issues.correct_work_item_type_id so we need to make sure new records and updates to the issues.work_item_type_id column are also reflected on the new issues.correct_work_item_type_id column.

We also need to add a FK for the new column. We cannot validate the FK until the backfill is finalized in %17.6

Notes for DB review

• This MR is based on the PoC Add new "correct id" to work item types and bac... (!155416 - closed) with some differences
• Trigger is not run conditionally !155416 (diffs). If we do it conditionally, we would loose sync with records that get backfilled. While we backfill, the trigger needs to run for every record in the issues table, not only for those that haven't been backfilled yet as in the PoC.
• Not restricting DDL migration to main !155416 (diffs) as it adds a function and a trigger
• Not adding index on the FK column as in past backfills we did see improved query times if no index was present due to HOT updates. Hot updates do happen even if the fillfactor is not decreased as stated in the docs

Migration output

UP

main: == [advisory_lock_connection] object_id: 129100, pg_backend_pid: 85431
main: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: migrating ======
main: -- execute("CREATE OR REPLACE FUNCTION update_issue_correct_work_item_type_id_sync_event()\nRETURNS TRIGGER AS\n$$\nBEGIN\nSELECT \"correct_id\"\nINTO NEW.\"correct_work_item_type_id\"\nFROM \"work_item_types\"\nWHERE \"work_item_types\".\"id\" = NEW.\"work_item_type_id\";\nRETURN NEW;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0016s
main: -- execute("CREATE TRIGGER trigger_correct_work_item_type_id_sync_event_on_issue_update\nBEFORE INSERT OR UPDATE of work_item_type_id ON issues\nFOR EACH ROW\n\nEXECUTE FUNCTION update_issue_correct_work_item_type_id_sync_event()\n")
main:    -> 0.0006s
main: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: migrated (0.0052s)

main: == [advisory_lock_connection] object_id: 129100, pg_backend_pid: 85431
ci: == [advisory_lock_connection] object_id: 129340, pg_backend_pid: 85433
ci: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: migrating ======
ci: -- execute("CREATE OR REPLACE FUNCTION update_issue_correct_work_item_type_id_sync_event()\nRETURNS TRIGGER AS\n$$\nBEGIN\nSELECT \"correct_id\"\nINTO NEW.\"correct_work_item_type_id\"\nFROM \"work_item_types\"\nWHERE \"work_item_types\".\"id\" = NEW.\"work_item_type_id\";\nRETURN NEW;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci:    -> 0.0015s
ci: -- execute("CREATE TRIGGER trigger_correct_work_item_type_id_sync_event_on_issue_update\nBEFORE INSERT OR UPDATE of work_item_type_id ON issues\nFOR EACH ROW\n\nEXECUTE FUNCTION update_issue_correct_work_item_type_id_sync_event()\n")
ci:    -> 0.0008s
ci: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: migrated (0.0116s)

ci: == [advisory_lock_connection] object_id: 129340, pg_backend_pid: 85433
main: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 85436
main: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: migrating ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0106s
main: -- index_exists?(:work_item_types, :correct_id, {:unique=>true, :name=>"index_work_item_types_on_correct_id_unique", :algorithm=>:concurrently})
main:    -> 0.0021s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:work_item_types, :correct_id, {:unique=>true, :name=>"index_work_item_types_on_correct_id_unique", :algorithm=>:concurrently})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: migrated (0.0249s)

main: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 85436
ci: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 85438
ci: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: migrating ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:work_item_types, :correct_id, {:unique=>true, :name=>"index_work_item_types_on_correct_id_unique", :algorithm=>:concurrently})
ci:    -> 0.0017s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:work_item_types, :correct_id, {:unique=>true, :name=>"index_work_item_types_on_correct_id_unique", :algorithm=>:concurrently})
ci:    -> 0.0027s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: migrated (0.0163s)

ci: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 85438
main: == [advisory_lock_connection] object_id: 130900, pg_backend_pid: 85442
main: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: migrating ===============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE issues ADD CONSTRAINT fk_1adaba52b0 FOREIGN KEY (correct_work_item_type_id) REFERENCES work_item_types (correct_id) NOT VALID;")
main:    -> 0.0018s
main: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: migrated (0.0160s) ======

main: == [advisory_lock_connection] object_id: 130900, pg_backend_pid: 85442
ci: == [advisory_lock_connection] object_id: 140980, pg_backend_pid: 85444
ci: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: migrating ===============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE issues ADD CONSTRAINT fk_1adaba52b0 FOREIGN KEY (correct_work_item_type_id) REFERENCES work_item_types (correct_id) NOT VALID;")
ci:    -> 0.0010s
ci: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: migrated (0.0159s) ======

ci: == [advisory_lock_connection] object_id: 140980, pg_backend_pid: 85444

DOWN

main: == [advisory_lock_connection] object_id: 128680, pg_backend_pid: 83713
main: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: reverting ===============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:issues, {:column=>:correct_work_item_type_id})
main:    -> 0.0030s
main: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: reverted (0.0380s) ======

main: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: reverting ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- indexes(:work_item_types)
main:    -> 0.0019s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:work_item_types, {:algorithm=>:concurrently, :name=>"index_work_item_types_on_correct_id_unique"})
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: reverted (0.0153s)

main: == [advisory_lock_connection] object_id: 128680, pg_backend_pid: 83713

main: == [advisory_lock_connection] object_id: 128660, pg_backend_pid: 84535
main: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: reverting ======
main: -- execute("DROP TRIGGER IF EXISTS trigger_correct_work_item_type_id_sync_event_on_issue_update ON issues")
main:    -> 0.0011s
main: -- execute("DROP FUNCTION IF EXISTS update_issue_correct_work_item_type_id_sync_event()")
main:    -> 0.0003s
main: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: reverted (0.0045s)

main: == [advisory_lock_connection] object_id: 128660, pg_backend_pid: 84535

ci: == [advisory_lock_connection] object_id: 147440, pg_backend_pid: 83976
ci: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: reverting ===============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:issues, {:column=>:correct_work_item_type_id})
ci:    -> 0.0029s
ci: == 20240928010637 AddFkToIssuesCorrectWorkItemTypeId: reverted (0.0223s) ======

ci: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: reverting ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:work_item_types)
ci:    -> 0.0031s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0008s
ci: -- remove_index(:work_item_types, {:algorithm=>:concurrently, :name=>"index_work_item_types_on_correct_id_unique"})
ci:    -> 0.0032s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20240928010520 AddUniqueConstraintToWorkItemTypesCorrectId: reverted (0.0327s)

ci: == [advisory_lock_connection] object_id: 147440, pg_backend_pid: 83976

ci: == [advisory_lock_connection] object_id: 129840, pg_backend_pid: 84793
ci: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: reverting ======
ci: -- execute("DROP TRIGGER IF EXISTS trigger_correct_work_item_type_id_sync_event_on_issue_update ON issues")
ci:    -> 0.0009s
ci: -- execute("DROP FUNCTION IF EXISTS update_issue_correct_work_item_type_id_sync_event()")
ci:    -> 0.0004s
ci: == 20240927213854 AddSyncCorrectWorkItemTypeIdTriggerToIssues: reverted (0.0084s)

ci: == [advisory_lock_connection] object_id: 129840, pg_backend_pid: 84793

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.

Related to #493974 (closed)