Skip to content

Add refresh assignment worker

Bishwa Hang Rai requested to merge 415302-add-refresh-seat-worker into master

What does this MR do and why?

Add new worker to refresh the user assignments records for the given root_namespace and the code_suggestions AddOn.

It goes through all the assignments and checks if the associated user is still eligible, or not. When the user is not eligible, it removes the assignment to free up code_suggestions seat.

This will be enqueued in follow up MR when GroupLink or ProjectLink gets destroyed, so that any seats that can be freed after the invited group removal, is freed.

Follow up MR: !131274 (merged)

Screenshots or screen recordings

n/a

How to set up and validate locally

  1. Check out this branch
  2. Create a new root group namespace
  3. Setup some seed records
namespace = Namespace.last
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
add_on_purchase = GitlabSubscriptions::AddOnPurchase.create!(
  add_on: add_on, namespace: namespace, expires_on: 1.month.from_now, quantity: 5, purchase_xid: 'A-S0001'
)

user_1 = User.find 69 # John Doe6
user_2 = User.find 70 # John Doe7

# add user as guest
namespace.add_guest(user_2)

# assign seat to the user
add_on_purchase.assigned_users.create(user: user_1)
add_on_purchase.assigned_users.create(user: user_2)

# enable the feature flag
Feature.enable(:hamilton_seat_management)

# check the current assigned_users count
add_on_purchase.assigned_users.count # 2

# removes only seat assignments to user_1 as user_2 is still eligible with `guest` role
GitlabSubscriptions::AddOnPurchases::RefreshUserAssignmentsWorker.new.perform(namespace.id)
add_on_purchase.assigned_users.count # 1

# remove the existing memberships
namespace.members.where(user: user_2).destroy_all

# removes the seat assignment also for user_2
GitlabSubscriptions::AddOnPurchases::RefreshUserAssignmentsWorker.new.perform(namespace.id)
add_on_purchase.assigned_users.count # 0

SQL

This is similar to the MR: !130622 (merged)

Here we loop through assignments in batches, and then delete the the ineligible assignment in bulk.

each_batch_and_delete 
# find in batches
SELECT "subscription_user_add_on_assignments"."id" FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 64 AND "subscription_user_add_on_assignments"."id" >= 111 ORDER BY "subscription_user_add_on_assignments"."id" ASC LIMIT 1 OFFSET 500

# pluck user_ids
SELECT "subscription_user_add_on_assignments"."user_id" FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 64 AND "subscription_user_add_on_assignments"."id" >= 11

# delete
DELETE FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 64 AND "subscription_user_add_on_assignments"."id" >= 111 AND "subscription_user_add_on_assignments"."user_id" IN (615, 616)

PostgresAI:

  1. each_batch query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22004/commands/71183
  2. pluck user_ids for batch: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22004/commands/71182
  3. Delete assignments in batch: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22004/commands/71184
filter ineligible user_ids 
# group member
SELECT "users"."id" 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 @> ('{1418}'))) AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1418) /* allow_cross_joins_across_databases */) AND "users"."user_type" IN (0, 4, 5) AND "users"."id" IN (615, 616)

# project member
SELECT "users"."id" FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' 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 "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1418}')))) AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1418)) AND "users"."user_type" IN (0, 4, 5) AND ("users"."state" IN ('active')) AND "users"."id" IN (615, 616)

# group group link member
SELECT "users"."id" FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "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 (WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids" FROM "namespaces" INNER JOIN "group_group_links" ON "group_group_links"."shared_with_group_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1418}')))) SELECT "namespaces"."id" FROM "namespaces" INNER JOIN (SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids") FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id WHERE "namespaces"."type" = 'Group') AND "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1418) /* allow_cross_joins_across_databases */) AND "users"."user_type" IN (0, 4, 5) AND "users"."id" IN (615, 616)

# group project link member
SELECT "users"."id" FROM "users" WHERE "users"."id" IN (SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "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 (WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids" FROM "namespaces" INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "project_group_links"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1418}'))))) SELECT "namespaces"."id" FROM "namespaces" INNER JOIN (SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids") FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id WHERE "namespaces"."type" = 'Group') AND "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."user_id" NOT IN (SELECT "namespace_bans"."user_id" FROM "namespace_bans" WHERE "namespace_bans"."namespace_id" = 1418)

PostgresAI:

  1. group member: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22004/commands/71185
  2. project member: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22004/commands/71186

Database Decision

After the MR review discussion we found out that using .where(usr_id: user_ids) filter didn't give much improvements on the main 4 queries to find [group, project, group_link, project_link] members.

Hence we decided to use existing Namespace::BilledUserFinder to fetch all the eligible user_ids at once, and use Ruby set to filter out ineligible user_ids.

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 #415302

Edited by Bishwa Hang Rai

Merge request reports