Fix cross-join in Group#reached_project_access_token_limit? method

What does this MR do and why?

Related issue: #417455 (closed), extracted from !133543 (merged)

1. Fix cross-join in Group#reached_project_access_token_limit? method

We now fetch members relation instead, and then fetch the users separately if required.

2. Also, introduce each_batch_pluck to prevent accidentally plucking large numbers of rows (e.g. 1 million), or accidentally sending large numbers of ids into queries

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Old

Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23054/commands/74216

Click to expand

SELECT
    1 AS one
FROM
    "personal_access_tokens"
    INNER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id"
    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 ("personal_access_tokens"."revoked" = FALSE
    OR "personal_access_tokens"."revoked" IS NULL)
AND ("personal_access_tokens"."expires_at" > '2023-10-12'
    OR "personal_access_tokens"."expires_at" IS NULL)
AND "namespaces"."id" IN (
    SELECT
        "namespaces"."id"
    FROM
        "namespaces"
    WHERE
        "namespaces"."type" = 'Group'
        AND (traversal_ids @> ('{9970}')))
AND "users"."user_type" = 6
LIMIT 1 OFFSET 0

New

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23054/commands/74224

Click to expand

SELECT DISTINCT
    "members"."id"
FROM
    "members"
WHERE
    "members"."type" = 'ProjectMember'
    AND "members"."source_type" = 'Project'
    AND "members"."source_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 @> ('{9970}'))))
        AND "members"."requested_at" IS NULL
        AND "members"."invite_token" IS NULL
    ORDER BY
        "members"."id" ASC
    LIMIT 1
    /*application:test,correlation_id:f6da26a4f074e9b8e9770011a926f708,db_config_name:main,line:/app/models/concerns/each_batch.rb:79:in ` each_batch '*/

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23054/commands/74225:

Click to expand

SELECT DISTINCT
    "members"."id"
FROM
    "members"
WHERE
    "members"."type" = 'ProjectMember'
    AND "members"."source_type" = 'Project'
    AND "members"."source_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 @> ('{9970}'))))
        AND "members"."requested_at" IS NULL
        AND "members"."invite_token" IS NULL
        AND "members"."id" >= 37416
    ORDER BY
        "members"."id" ASC
    LIMIT 1 OFFSET 1000
    /*application:test,correlation_id:f6da26a4f074e9b8e9770011a926f708,db_config_name:main,line:/app/models/concerns/each_batch.rb:98:in `block in each_batch'*/

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23054/commands/74226:

Click to expand

SELECT DISTINCT
    "members"."user_id"
FROM
    "members"
WHERE
    "members"."type" = 'ProjectMember'
    AND "members"."source_type" = 'Project'
    AND "members"."source_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 @> ('{9970}'))))
        AND "members"."requested_at" IS NULL
        AND "members"."invite_token" IS NULL
        AND "members"."id" >= 37416
        /*application:test,correlation_id:f6da26a4f074e9b8e9770011a926f708,db_config_name:main,line:/app/models/concerns/each_batch.rb:21:in ` block IN each_batch_pluck '*/

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/23057/commands/74231:

Click to expand

SELECT
    COUNT(*)
FROM
    "personal_access_tokens"
    INNER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id"
WHERE ("personal_access_tokens"."revoked" = FALSE
    OR "personal_access_tokens"."revoked" IS NULL)
AND ("personal_access_tokens"."expires_at" > '2023-10-12'
    OR "personal_access_tokens"."expires_at" IS NULL)
AND "users"."user_type" = 6
AND "users"."id" IN (
12512557,  9064759,  5324454,  3946291,  2327535,  4709667,  9669638,  1906296,  8964751,  9605800, 11848785, 13673146,  5057340, 15260061, 10419315, 15497712, 14612723,  2683848,  4544240, 14816301, 12434951,   695467,   546275, 15421261, 10205923,   731181, 14645787, 16889457, 12979608,   228150, 15785735,  3580310,  9047949,  5023081, 11811081,  3983112, 10762712,  4545061, 11222623,   853414, 15572835,  5619708,  1646689,  3462441, 10371032,  4409816, 18309043, 15292019,  3110502, 12515079,    16943,  5057243,  1553458, 12860658,  2950912, 16940447, 10764887, 11004366,  3946912, 10761376,   597578, 18308444, 11065273, 11587068, 12248987,  9712164, 18219077, 15775037, 12426577, 14863785,  3468028, 13169945, 11227928,   127064, 10875546,  2079962,  4554599, 17052107,  5708766,  9488766, 12750949, 10822493, 12376262,  2292092, 10446127, 16946559, 13091325, 13249573,   113370,  7933752,  4787364, 11707854,  9303898, 12385232,  3944199,  2709171,  4554632,    42196, 11488451,  3146670, 15783663,  1588769, 13259879,   171554, 11762197, 14386710,   758045,  9788508, 15700593, 12867582, 14408730,  6594987, 13036290,  9820893, 13176302,  7272738, 13200136,  4442786, 14664107,  4415250,  9057126,  3540517,  9097212, 15725308,  5413970, 13200481,  2167502,  3658061, 10283211,  9430722, 15497718, 12354281, 12381455, 10369898, 12376272, 15728249, 15368982, 14921446,  4586870, 11509790, 10411828,  4584738,  4226366, 12811021,  3440044,  4279595,  9299375,     4849,  3457563,     3585, 14602576,  8864576, 15781836, 14263226, 14388006,  2683007,  4411649,  2761888, 10373497, 13050840, 14375399, 14547753,    30915,  9094263, 10422180,  4090207, 17907027,  3913904,   832471, 14907653,  9093704,  8235192,  5325466,  9655539,  3967332,  3997006,  3509693,   714871,  5054840,  5213246,  6534468,  5022246,  8198564,   581582, 12912498,  7604414,  5667561,  8298946,  4998246,   181229,  9821577, 13639998,  5522960,  6412990, 12188811,  5535431,   411701, 16883909,  6008683,  5344211,  4350670, 11655833,  2613121, 15497708,  5252563, 10421503,  7932574,  6290669, 18191110, 15033743,   626804, 13579330,  4087087, 13685780,  9367898,  5833976,  4159190, 11586953,   428430,  5212325,  4855585,   201566,  1161495,     5302,  7932869,  8979214,   142752,  3397881, 16928417,  9396342, 17009072,  5722081,  7855775, 13435075,  8810176, 15497710,  8000666,  4312863,  5963704,   489758,  6453606, 13375861, 10205888,   570793, 12951158,  4627057,  2819375, 12352716,  5020159, 14763981, 15517787, 14551351,  5460659,   730684,   676946, 15242341,  9283447,  1884221,  3721950,  4337537, 11217480, 11257463, 15096249,  3577640,  9766004, 15497709, 12483448,  7647886,  5291762, 13594745,  8167533,  4267173,   113870,  4018056,  4088036,  5133434, 13934208,   739361,  9231122, 13988757,   210928,  9892463, 12689435,  9588253,  8995688,  9903034,  6333561,  4704683,  8953999,  7742267,  2360513, 16918910, 13376698,  4512666,  9001966,   787914,  8200146, 18309396,  4985194,   487608, 11405098,  5058897,  8975654, 14417396,  2672834,  4320962, 11349374,  1986039,  1758950,  1378158,  5412787,  4711202,  4748809,  7782934,  9887299,  1144264, 10151854,  4092171, 16991862,  9159589,  8018225,  4713264, 10907917, 18122937, 11815951,   194566, 12457737, 14405921, 16992062,  3352258,  8763186,  1197909,  8033532,  8956893,  9717668,  9513836, 12410838,  9770042,  4300337, 12773668,  8496959,   895869,  4903414,    32707,  4006601,  8076474, 11809982, 10539680,      116,  2369952,  4546153,  4354779,  4389314,  3769593, 13017867,  3649728,  4980728, 12605565,  5212274,   447038,  4513129, 12233185,  4981524,  2453071,  4953709,  4059067,  9824869,  2935693,  5757327, 15613537,  2712651, 14132659, 11746338, 12620059, 13279507, 12190616, 14404397,  9613277, 10595500, 13889639, 10203155,  8302598,  3944762,  3719688,  3158191, 14404896, 11648083,  2880930,  5879716, 17052072,  2097582,  1534957,  9270911, 15115310, 10662949,   203201,  5878538,  8702682, 10184967, 10594599,  4867274,  4303096,  3167938, 10033597,  9411358, 14537367,   750946,  2469083,   660180,  3418353, 14389710,     2293,  9338020,  3841241,  8108602, 16992065, 12353486,   177868,  1902115,  9923049,  2967854,  9152310, 14410417,  3482158,  4763342, 11758441,   739252,  4626651,  8582404, 18320025,   154136,   480097,  8340608, 15087397,  7742304,  2815943,  3385446, 16992066,  9299502,  2516894,  2187482, 14673089,  5534214, 15204810,  5247784,  5462477, 15497714,   553096,  8664862,  3993566,  9795428,  9785012, 12848300, 10620598,  8109794, 18319665, 11697411,  6049904, 15229620, 18040649,   221775, 13386937,  3753003,  4019091,  4903108, 13382543, 14481011,  4807002,   402401, 13421380,  9056767,    64248, 12593718,  8236808,  9671738,  5622641, 14863768, 14931796,  9677998,   128633, 13474394, 12643927,  1614863,  4378428,  4748699,  4266820,   508743,  3379682, 13561597,  2166254,  4093067,  4415483, 15182183, 12455340, 12111819, 14705300,  5709669,  6407013, 12551895,  4509877, 12490809,  4810535,  9211819, 14411574, 14537118,  7722111, 11483900, 11811231, 16923210, 15169848, 15053914,  9516393,   926632,   437255,  4961018, 15732534, 12246302,  5213411, 12065323, 15785731,  9671578, 11979729,  5621907, 14877033, 17052063,  7010448, 12893207, 12376229,  2982208, 13728016,   386398, 14990724,  9877149,  4682534, 11167007,   888551,    13356, 12687636,  3714742, 14258362, 11436077,  8749551, 13110797, 13725918, 16930189, 13439097,  4790482, 13736492,  5966677,  5712035,  5709468,  4303289, 13013945,  4888751,  2311904, 10900276, 13550471,  4869073,  9941904, 11318188, 12516527, 14653792,  7197671,  3483274,  4914861, 11283029, 18320007, 12478967, 12734622,  4937540, 10188621, 15416838,  1354070, 15775050, 10303394,  9642043,  9671710,  1424505,  2168702, 15758444, 10205902,  2324599,  5880230, 14405075,  7322644,  4226514, 12882469, 13600406, 15182180, 14405070,  6265352,  5251734,  3645992, 12187344,  5076821, 13640004, 13868829,   696751, 14603232,  3417286, 15497706, 17052095, 14387494, 15497715,   683729,   514662,  9267714,  8962759, 14522619, 14049827,  9482089, 12900369, 11507838, 16889450, 15116510, 12695721, 16889465,   695248,   194645,  1470856,  7824758, 16992063,  4667123, 12995641,  2133982, 15785726, 14962133, 11735613, 14196438,  5095400,  8806731, 16923872,  8324403, 18305980,  8558332,  2398164,  9430517, 16921343, 17746711,  8272987,  5412956,  1388762,  1149402, 13382765,  9100116, 15123970, 12410405, 15755242, 13143755,  9892523,   273486, 15497717, 11636414,   429540, 14587610, 10059845,  9302527,  3713435,  8814129, 12376508,  4159863,  4984829, 11671239,  5214148,  4587928,  1642716, 11379032,  4389308,  5041437,  3029776, 15572756, 12782477, 13180536, 11817778,  5749302,  8401507,  4377447,  9142892, 12691876,  2786489,  8655314, 10034774, 17909265,    40375,  2535118,  6239198, 16891296, 14740937,  7722442, 13660081, 15774863,  2404524, 15112879,  2302942,  4903089,  8095268, 16963026,  7488329, 14408190, 11271910, 11392688, 15382376,   164827,  9945524, 15021620,  8552244, 15173625, 12374941,  3652047,   120073,  5213202, 11509758,  6237855, 15455058, 15222654, 11698387, 14391296, 15783664,  9468400,  7964536,  4489949,  1749038, 12347103,  4430316, 10776513, 11334402,  1786152, 10764796,  5667426, 11846540,  5211976,  1523140,  2890431,  6132264, 12188267, 11592758, 12556573,  5365921,  5249152,  5058729,  9182852,  9887006,  7539010,  5665736, 12815838,  6354882,  9665428,  4828790,  9921910,    12452,  4785811,   362790,  4829721, 12577467,  8158913, 15497713,  4985794, 17052037,  5054237, 16992064,  2602134, 16933669,  4786475, 12195435, 14391956,   260236,  1254817,  4544618,  4082686,  2338505, 15173250, 13389456,  6976749,   283999,  4418630,  8111826,  4829746,   205600, 11279441,  7351507,  4391348, 11621537, 10205931, 15540760,  4826728,  6381088, 15777262, 14647125,  4087379,  4825511,   516904, 10539876,   668120,  6194630,  9179861,  9426624, 15331937,  2277639,  2722370, 12660339,  5564482, 15486278,  8167016,  5727219, 11701495, 12498246,  5369544,  3769272, 18079943,  2027441,  5212466, 14534878,  4155411,  4788291, 14382111, 15783209,  3661091,  1991225,  4665308,  4089849,  6121997, 10375381,  9953688,  2107592,  3685676,  8941681, 15497711, 14721010,  4091368,   370493,  8446310,  8299606, 11394293,  9670171, 14665220, 14498471,  4905402, 10325315,  4377833,  6113071, 12415036, 13206119,  5749348,  9676400, 12605621, 10940776, 10865679,  4545397,   438961, 12385288,  9343200, 14055936,  2737706, 10806410,  9717295,   401232,  2411612, 10020628,  4668304,  2719180,  4124757,  3800184,  8955920, 17878003,  3457201, 11662799,   480804, 10216431,   262533,  2321156,  3558825,  8420142,  5535246,  8108090, 11525683,  2339743,  2702368, 15782201,  9622933,  5803275,  4799322,  8861730, 11997412,   263716, 10374156,  5251771, 12311766, 15130412, 14005418,  1958785, 10411862,  3507264,  7598233,  1315742, 10551696, 12516524, 12564783,  4376883, 12608527, 12300535,  1503666, 13382732, 16933730,  9304687, 10020609, 15222852,  4058309,  7829965,  5833947,  2075452, 10769415, 10449864, 13015490,  4445483, 15375677, 13656597,   786106,  2578846, 16812817, 16864241,   724395, 14319393,  5211906,  2420477, 11980591,  4477602,  5753738,  4155490, 15586248,  6348276,  4373269, 12806086, 13658101,  4017299,  3688647,  2620738,  1674572, 10989101,   674121,  2532289,  2902958,  6283611, 12381426,  5461018,    87628, 13437976, 14387569, 15152744, 15089149,    52092,  3101551, 14970310, 12376235,  4587943, 12772245,  3614911,  6115425, 15600216, 15667608, 11492005, 18219058, 14389376,  9740416,   376774, 11292236, 11985168, 16991482,  4627677, 14692816, 12883886,  1230286, 12772298,    10238,    33100,   437197, 12416307,  5791466,   876203, 13437721,  9224685,  4903695,  9623653, 17871236,  1863977,   138401,  4906322,  8013274, 15497716,   262664,  1302695,  3945330, 12772314, 15227560, 12760071,  1245908, 11752692,  2561044,  9265062, 12772267, 15497707, 12533702,  8160844,  4191922, 15784143, 11799140, 15678554, 16923221,  8110789,  3223586, 16853856, 15503417,   367626,  4234774,  2233420, 12862586,  2338871, 11722161, 18306391,  9625522,  9608331, 15783414, 12697370, 12585830,  1499790,  9788406, 12415166,  2799498,  9670031,  5721376, 13377717,  1125848, 15115187, 13456179,   655908,  1386641,  9788370, 11700478,  7643877,  5724435,  8551613,  8555568,  2145909,  4192493,  5209897, 15053827, 10424499, 14401884, 12554896, 15375684,  6115756, 15497739,  3355217, 11343020,  3114018,  9518928, 14389187,  5329074,  3079878,  9294695, 12875417,  1895698,  3717078,  4098809, 11165152,  3796140,  4378955,  7419344,  2629166,  2524675,  4717481, 12495884,   506061, 12459835, 15380924, 15730788,  4413992,  5413891,  5021856,  4669960,  4572001,  9768138,  2632430, 11705052,  4554077,  3695796, 15118938,  5213298,  3212547, 15004152,  9426861, 10356674, 15548862, 16942069, 13746989, 15742496, 11872546, 11509850,  3716529, 16991285, 15116516,  3374280, 10821571,  8063083,  6008053, 13769725,  8000915, 10134443,  3948027,   215818, 11830359, 14208413, 15785746, 18309937,  5537154,  5621665, 15732552,   421631, 10132402,  9750297,  5041243,  3732265,  3916059,  4708570,  9454984, 15373200,  2083197,  5022822,  2239449,  4339707, 15789036, 10222550, 14723614, 10118096,  4584545, 11226186, 12286542, 13308847,  3163848, 14657908, 15112663, 15027042, 15182181, 14387558, 12053347,  7293390,  5921301,  4796748,  9882561, 11485645,  4228834, 15309975,  7835666,   272636,   426128, 15775173,  5327378, 11226654)

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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.