Synchronously add indexes for new `vulnerability_occurrences` cols
requested to merge 422382-sync-index-creation-for-the-new-vulnerability_occurrences-columns into master
What does this MR do and why?
We want to drop the vulnerability_occurrence_pipelines table. In
order to do this, we still need the first and latest pipeline ID
stored somewhere to support the existing feature-set
The columns and asynchronous index creation were added in a previous MR
This change adds the synchronous index migration to complete the index creation process
Follow-up Work
We will need to add loose foreign keys and model association. Both of these are blocked on this index creation
That work is tracked in the following tasks:
- Add the model associations | M (#443283 - closed) • Michael Becker • 16.10
- Add the loose FK config | M (#443284 - closed) • Michael Becker • 16.10
Migration Output
bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 119460, pg_backend_pid: 118859
main: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0049s
main: -- index_exists?(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0063s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0133s
main: -- execute("RESET statement_timeout")
main: -> 0.0005s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0007s
main: -- index_exists?(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0069s
main: -- add_index(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0011s
main: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0533s)
main: == [advisory_lock_connection] object_id: 119460, pg_backend_pid: 118859
ci: == [advisory_lock_connection] object_id: 119700, pg_backend_pid: 118861
ci: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0008s
ci: -- index_exists?(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0052s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- add_index(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0140s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0008s
ci: -- index_exists?(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0057s
ci: -- add_index(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0014s
ci: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0497s)
ci: == [advisory_lock_connection] object_id: 119700, pg_backend_pid: 118861Rollbacks
bundle exec rails db:migrate:down:main VERSION=20240226094324
bundle exec rails db:migrate:down:main VERSION=20240226094324main: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 118391
main: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverting
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0031s
main: -- indexes(:vulnerability_occurrences)
main: -> 0.0058s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_initial_pipeline_id"})
main: -> 0.0011s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0005s
main: -- indexes(:vulnerability_occurrences)
main: -> 0.0049s
main: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_latest_pipeline_id"})
main: -> 0.0008s
main: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0411s)
main: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 118391
bundle exec rails db:migrate:down:ci VERSION=20240226094324
bundle exec rails db:migrate:down:ci VERSION=20240226094324ci: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 117977
ci: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverting
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0054s
ci: -- indexes(:vulnerability_occurrences)
ci: -> 0.0094s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0005s
ci: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_initial_pipeline_id"})
ci: -> 0.0011s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0002s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0008s
ci: -- indexes(:vulnerability_occurrences)
ci: -> 0.0073s
ci: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_latest_pipeline_id"})
ci: -> 0.0010s
ci: == 20240226094324 AddIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0654s)
ci: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 117977
Async migration complete verification
postgres.ai (staging)
The indexes existing via postrges.ai
via rails console (production)
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.
Changelog: added
Related to #422382 (closed)
Resolves #443282 (closed)
Edited by Michael Becker