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-op
s 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
- Run the migration
- Should run successfully
- 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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #351207 (closed)