Drop temporary index on `vulnerability_occurrences` table

What does this MR do and why?

This index was added to be a supporting index for a backfill in:

Async tmp index addition for a `vulnerability_o... (!148470 - merged) • Michael Becker • 16.11

This commit adds a migration to drop the index as the backfill is complete.

previous related MRs:

• tmp index added: !148470 (merged)
• backfill: !148470 (merged)
• finalization of the backfill: !158720 (merged)

Changelog: removed

Migration Output

bundle exec rails db:migrate

main: == [advisory_lock_connection] object_id: 130560, pg_backend_pid: 470382
main: == 20240131100818 DropTmpFindingIndexFromVulnerabilities: migrating ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0026s
main: -- indexes(:vulnerabilities)
main:    -> 0.0056s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:vulnerabilities, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerabilities_on_id_finding_id_empty"})
main:    -> 0.0008s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20240131100818 DropTmpFindingIndexFromVulnerabilities: migrated (0.0183s) ==

main: == [advisory_lock_connection] object_id: 130560, pg_backend_pid: 470382
ci: == [advisory_lock_connection] object_id: 130780, pg_backend_pid: 470384
ci: == 20240131100818 DropTmpFindingIndexFromVulnerabilities: migrating ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0005s
ci: -- indexes(:vulnerabilities)
ci:    -> 0.0060s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:vulnerabilities, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerabilities_on_id_finding_id_empty"})
ci:    -> 0.0008s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0001s
ci: == 20240131100818 DropTmpFindingIndexFromVulnerabilities: migrated (0.0182s) ==

ci: == [advisory_lock_connection] object_id: 130780, pg_backend_pid: 470384

bundle exec rails db:migrate:down:main VERSION=20240709081011

main: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 472323
main: == 20240709081011 DropTmpIndexFromVulnerabilityOccurrences: reverting =========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0022s
main: -- index_exists?(:vulnerability_occurrences, [:id, :initial_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id", :where=>"initial_pipeline_id IS NULL", :algorithm=>:concurrently})
main:    -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:vulnerability_occurrences, [:id, :initial_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id", :where=>"initial_pipeline_id IS NULL", :algorithm=>:concurrently})
main:    -> 0.0048s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:vulnerability_occurrences, [:id, :latest_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id", :where=>"latest_pipeline_id IS NULL", :algorithm=>:concurrently})
main:    -> 0.0039s
main: -- add_index(:vulnerability_occurrences, [:id, :latest_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id", :where=>"latest_pipeline_id IS NULL", :algorithm=>:concurrently})
main:    -> 0.0013s
main: == 20240709081011 DropTmpIndexFromVulnerabilityOccurrences: reverted (0.0300s) 

main: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 472323```

bundle exec rails db:migrate:down:ci VERSION=20240709081011

ci: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 471827
ci: == 20240709081011 DropTmpIndexFromVulnerabilityOccurrences: reverting =========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0023s
ci: -- index_exists?(:vulnerability_occurrences, [:id, :initial_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id", :where=>"initial_pipeline_id IS NULL", :algorithm=>:concurrently})
ci:    -> 0.0051s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:vulnerability_occurrences, [:id, :initial_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id", :where=>"initial_pipeline_id IS NULL", :algorithm=>:concurrently})
ci:    -> 0.0136s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- index_exists?(:vulnerability_occurrences, [:id, :latest_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id", :where=>"latest_pipeline_id IS NULL", :algorithm=>:concurrently})
ci:    -> 0.0055s
ci: -- add_index(:vulnerability_occurrences, [:id, :latest_pipeline_id], {:name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id", :where=>"latest_pipeline_id IS NULL", :algorithm=>:concurrently})
ci:    -> 0.0008s
ci: == 20240709081011 DropTmpIndexFromVulnerabilityOccurrences: reverted (0.0486s) 

ci: == [advisory_lock_connection] object_id: 130180, pg_backend_pid: 471827

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.

Resolves #454243 (closed)