Limit project moved e-mails to maintainers/owners
Previously the "Project was moved" email notifications were sent every member in the project. This served a useful purpose when we did not have project redirects, so there was no way to discover that a project had actually moved. However, these e-mails generate a significant amount of noise, especially with projects with large number of team members.
To cut down on these notifications, we restrict the e-mails to project/group maintainers and owners. Later we can introduce custom notification settings to disable this.
Part of #30371 (closed)
SQL output
The main query:
SELECT "members".*
FROM
(SELECT DISTINCT ON (user_id,
invite_email) member_union.id,
COALESCE(project_authorizations.access_level, member_union.access_level) access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override
FROM (
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_id" = 6543
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL)
UNION ALL
(SELECT "members".*
FROM "members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE "members"."type" = 'ProjectMember'
AND "members"."source_id" = 7764
AND "members"."source_type" = 'Project'
AND "members"."requested_at" IS NULL
AND "users"."state" = 'active'
AND "members"."requested_at" IS NULL)) AS member_union
LEFT JOIN users ON users.id = member_union.user_id
LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
AND project_authorizations.project_id = 1
ORDER BY user_id,
invite_email,
CASE
WHEN TYPE = 'ProjectMember' THEN 1
WHEN TYPE = 'GroupMember' THEN 2
ELSE 3
END) AS members
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE (("members"."user_id" IS NULL
AND "members"."invite_token" IS NOT NULL)
OR "users"."state" = 'active')
AND "members"."requested_at" IS NULL
AND "members"."access_level" IN (40,
50) QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=426.30..430.35 rows=1 width=168) (actual time=26.578..29.028 rows=318 loops=1)
Filter: (((members.user_id IS NULL) AND (members.invite_token IS NOT NULL)) OR ((users.state)::text = 'active'::text))
Rows Removed by Filter: 1
-> Subquery Scan on members (cost=425.87..426.89 rows=1 width=168) (actual time=26.567..27.540 rows=319 loops=1)
Filter: ((members.requested_at IS NULL) AND (members.access_level = ANY ('{40,50}'::integer[])))
Rows Removed by Filter: 1243
-> Unique (cost=425.87..426.25 rows=51 width=172) (actual time=26.562..27.228 rows=1562 loops=1)
-> Sort (cost=425.87..425.99 rows=51 width=172) (actual time=26.561..26.751 rows=1618 loops=1)
Sort Key: members_1.user_id, members_1.invite_email, (CASE WHEN ((members_1.type)::text = 'ProjectMember'::text) THEN 1 WHEN ((members_1.type)::text = 'GroupMember'::text) THEN 2 ELSE 3 END)
Sort Method: quicksort Memory: 315kB
-> Nested Loop Left Join (cost=1.56..424.42 rows=51 width=172) (actual time=0.079..25.500 rows=1618 loops=1)
-> Nested Loop Left Join (cost=0.99..373.06 rows=51 width=172) (actual time=0.063..12.980 rows=1618 loops=1)
-> Append (cost=0.56..270.61 rows=51 width=168) (actual time=0.030..4.994 rows=1618 loops=1)
-> Index Scan using index_members_on_source_id_and_source_type on members members_1 (cost=0.56..249.41 rows=47 width=168) (actual time=0.030..2.224 rows=1338 loops=1)
Index Cond: ((source_id = 6543) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (invite_token IS NULL) AND ((type)::text = 'GroupMember'::text))
Rows Removed by Filter: 38
-> Nested Loop (cost=0.99..20.44 rows=4 width=168) (actual time=0.043..2.577 rows=280 loops=1)
-> Index Scan using index_non_requested_project_members_on_source_id_and_type on members members_2 (cost=0.56..6.63 rows=4 width=168) (actual time=0.025..0.526 rows=280 loops=1)
Index Cond: ((source_id = 7764) AND ((source_type)::text = 'Project'::text))
-> Index Scan using users_pkey on users users_2 (cost=0.43..3.45 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=280)
Index Cond: (id = members_2.user_id)
Filter: ((state)::text = 'active'::text)
-> Index Only Scan using users_pkey on users users_1 (cost=0.43..2.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1618)
Index Cond: (id = members_1.user_id)
Heap Fetches: 90
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..0.99 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1618)
Index Cond: ((user_id = users_1.id) AND (project_id = 1))
Heap Fetches: 0
-> Index Scan using users_pkey on users (cost=0.43..3.45 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=319)
Index Cond: (members.user_id = id)
Planning Time: 2.504 ms
Execution Time: 29.145 ms
(33 rows)There are two other queries for SELECT * FROM users and SELECT * FROM notification_settings in !36665 (comment 380994497).
Edited by Stan Hu