Synch add tmp indexes for `vulnerability_occurrences` backfill

What does this MR do and why?

As part of the epic to drop the vulnerability_occurrence_pipelines table, we need to backfill some new columns to migrate dependent feature to

The backfill MR is scoped to nil columns

The asynchronous index creation was added in a previous MR

This change adds the synchronous index migration to complete the index creation process

Follow-up Work

We need to run the backfills and then remove the temporary indexes

That work is tracked in the following tasks:

• Backfill `initial_pipeline_id` column (#451180 - closed) • Michael Becker • 17.1
• Backfill `latest_pipeline_id` column (#451181 - closed) • Michael Becker • 17.1
• Remove temporary indexes added for the backfill (#454243 - closed) • Michael Becker • 17.3

Migration Output

bundle exec rails db:migrate

main: == [advisory_lock_connection] object_id: 122480, pg_backend_pid: 473135
main: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0117s
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.0110s
main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_occurrences, column_name: [:id, :initial_pipeline_id]
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0010s
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.0107s
main: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_occurrences, column_name: [:id, :latest_pipeline_id]
main: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: migrated (0.0527s) 

main: == [advisory_lock_connection] object_id: 122480, pg_backend_pid: 473135
ci: == [advisory_lock_connection] object_id: 122680, pg_backend_pid: 473137
ci: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: migrating 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
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.0095s
ci: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_occurrences, column_name: [:id, :initial_pipeline_id]
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
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.0123s
ci: -- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_occurrences, column_name: [:id, :latest_pipeline_id]
ci: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: migrated (0.0457s) 

ci: == [advisory_lock_connection] object_id: 122680, pg_backend_pid: 473137

Rollbacks

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

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

main: == [advisory_lock_connection] object_id: 122120, pg_backend_pid: 476106
main: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0028s
main: -- indexes(:vulnerability_occurrences)
main:    -> 0.0051s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id"})
main:    -> 0.0118s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0011s
main: -- indexes(:vulnerability_occurrences)
main:    -> 0.0059s
main: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id"})
main:    -> 0.0009s
main: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: reverted (0.0428s) 

main: == [advisory_lock_connection] object_id: 122120, pg_backend_pid: 476106

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

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

ci: == [advisory_lock_connection] object_id: 122120, pg_backend_pid: 476519
ci: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: reverting 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0031s
ci: -- indexes(:vulnerability_occurrences)
ci:    -> 0.0079s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerability_occurrences_id_and_initial_pipline_id"})
ci:    -> 0.0119s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:vulnerability_occurrences)
ci:    -> 0.0056s
ci: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id"})
ci:    -> 0.0009s
ci: == 20240403104306 AddTmpBackfillIndexForPipelineIdsToVulnerabilityOccurrences: reverted (0.0511s) 

ci: == [advisory_lock_connection] object_id: 122120, pg_backend_pid: 476519

Async migration complete verification

✅ Staging (postgres.ai)

postgress.ai screenshot

The indexes existing via postrges.ai

✅ Production (rails console)

production verification

indexes = %w[ tmp_index_vulnerability_occurrences_id_and_initial_pipline_id tmp_index_vulnerability_occurrences_id_and_latest_pipeline_id]
table = :vulnerability_occurrences

indexes.map { |idx| ActiveRecord::Base.connection.index_name_exists?(table, idx) }

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 #422383 (closed)
Resolves #454239 (closed)
Changelog: added