Add `initial` & `latest` pipeline IDs to `vulnerability_occurrences`
requested to merge 422382-add-columns-first_pipeline_id-and-latest_pipeline_id-to-vulnerability_occurrences 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
These columns should both be nullable, as continuous scanning or alternative scan sources will not have a pipeline_id to associate.
Follow-up Work
We will need to add loose foreign keys and model association. Both of these are blocked on index creation, which has to be done via the async process
That work is tracked in the following tasks:
- Add the indexes SYNC | M (#443282 - closed) • Michael Becker • 16.10
- Add the model associations | M (#443283 - closed) • Michael Becker • 16.10
- Add the loose FK config | M (#443284 - closed) • Michael Becker • 16.10
Changelog: added
Migration Output
bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 119600, pg_backend_pid: 50917
main: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: migrating
main: -- add_column(:vulnerability_occurrences, :initial_pipeline_id, :bigint, {:null=>true})
main: -> 0.0188s
main: -- add_column(:vulnerability_occurrences, :latest_pipeline_id, :bigint, {:null=>true})
main: -> 0.0004s
main: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0219s)
main: == [advisory_lock_connection] object_id: 119600, pg_backend_pid: 50917
ci: == [advisory_lock_connection] object_id: 119860, pg_backend_pid: 50919
ci: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: migrating
ci: -- add_column(:vulnerability_occurrences, :initial_pipeline_id, :bigint, {:null=>true})
ci: -> 0.0021s
ci: -- add_column(:vulnerability_occurrences, :latest_pipeline_id, :bigint, {:null=>true})
ci: -> 0.0005s
ci: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0091s)
ci: == [advisory_lock_connection] object_id: 119860, pg_backend_pid: 50919
main: == [advisory_lock_connection] object_id: 120020, pg_backend_pid: 50922
main: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrating
main: -- index_exists?(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0053s
main: -- add_index_options(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0001s
main: -- index_exists?(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0033s
main: -- add_index_options(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
main: -> 0.0000s
main: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0266s)
main: == [advisory_lock_connection] object_id: 120020, pg_backend_pid: 50922
ci: == [advisory_lock_connection] object_id: 120300, pg_backend_pid: 50924
ci: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrating
ci: -- index_exists?(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0042s
ci: -- add_index_options(:vulnerability_occurrences, :initial_pipeline_id, {:name=>"index_vulnerability_occurrences_on_initial_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0001s
ci: -- index_exists?(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0026s
ci: -- add_index_options(:vulnerability_occurrences, :latest_pipeline_id, {:name=>"index_vulnerability_occurrences_on_latest_pipeline_id", :algorithm=>:concurrently})
ci: -> 0.0000s
ci: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: migrated (0.0205s)
ci: == [advisory_lock_connection] object_id: 120300, pg_backend_pid: 50924Adding columns
bundle exec rails db:migrate:down:main VERSION=20240214035028
bundle exec rails db:migrate:down:main VERSION=20240214035028main: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 211764
main: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: reverting
main: -- remove_column(:vulnerability_occurrences, :latest_pipeline_id, :bigint, {:null=>true})
main: -> 0.0010s
main: -- remove_column(:vulnerability_occurrences, :initial_pipeline_id, :bigint, {:null=>true})
main: -> 0.0004s
main: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0046s)
main: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 211764
bundle exec rails db:migrate:down:ci VERSION=20240214035028
bundle exec rails db:migrate:down:ci VERSION=20240214035028ci: == [advisory_lock_connection] object_id: 119100, pg_backend_pid: 211361
ci: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: reverting
ci: -- remove_column(:vulnerability_occurrences, :latest_pipeline_id, :bigint, {:null=>true})
ci: -> 0.0010s
ci: -- remove_column(:vulnerability_occurrences, :initial_pipeline_id, :bigint, {:null=>true})
ci: -> 0.0005s
ci: == 20240214035028 AddFirstAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0096s)
ci: == [advisory_lock_connection] object_id: 119100, pg_backend_pid: 211361
Preparing indexes
bundle exec rails db:migrate:down:main VERSION=20240222091910
bundle exec rails db:migrate:down:main VERSION=20240226072310main: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 49638
main: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverting
main: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0156s)
main: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 49638bundle exec rails db:migrate:down:ci VERSION=20240222091910
bundle exec rails db:migrate:down:ci VERSION=20240226072310ci: == [advisory_lock_connection] object_id: 119140, pg_backend_pid: 49141
ci: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverting
ci: == 20240226072310 PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences: reverted (0.0215s)
ci: == [advisory_lock_connection] object_id: 119140, pg_backend_pid: 49141
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 #422382 (closed)
Resolves Add the indexes ASYNC | M (#443281 - closed) • Michael Becker • 16.10
Resolves Add the columns | M (#443280 - closed) • Michael Becker • 16.10
Edited by Michael Becker