Create user_group_member_roles when a group is shared to another group

Eugie Limpinrequested to merge
el-cache-user-group-member-role-on-group-share into master

What does this MR do and why?

When a group is invited to another group (a GroupGroupLink is created) run a background job to create user_group_member_roles records for each member of link.shared_with_group.

The same background job is also run when a GroupGroupLink is updated to ensure the user_group_member_roles records for each member of link.shared_with_group are updated.

References

Implements Create user_group_member_roles when group_group... (#517650 - closed).

Database changes

Batching query 1

Raw SQL 
SELECT
    "members"."id"
FROM
    "members"
WHERE
    "members"."source_id" = 91663516
    AND "members"."source_type" = 'Namespace'
    AND "members"."type" = 'GroupMember'
    AND "members"."invite_token" IS NULL
ORDER BY
    "members"."id" ASC
LIMIT 1;

EXPLAIN: https://console.postgres.ai/shared/b377929d-0e69-47b9-9c4b-9b9dab6a378c

Batching query 2

Raw SQL 
SELECT
    "members"."id"
FROM
    "members"
WHERE
    "members"."source_id" = 91663516
    AND "members"."source_type" = 'Namespace'
    AND "members"."type" = 'GroupMember'
    AND "members"."invite_token" IS NULL
    AND "members"."id" >= 108980661
ORDER BY
    "members"."id" ASC
LIMIT 1 OFFSET 1000;

EXPLAIN: https://console.postgres.ai/shared/89f8f64b-f167-4712-b00a-dc9b753fa3eb

Per-batch query

Raw SQL 
WITH "batched_relation" AS MATERIALIZED (
    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",
        "members"."request_accepted_at"
    FROM
        "members"
    WHERE
        "members"."source_id" = 91663516
        AND "members"."source_type" = 'Namespace'
        AND "members"."type" = 'GroupMember'
        AND "members"."invite_token" IS NULL
        AND "members"."id" >= 108980661
    LIMIT 1000
)
SELECT
    "members"."user_id",
    "user_group_member_roles"."id",
    "group_group_links"."shared_group_id" AS group_id,
    CASE WHEN "members"."access_level" > "group_group_links"."group_access" THEN
        "group_group_links"."member_role_id"
    WHEN "members"."access_level" < "group_group_links"."group_access" THEN
        "members"."member_role_id"
    WHEN "group_group_links"."member_role_id" IS NULL THEN
        NULL
    ELSE
        "members"."member_role_id"
    END AS member_role_id,
    "group_group_links"."shared_with_group_id"
FROM
    "batched_relation" AS "members"
    INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = 91385606
        AND "group_group_links"."shared_with_group_id" = "members"."source_id"
        AND "members"."user_id" IS NOT NULL
        AND "members"."requested_at" IS NULL
        AND "members"."state" = 0
        AND "members"."access_level" > 5
    LEFT OUTER JOIN "user_group_member_roles" ON "user_group_member_roles"."user_id" = "members"."user_id"
    AND "user_group_member_roles"."group_id" = "group_group_links"."shared_group_id"
    AND "user_group_member_roles"."shared_with_group_id" = "group_group_links"."shared_with_group_id";

EXPLAIN: https://console.postgres.ai/shared/51a6f992-d853-4f91-a344-4ed9e8c2e4b4

Query 2

Raw SQL 
INSERT INTO "user_group_member_roles" ("user_id", "group_id", "member_role_id", "shared_with_group_id", "created_at", "updated_at")
    VALUES (10042341, 11901785, 1003286, 68812605, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT ("user_id", "group_id", "shared_with_group_id")
WHERE (shared_with_group_id IS NOT NULL)
    DO UPDATE SET
        updated_at = (
            CASE WHEN ("user_group_member_roles"."member_role_id" IS NOT DISTINCT FROM excluded."member_role_id") THEN
                "user_group_member_roles".updated_at
            ELSE
                CURRENT_TIMESTAMP
            END),
        "member_role_id" = excluded."member_role_id"
    RETURNING
        "id"
EXPLAIN

Link: https://console.postgres.ai/shared/e1d93cc2-942a-4862-9bb5-3366702627a9

 ModifyTable on public.user_group_member_roles  (cost=0.00..0.02 rows=1 width=56) (actual time=2.317..2.319 rows=1 loops=1)
   Buffers: shared hit=29 read=5 dirtied=14 written=7
   WAL: records=15 fpi=0 bytes=1198
   I/O Timings: read=1.040 write=0.211
   ->  Result  (cost=0.00..0.02 rows=1 width=56) (actual time=1.158..1.158 rows=1 loops=1)
         Buffers: shared hit=10 read=5 dirtied=1
         WAL: records=1 fpi=0 bytes=99
         I/O Timings: read=1.040 write=0.000
Trigger RI_ConstraintTrigger_c_2187414198 for constraint fk_aa0ed88ba1: time=59.478 calls=1
Trigger RI_ConstraintTrigger_c_2187416521 for constraint fk_d222d57eec: time=14.052 calls=1
Trigger RI_ConstraintTrigger_c_2187418776 for constraint fk_257d7c48b8: time=8.039 calls=1
Trigger RI_ConstraintTrigger_c_2187420337 for constraint fk_f3b8fc5e4e: time=4.167 calls=1
Settings: seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5'

Data deletion

As per https://docs.gitlab.com/development/database_review/#preparation-when-adding-data-migrations:

If the migration itself is not reversible, details of how data changes could be reverted in the event of an incident. For example, in the case of a migration that deletes records (an operation that most of the times is not automatically reversible), how could the deleted records be recovered

There is no migration that deletes data. Instead, user_group_member_roles records can be deleted when a group_group_link is updated (e.g. when member_role_id is removed). Deletion can be reverted by recomputing which member roles are assigned to users when a group is shared to another group and then re-inserting user_group_member_roles records with the correct data. This computation is currently handled by UserMemberRolesIngroupsPreloader.

If the migration deletes data, apply the label data-deletion.

There is no migration that deletes data. Instead, user_group_member_roles records can be deleted when a group_group_link is updated (e.g. when member_role_id is removed).

Concise descriptions of possible user experience impact of an error

User's may not be able to do actions that require permissions granted through custom roles.

Relevant data from the query plans that indicate the query works as expected; such as the approximate number of records that are modified or deleted.

How to set up and validate locally

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugie Limpin

Merge request reports