Skip to content

Fix existing incidents issue_type/work_item column out of sync

Mario Celi requested to merge 403158-fix-incident-records into master

What does this MR do and why?

Due to #403142 (closed) we need to fix existing incident records after merging the fix in !116189 (merged)

Rescheduling backfill only for 1 issue type. In !96591 (merged) we did it for all existing types. The only difference now is that we have added back the index on issues.work_item_type_id so this should prevent HOT updates from happening.

Database review

Record count and run time

DB job output like !117728 (comment 1357593840) time estimates are not accurate because we are not iterating over all records in the issues table, but just in a small subset of records

More details in #403142 (comment 1353790340) (internal only)

Migration Output

UP

bin/rails db:migrate                                                                                                                                                                                     403158-fix-incident-records ✭ ✖ ✱
main: == [advisory_lock_connection] object_id: 274040, pg_backend_pid: 32216
main: == 20230414150202 AddIssuesIncidentTypeTempIndex: migrating ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1222s
main: -- index_exists?(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id_only_incidents", :where=>"issue_type = 1", :algorithm=>:concurrently})
main:    -> 0.0387s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- add_index(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id_only_incidents", :where=>"issue_type = 1", :algorithm=>:concurrently})
main:    -> 0.0089s
main: -- execute("RESET statement_timeout")
main:    -> 0.0014s
main: == 20230414150202 AddIssuesIncidentTypeTempIndex: migrated (0.2032s) ==========

main: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: migrating =========
main: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: migrated (0.1299s)

main: == [advisory_lock_connection] object_id: 274040, pg_backend_pid: 32216
ci: == [advisory_lock_connection] object_id: 274620, pg_backend_pid: 32218
ci: == 20230414150202 AddIssuesIncidentTypeTempIndex: migrating ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0013s
ci: -- index_exists?(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id_only_incidents", :where=>"issue_type = 1", :algorithm=>:concurrently})
ci:    -> 0.0451s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0008s
ci: -- add_index(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id_only_incidents", :where=>"issue_type = 1", :algorithm=>:concurrently})
ci:    -> 0.0112s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0009s
ci: == 20230414150202 AddIssuesIncidentTypeTempIndex: migrated (0.0953s) ==========

ci: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: migrating =========
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_shared, :gitlab_internal].
ci: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: migrated (0.0179s)

ci: == [advisory_lock_connection] object_id: 274620, pg_backend_pid: 32218

DOWN

bin/rails db:rollback:main db:rollback:ci STEP=2                                                                                                                                                             403158-fix-incident-records ✭
main: == [advisory_lock_connection] object_id: 273880, pg_backend_pid: 31433
main: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: reverting =========
main: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: reverted (0.0082s)

main: == 20230414150202 AddIssuesIncidentTypeTempIndex: reverting ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0128s
main: -- indexes(:issues)
main:    -> 0.0272s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_index_issues_on_issue_type_and_id_only_incidents"})
main:    -> 0.0051s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230414150202 AddIssuesIncidentTypeTempIndex: reverted (0.0652s) ==========

main: == [advisory_lock_connection] object_id: 273880, pg_backend_pid: 31433
ci: == [advisory_lock_connection] object_id: 309440, pg_backend_pid: 31708
ci: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: reverting =========
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_shared, :gitlab_internal].
ci: == 20230414200202 RescheduleIncidentWorkItemTypeIdBackfill: reverted (0.0302s)

ci: == 20230414150202 AddIssuesIncidentTypeTempIndex: reverting ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0017s
ci: -- indexes(:issues)
ci:    -> 0.0568s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_index_issues_on_issue_type_and_id_only_incidents"})
ci:    -> 0.0073s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0008s
ci: == 20230414150202 AddIssuesIncidentTypeTempIndex: reverted (0.1134s) ==========

ci: == [advisory_lock_connection] object_id: 309440, pg_backend_pid: 31708

Query Plans

https://console.postgres.ai/shared/7650e7c7-5827-43c8-a687-4ced69ba2f73 
SELECT 
  "issues"."id" 
FROM 
  "issues" 
WHERE 
  "issues"."id" BETWEEN 2949893 
  AND 71681606 
  AND "issues"."issue_type" = 1 
ORDER BY 
  "issues"."id" ASC 
LIMIT 
  1
https://console.postgres.ai/shared/29e0c8de-6ae9-4c63-b63f-e44b652b7dab 
SELECT 
  "issues"."id" 
FROM 
  "issues" 
WHERE 
  "issues"."id" BETWEEN 2949893 
  AND 71681606 
  AND "issues"."issue_type" = 1 
  AND "issues"."id" >= 2949893 
ORDER BY 
  "issues"."id" ASC 
LIMIT 
  1 OFFSET 50
https://console.postgres.ai/shared/a9c63e13-5ee6-4300-b033-3b5ecedf601c 
UPDATE 
  "issues" 
SET 
  "work_item_type_id" = 2, 
  "lock_version" = COALESCE("lock_version", 0) + 1 
WHERE 
  "issues"."issue_type" = 1 
  AND "issues"."id" BETWEEN 71681606 
  AND 12015589

MR acceptance checklist

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

Related to #403158 (closed)

Edited by Mario Celi

Merge request reports