Skip to content

Add unique index for work item type names with no namespace

Mario Celi requested to merge 353552-fix-work-item-type-null-namespaces into master

What does this MR do and why?

This change is necessary to resolve #353552 (comment 901679281)

We no longer what to depend on app seeds to create this records, so we need a unique index that accounts for null namespace_id. THis way we can use SQL to upsert this new records using an INSERT ... ON CONFLICT DO NOTHING.

The existing index for unique names on the work_item_types table won't account for records that have a null namespace_id.

From postgres docs Null values are not considered equal, so adding a unique index for work_item_types on [:namespace_id, name] allows multiple records with the same name as long as namespace_id is null. We already have that index for when we have work item types that belong to a namespace

This change will create a unique index only for those work item types that have a null namespace_id currently we only have 5 records and we don't expect this list to be large at any moment, so the index will always be small.

Related to #353552 (closed) and #347072 (closed)

Migration output

UP

== 20220408001450 AddWorkItemTypeNameUniqueIndexNullNamespaces: migrating =====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:work_item_types, "TRIM(BOTH FROM LOWER(name)), (namespace_id IS NULL)", {:unique=>true, :name=>:idx_work_item_types_on_namespace_id_and_name_null_namespace, :where=>"namespace_id IS NULL", :algorithm=>:concurrently})
   -> 0.0026s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:work_item_types, "TRIM(BOTH FROM LOWER(name)), (namespace_id IS NULL)", {:unique=>true, :name=>:idx_work_item_types_on_namespace_id_and_name_null_namespace, :where=>"namespace_id IS NULL", :algorithm=>:concurrently})
   -> 0.0094s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20220408001450 AddWorkItemTypeNameUniqueIndexNullNamespaces: migrated (0.0211s)

DOWN

== 20220408001450 AddWorkItemTypeNameUniqueIndexNullNamespaces: reverting =====
-- transaction_open?()
   -> 0.0000s
-- indexes(:work_item_types)
   -> 0.0022s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:work_item_types, {:algorithm=>:concurrently, :name=>:idx_work_item_types_on_namespace_id_and_name_null_namespace})
   -> 0.0070s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20220408001450 AddWorkItemTypeNameUniqueIndexNullNamespaces: reverted (0.0171s)

How to set up and validate locally

Before running the migration try to insert a duplicate work item (you probably already have the issue type in your dev DB):

INSERT INTO "work_item_types" ("base_type","name","icon_name","created_at","updated_at") VALUES (0, 'Issue', 'any', '2022-04-07 23:30:38.082418', '2022-04-07 23:30:38.082419');

This will raise no error and we do have a unique index defined as:

CREATE UNIQUE INDEX work_item_types_namespace_id_and_name_unique ON work_item_types USING btree (namespace_id, btrim(lower(name)));

Remove the newly created record and run the migration.

Then, try to insert the same record again with:

INSERT INTO "work_item_types" ("base_type","name","icon_name","created_at","updated_at") VALUES (0, 'Issue', 'any', '2022-04-07 23:30:38.082418', '2022-04-07 23:30:38.082419');

This time you should get a record not unique error.

MR acceptance checklist

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

Edited by Mario Celi

Merge request reports