Skip to content

Change to truncate table before adding finding_link_url_idx

What does this MR do and why?

The migration introduced in !67993 (merged) (db/migrate/20210811193033_add_unique_index_to_vulnerability_finding_links.rb), which adds the unique index has a smaller version number than the one that cleans the data (db/post_migrate/20211210173137_remove_vulnerability_finding_links_again.rb), which was introduced in !76580 (merged). This was due to the need to clean out the table after the index migration was created in the branch.

20210811193033 < 20211210173137

So even though !76580 (merged) was merged first, if both migrations have not run, they run in the reverse order.

That will be an issue with any instance applying migrations introduced in %14.6 and %14.7 at the same time.

This MR no-ops the table cleanout migrations and sets them to truncate before applying the index.

Screenshots or screen recordings

Migration:

== 20220201193033 AddUniqueIndexToVulnerabilityFindingLinksWithTruncate: migrating 
-- execute("TRUNCATE TABLE vulnerability_finding_links")
   -> 0.0088s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :name, :url], {:unique=>true, :name=>"finding_link_name_url_idx", :algorithm=>:concurrently})
   -> 0.0038s
-- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_finding_links, column_name: [:vulnerability_occurrence_id, :name, :url]
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:unique=>true, :where=>"name is null", :name=>"finding_link_url_idx", :algorithm=>:concurrently})
   -> 0.0018s
-- Index not created because it already exists (this may be due to an aborted migration or similar): table_name: vulnerability_finding_links, column_name: [:vulnerability_occurrence_id, :url]
== 20220201193033 AddUniqueIndexToVulnerabilityFindingLinksWithTruncate: migrated (0.0165s) 

Rollback:

== 20220201193033 AddUniqueIndexToVulnerabilityFindingLinksWithTruncate: reverting 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :name, :url], {:name=>"finding_link_name_url_idx", :algorithm=>:concurrently})
   -> 0.0044s
-- execute("SET statement_timeout TO 0")
   -> 0.0008s
-- remove_index(:vulnerability_finding_links, {:name=>"finding_link_name_url_idx", :algorithm=>:concurrently, :column=>[:vulnerability_occurrence_id, :name, :url]})
   -> 0.0125s
-- execute("RESET statement_timeout")
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:name=>"finding_link_url_idx", :algorithm=>:concurrently})
   -> 0.0025s
-- remove_index(:vulnerability_finding_links, {:name=>"finding_link_url_idx", :algorithm=>:concurrently, :column=>[:vulnerability_occurrence_id, :url]})
   -> 0.0044s
== 20220201193033 AddUniqueIndexToVulnerabilityFindingLinksWithTruncate: reverted (0.0327s) 

Cannot run TRUNCATE in postgres.ai, so no plan is available.

How to set up and validate locally

  1. Run the migration
    • Should run successfully
  2. Log into rails console
> Vulnerabilities::FindingLink.count # should be 0

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #351207 (closed)

Edited by Jonathan Schafer

Merge request reports