Skip to content

Create (namespace,iid) unique index on issues table

Mario Celi requested to merge 432908-create-issues-namespace-iid-unique-index into master

What does this MR do and why?

Group level work item creation currently existed behind a feature flag. We are completely disabling this in production environments so we are sure no one enables the flag as it will affect the internal id migration from epics usage to issues usage.

At the same time, we are deleting all group level issue records (at the time of writting only 20 in .com and all internal tests on the groups where the FF was enabled). After they are all deleted, we are adding a unique index to make sure we don't get duplicate IIDs, but this is very unlikely since we already have an implicit locking mechanism that should prevent this

More info in Scope epics internal_ids generation to `issues`... (!139367 - merged)

DB review

DB plans

https://console.postgres.ai/shared/a8c03a19-ce71-4b8f-a3d6-32fa2c7008a5 
SELECT 
  "todos"."id" 
FROM 
  "todos" 
WHERE 
  "todos"."target_type" = 'Issue' 
  AND "todos"."target_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
ORDER BY 
  "todos"."id" ASC 
LIMIT 
  1
https://console.postgres.ai/shared/5b0cc64d-8936-4d5a-86a6-122eb7e9da7b 
DELETE FROM 
  "todos" 
WHERE 
  "todos"."target_type" = 'Issue' 
  AND "todos"."target_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
  AND "todos"."id" >= 356909065
https://console.postgres.ai/shared/c954c2b5-1065-4966-bc9f-031b002355d0 
SELECT 
  "label_links"."id" 
FROM 
  "label_links" 
WHERE 
  "label_links"."target_type" = 'Issue' 
  AND "label_links"."target_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
ORDER BY 
  "label_links"."id" ASC 
LIMIT 
  1
https://console.postgres.ai/shared/e4d432d2-870d-447d-8040-d7bdf2923bfb 
DELETE FROM 
  "label_links" 
WHERE 
  "label_links"."target_type" = 'Issue' 
  AND "label_links"."target_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
  AND "label_links"."id" >= 279474465
https://console.postgres.ai/shared/96370df3-38ab-451f-9f84-c49fed9c0fff 
SELECT 
  "notes"."id" 
FROM 
  "notes" 
WHERE 
  "notes"."noteable_type" = 'Issue' 
  AND "notes"."noteable_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
ORDER BY 
  "notes"."id" ASC 
LIMIT 
  1
https://console.postgres.ai/shared/b214b136-a0df-47b7-a577-5d0df995c301 
DELETE FROM 
  "notes" 
WHERE 
  "notes"."noteable_type" = 'Issue' 
  AND "notes"."noteable_id" IN (
    SELECT 
      "issues"."id" 
    FROM 
      "issues" 
    WHERE 
      "issues"."project_id" IS NULL 
      AND "issues"."id" >= 131466254
  ) 
  AND "notes"."id" >= 1611306552

Migration output

UP 
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 182760, pg_backend_pid: 91314
main: == 20231207145335 CleanupGroupLevelWorkItems: migrating =======================
main: == 20231207145335 CleanupGroupLevelWorkItems: migrated (0.0249s) ==============

main: == [advisory_lock_connection] object_id: 182760, pg_backend_pid: 91314
ci: == [advisory_lock_connection] object_id: 183080, pg_backend_pid: 91316
ci: == 20231207145335 CleanupGroupLevelWorkItems: migrating =======================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20231207145335 CleanupGroupLevelWorkItems: migrated (0.0063s) ==============

ci: == [advisory_lock_connection] object_id: 183080, pg_backend_pid: 91316

main: == [advisory_lock_connection] object_id: 182720, pg_backend_pid: 34205
main: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: migrating ===========
main: -- index_exists?(:issues, [:namespace_id, :iid], {:unique=>true, :name=>"index_issues_on_namespace_id_iid_unique", :algorithm=>:concurrently})
main:    -> 0.0107s
main: -- add_index_options(:issues, [:namespace_id, :iid], {:unique=>true, :name=>"index_issues_on_namespace_id_iid_unique", :algorithm=>:concurrently})
main:    -> 0.0001s
main: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: migrated (0.1149s) ==

main: == [advisory_lock_connection] object_id: 182720, pg_backend_pid: 34205
ci: == [advisory_lock_connection] object_id: 183040, pg_backend_pid: 34207
ci: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: migrating ===========
ci: -- index_exists?(:issues, [:namespace_id, :iid], {:unique=>true, :name=>"index_issues_on_namespace_id_iid_unique", :algorithm=>:concurrently})
ci:    -> 0.0104s
ci: -- add_index_options(:issues, [:namespace_id, :iid], {:unique=>true, :name=>"index_issues_on_namespace_id_iid_unique", :algorithm=>:concurrently})
ci:    -> 0.0002s
ci: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: migrated (0.0248s) ==

ci: == [advisory_lock_connection] object_id: 183040, pg_backend_pid: 34207
DOWN 
bin/rails db:migrate:down:main db:migrate:down:ci VERSION=20231207155340
main: == [advisory_lock_connection] object_id: 182220, pg_backend_pid: 23141
main: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: reverting ===========
main: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: reverted (0.0606s) ==

main: == [advisory_lock_connection] object_id: 182220, pg_backend_pid: 23141
ci: == [advisory_lock_connection] object_id: 187980, pg_backend_pid: 23399
ci: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: reverting ===========
ci: == 20231207155340 PrepareIidNamespaceUniqueIndexInIssues: reverted (0.0094s) ==

ci: == [advisory_lock_connection] object_id: 187980, pg_backend_pid: 23399

bin/rails db:migrate:down:main db:migrate:down:ci VERSION=20231207145335
main: == [advisory_lock_connection] object_id: 182300, pg_backend_pid: 90435
main: == 20231207145335 CleanupGroupLevelWorkItems: reverting =======================
main: == 20231207145335 CleanupGroupLevelWorkItems: reverted (0.0030s) ==============

main: == [advisory_lock_connection] object_id: 182300, pg_backend_pid: 90435
ci: == [advisory_lock_connection] object_id: 183900, pg_backend_pid: 90686
ci: == 20231207145335 CleanupGroupLevelWorkItems: reverting =======================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20231207145335 CleanupGroupLevelWorkItems: reverted (0.0080s) ==============

ci: == [advisory_lock_connection] object_id: 183900, pg_backend_pid: 90686

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

Edited by Mario Celi

Merge request reports