Delete state transitions with no state change
Migration to delete state transitions where same state present in both from_state
and to_state
Migration and query analysis:
bundle exec rake db:migrate
main: == 20221013215832 CleanupVulnerabilityStateTransitionsWithSameFromStateToState: migrating
main: == 20221013215832 CleanupVulnerabilityStateTransitionsWithSameFromStateToState: migrated (0.0184s)
ci: == 20221013215832 CleanupVulnerabilityStateTransitionsWithSameFromStateToState: 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: == 20221013215832 CleanupVulnerabilityStateTransitionsWithSameFromStateToState: migrated (0.0003s)
[ gprd ] production> Vulnerabilities::StateTransition.count
=> 1759754
[ gprd ] production> Vulnerabilities::StateTransition.where('from_state = to_state').count
=> 178153
[ gstg ] production> Vulnerabilities::StateTransition.where('from_state = to_state').count
=> 0
DELETE from vulnerability_state_transitions where from_state = to_state;
Time: 2.867 s
- planning: 0.696 ms
- execution: 2.867 s
- I/O read: 907.333 ms
- I/O write: 0.000 ms
https://gitlab.slack.com/archives/CLJMDRD8C/p1665697592125439 https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12629/commands/44571
Related to #376356 (closed)
Edited by Subashis Chakraborty