Skip to content

Prepare for uuid type migration

What does this MR do and why?

This MR prepares vulnerability_occurrences table for type migration of UUID column from varchar(36) to uuid. To do this we need to:

  1. Add uuid_for_type_change column.
  2. Create triggers for uuid_for_type_change column so new values are inserted to that column for new entries
  3. Add a new background migration to copy values for old rows

This MR doesn't use the change_column_type_concurrently because it doesn't support type casting and an in-depth discussion and refactoring would be required to use it as suggested in #335708 (comment 710638565)

This MR does not schedule the background migration since there is another background migration effort for this table ongoing and I believe it would be safer not to have two big background migrations running at the same time

Database review

db:migrate and db:rollback

➜ bundle exec rails db:migrate
== 20211109162349 AddUuidForTypeChangeToVulnerabilityOccurrences: migrating ===
-- add_column(:vulnerability_occurrences, :uuid_for_type_change, :uuid)
   -> 0.0044s
== 20211109162349 AddUuidForTypeChangeToVulnerabilityOccurrences: migrated (0.0044s)

➜ bundle exec rails db:rollback
== 20211109162349 AddUuidForTypeChangeToVulnerabilityOccurrences: reverting ===
-- remove_column(:vulnerability_occurrences, :uuid_for_type_change)
   -> 0.0024s
== 20211109162349 AddUuidForTypeChangeToVulnerabilityOccurrences: reverted (0.0024s)
➜ bundle exec rails db:migrate
== 20211109163516 AddTriggersForUuidTypeChange: migrating =====================
== 20211109163516 AddTriggersForUuidTypeChange: migrated (0.0087s) ============

➜ bundle exec rails db:rollback
== 20211109163516 AddTriggersForUuidTypeChange: reverting =====================
== 20211109163516 AddTriggersForUuidTypeChange: reverted (0.0021s) ============

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 #335708 (closed)

Edited by Michał Zając

Merge request reports