Backfill project member namespace id

What does this MR do and why?

Very similar to !78715 (merged) 😸

For ProjectMember rows in the members table, the value of member_namespace_id should be equal to the associated Project's project_namespace_id, via source_id foreign key. This migration simply copies that value.

New records in the members table already have this column set: Set member_namespace_id when creating new proje... (!78491 - merged)

Depends on all project namespaces existing 👉 #337100 (closed)

Database migration info

up

$ be rails db:migrate:up VERSION=20220306054002
== 20220306054002 TempIndexForProjectNamespaceMemberBackfill: migrating =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:members, :id, {:where=>"members.member_namespace_id IS NULL and members.type = 'ProjectMember'", :name=>"tmp_index_for_namespace_id_migration_on_project_members", :algorithm=>:concurrently})
   -> 0.0047s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:members, :id, {:where=>"members.member_namespace_id IS NULL and members.type = 'ProjectMember'", :name=>"tmp_index_for_namespace_id_migration_on_project_members", :algorithm=>:concurrently})
   -> 0.0048s
-- execute("RESET statement_timeout")
   -> 0.0007s
== 20220306054002 TempIndexForProjectNamespaceMemberBackfill: migrated (0.0134s) 

$ be rails db:migrate:up VERSION=20220306054011
== 20220306054011 ScheduleBackfillProjectMemberNamespaceId: migrating =========
== 20220306054011 ScheduleBackfillProjectMemberNamespaceId: migrated (0.0058s) 

down

$ brails db:migrate:down VERSION=20220306054011
== 20220306054011 ScheduleBackfillProjectMemberNamespaceId: reverting =========
== 20220306054011 ScheduleBackfillProjectMemberNamespaceId: reverted (0.0184s) 

$ brails db:migrate:down VERSION=20220306054002
== 20220306054002 TempIndexForProjectNamespaceMemberBackfill: reverting =======
-- transaction_open?()
   -> 0.0000s
-- indexes(:members)
   -> 0.0048s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:members, {:algorithm=>:concurrently, :name=>"tmp_index_for_namespace_id_migration_on_project_members"})
   -> 0.0033s
-- execute("RESET statement_timeout")
   -> 0.0004s
== 20220306054002 TempIndexForProjectNamespaceMemberBackfill: reverted (0.0142s) 

Query log (on my local)

↳ spec/lib/gitlab/backfill_project_member_namespace_id_spec.rb:38:in `block (2 levels) in <top (required)>'
   Load (0.4ms)  SELECT "members"."id" FROM "members" WHERE "members"."id" BETWEEN 1 AND 10 AND "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."member_namespace_id" IS NULL ORDER BY "members"."id" ASC, "members"."type" ASC LIMIT 1
  ↳ app/models/concerns/each_batch.rb:61:in `each_batch'
   Load (0.5ms)  SELECT "members"."id" FROM "members" WHERE "members"."id" BETWEEN 1 AND 10 AND "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."member_namespace_id" IS NULL AND "members"."id" >= 1 ORDER BY "members"."id" ASC, "members"."type" ASC LIMIT 1 OFFSET 100 
  ↳ app/models/concerns/each_batch.rb:80:in `block in each_batch'
  #<Class:0x000055b7f3d98d60> Update All (1.3ms)  UPDATE "members" SET member_namespace_id = (SELECT projects.project_namespace_id FROM projects WHERE projects.id = source_id) WHERE "members"."id" BETWEEN 1 AND 10 AND "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."member_namespace_id" IS NULL AND "members"."id" >= 1
  ↳

Query plans

Batched retrieval

explain SELECT "members"."id" FROM "members" WHERE "members"."id" BETWEEN 16000 AND 16100 AND "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."member_namespace_id" IS NULL AND "members"."id" >= 1 ORDER BY "members"."id" ASC, "members"."type" ASC

(removed the LIMIT 1 and OFFSET 100 for clarity)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9852/commands/34909

Update

explain UPDATE "members" SET member_namespace_id = (SELECT projects.project_namespace_id FROM projects WHERE projects.id = source_id) WHERE "members"."id" BETWEEN 16000 AND 16100 AND "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."member_namespace_id" IS NULL AND "members"."id" >= 1

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