Correctly delete todos when group is made private

What does this MR do and why?

When a group is changed to private, we must delete users that are members of subgroups. These do not have access to the parent group.

Also, we need to keep todos from group shares because these users still have access to the group.

Query changes

Note: I used SELECT todos.id instead of DELETE FROM todos when getting these plans. They should be identical to the deletion plans though.

Old query: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20795/commands/68278

Time: 465.505 ms  
  - planning: 5.973 ms  
  - execution: 459.532 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 197778 (~1.50 GiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

SQL

SELECT "todos"."id"
FROM "todos"
WHERE "todos"."group_id" = 6543
  AND "todos"."user_id" NOT IN (
    SELECT "users"."id" FROM (
      (SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND "members"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{6543}') OR 1=0))))
      UNION
      (SELECT "users".* FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{6543}'))))
    ) users
  );

New query: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20795/commands/68276

Time: 250.725 ms  
  - planning: 5.805 ms  
  - execution: 244.920 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 136946 (~1.00 GiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

SQL

SELECT "todos"."id" FROM "todos" WHERE "todos"."group_id" = 6543 AND "todos"."user_id" NOT IN (SELECT "users"."id" FROM ((SELECT "users"."id" FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{6543}'))))
UNION ALL
(SELECT "members"."user_id" FROM ((SELECT "members"."id", "members"."access_level", "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND (members.access_level > 5) AND "members"."source_id" = 6543 AND "users"."state" = 'active' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 6543) SELECT "members"."id", LEAST("group_group_links"."group_access", "members"."access_level") AS access_level, "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at" FROM "members", "group_group_links_cte" AS "group_group_links" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "group_group_links"."shared_with_group_id" AND "members"."source_type" = 'Namespace' AND "members"."state" = 0 AND (members.access_level > 5))) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace')) users)

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