Skip to content

Fix vulnerability statistics adjustment service

Mehmet Emin INAC requested to merge minac_439051_dont_try_to_adjust_statistics_for_non_existing_ids into master

What does this MR do and why?

The service logic was failing when it receives a project ID which was recently deleted from the database.

With this change, we are now making sure that the project exists in the database while trying to insert a new vulnerability statistics record.

Related to ActiveRecord::InvalidForeignKey Sidekiq/Vulne... (#439051 - closed)

Database review

Both the old and new queries are using ~750 MiB buffers which is a lot but I have selected project IDs for the worst-case scenario. That being said, we can implement a batching with a follow-up MR to UPSERT the records in smaller batches.

Old Query 
INSERT INTO vulnerability_statistics (project_id, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) ( WITH project_ids AS (
        SELECT
            unnest(ARRAY[13083, 14022, 20699, 74823, 250833, 278964, 413007, 430285, 554859, 734943, 1507906, 1777822, 1794617, 2009901, 2670515, 2953390, 3828396, 3842996, 3843116, 3844141, 4176070, 4456656, 5261717, 5456231, 5457651, 5457755, 5465687, 5467143, 5467277, 5474112, 5486671, 5509547, 5647182, 5727416, 5737157, 5778095, 5964710, 5996549, 6126012, 6130122, 10400718, 10472408, 10476326, 10748426, 10846938, 10861561, 11139665, 11317506, 11432834, 11511606, 11520423, 11574953, 11625232, 11688089, 11915984, 12006272, 12274078, 12507547, 13017938, 13017973, 13150952, 13348998, 13467157, 13664986, 13672681, 13683636, 13822214, 13831684, 13922331, 14292404, 6237085, 6237088, 6237091, 6328050, 6392811, 6457868, 6523803, 6535935, 6802300, 7071551, 7145793, 7660893, 7688358, 7776928, 14645774, 7792567, 8368695, 8368700, 14853550, 15477483, 14921629, 15161313, 16239404, 9049889, 9358979, 9396716, 9438583, 9450192, 9450195, 9450197, 9762266, 15369510, 15393051, 15572444, 15642544, 15683922, 15685819, 15685887, 15685912, 15704133, 16573099, 15704782, 15769471, 16590122, 15769622, 16683102, 16613296, 16700143, 16816732, 17281958, 16819118, 16819125, 16819131, 16819134, 16819140, 16819151, 16819248, 16842968, 17450826, 17035255, 17530059, 18042963, 17043690, 17738687, 17370206, 17371314, 18147374, 17930014, 17978213, 18021647, 18026144, 18315938, 18446184, 18464659, 18466096, 17823896, 18342819, 18346158, 18347703, 18347716, 18347776, 18347780, 18347796, 18347808, 18347809, 18347819, 18347820, 18347830, 18347831, 18347833, 18347834, 18347835, 18347836, 18347848, 18347863, 18347890, 18347935, 18348449, 18374998, 18789638, 18792504, 18855948, 19042106, 19047934, 18897919, 18899486, 18925373, 18943607, 18977208, 18991900, 19019153, 19031064, 18860383, 18863350, 18877555, 19253018, 19413012, 19413496, 19315261, 20162469, 19483656, 19532875, 19861191, 19871264, 19871573, 19904939, 20234539, 19936172, 20334170, 20336302, 20337863, 20401626, 21351796, 20773921, 20785013, 20791019, 20851082, 21005471, 21439066, 21479276, 21731412, 21734866, 21740210, 21224328, 21250701, 21483460, 21501997, 21582428, 22111360, 22133520, 22382594, 22200396, 22215226, 21939352, 22409005, 22427391, 22734535, 22441624, 22511552, 22517790, 23345837, 22564246, 23069693, 23070140, 23070377, 23070395, 22848448, 22869331, 22877515, 22899058, 22907418, 22955441, 22979053, 22979589, 22980130, 23011180, 23174714, 23176783, 23176971, 23221705, 23221789, 23365123, 23455079, 23505725, 23508645, 23545481, 23597850, 23599168, 23729229, 23809347, 23921884, 23924111, 23944319, 23465101, 24134396, 24055623, 24059189, 24075172, 24249085, 24257614, 24189296, 24200638, 24225194, 24544020, 24298348, 24302484, 24313398, 24313598, 24317064, 24631368, 24673064, 24544330, 25230764, 25312761, 26074395, 25534115, 25537666, 25538517, 25944737, 25560556, 25582529, 25582530, 25582531, 25582532, 25582534, 25582535, 25582562, 25584602, 25586931, 25588419, 26074460, 26074605, 26074844, 26217586, 26598766, 25829938, 25838334, 25839455, 25840330, 25840667, 26543618, 26549908, 26394287, 26434779, 26435679, 26751150, 26630862, 26638651, 26638991, 26808701, 26808774, 26882341, 26882878, 27038823, 26997949, 27051800, 27060148, 27069846, 27079314, 27269166, 26893673, 27540367, 27497069, 27214210, 27226337, 27227767, 27229445, 27540387, 27540393, 27540443, 27540450, 27540464, 27792539, 27628435, 27668507, 27819859, 27821122, 27821148, 28274618, 27918529, 27920967, 27958807, 28274632, 28421100, 28345906, 28599337, 28022549, 28665110, 28422773, 28443011, 28446310, 28455577, 28922692, 28933533, 28952100, 30152225, 29753686, 29955779, 29970261, 30205118, 29071939, 29282558, 29843242, 30385968, 30338950, 31188743, 30561867, 30587963, 30656896, 30661297, 31022487, 31048485, 31195676, 31202357, 31202639, 31202862, 31203840, 31203942, 31204058, 31554001, 31401752, 31761828, 31636488, 31778258, 32189602, 32024675, 32121908, 32125531, 32126861, 32149347, 32158411, 31930487, 31942080, 31942208, 32318686, 34347217, 32908327, 32950782, 32954475, 33421273, 33497888, 33498542, 33498662, 33754619, 34107681, 32581778, 33211560, 33312519, 33427636, 33427648, 33427704, 33427764, 33427894, 33428133, 33428714, 33428778, 33611983, 33931271, 34391841, 34392208, 34392284, 34392376, 34392435, 34392571, 34420392, 34655801, 34675721, 34757919, 34648367, 34770409, 34783146, 35156331, 34937947, 34951944, 35086421, 35090945, 35109800, 35109805, 35188840, 35212390, 35213958, 35012035, 35368955, 35304830, 35304915, 35305073, 35305128, 35309593, 35369092, 35369131, 35369174, 35369235, 35369870, 35369914, 35369937, 35369953, 35369992, 35370025, 35435896, 35549088, 35436083, 35836317, 35486102, 35486142, 35486190, 35486233, 35486264, 35486304, 35486348, 35486477, 35486541, 35486664, 35486704, 35486750, 35486787, 35513182, 35888489, 35749134, 36057379, 36078023, 36169849, 36317835, 36332072, 36232127, 36698255, 36468333, 36731661, 37467652, 37023406, 37051576]::bigint[]) AS project_id
)
        SELECT
            project_ids.project_id AS project_id,
            COALESCE(severity_counts.total, 0) AS total,
            COALESCE(severity_counts.info, 0) AS info,
            COALESCE(severity_counts.unknown, 0) AS unknown,
            COALESCE(severity_counts.low, 0) AS low,
            COALESCE(severity_counts.medium, 0) AS medium,
            COALESCE(severity_counts.high, 0) AS high,
            COALESCE(severity_counts.critical, 0) AS critical,
            (
                CASE WHEN severity_counts.critical > 0 THEN
                    4
                WHEN severity_counts.high > 0
                    OR severity_counts.unknown > 0 THEN
                    3
                WHEN severity_counts.medium > 0 THEN
                    2
                WHEN severity_counts.low > 0 THEN
                    1
                ELSE
                    0
                END) AS letter_grade,
            now() AS created_at,
            now() AS updated_at
        FROM
            project_ids
        LEFT OUTER JOIN (
        SELECT
            vulnerability_reads.project_id AS project_id,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE severity = 1) AS info,
            COUNT(*) FILTER (WHERE severity = 2) AS unknown,
            COUNT(*) FILTER (WHERE severity = 4) AS low,
            COUNT(*) FILTER (WHERE severity = 5) AS medium,
            COUNT(*) FILTER (WHERE severity = 6) AS high,
            COUNT(*) FILTER (WHERE severity = 7) AS critical
        FROM
            vulnerability_reads
        WHERE
            vulnerability_reads.project_id IN (13083, 14022, 20699, 74823, 250833, 278964, 413007, 430285, 554859, 734943, 1507906, 1777822, 1794617, 2009901, 2670515, 2953390, 3828396, 3842996, 3843116, 3844141, 4176070, 4456656, 5261717, 5456231, 5457651, 5457755, 5465687, 5467143, 5467277, 5474112, 5486671, 5509547, 5647182, 5727416, 5737157, 5778095, 5964710, 5996549, 6126012, 6130122, 10400718, 10472408, 10476326, 10748426, 10846938, 10861561, 11139665, 11317506, 11432834, 11511606, 11520423, 11574953, 11625232, 11688089, 11915984, 12006272, 12274078, 12507547, 13017938, 13017973, 13150952, 13348998, 13467157, 13664986, 13672681, 13683636, 13822214, 13831684, 13922331, 14292404, 6237085, 6237088, 6237091, 6328050, 6392811, 6457868, 6523803, 6535935, 6802300, 7071551, 7145793, 7660893, 7688358, 7776928, 14645774, 7792567, 8368695, 8368700, 14853550, 15477483, 14921629, 15161313, 16239404, 9049889, 9358979, 9396716, 9438583, 9450192, 9450195, 9450197, 9762266, 15369510, 15393051, 15572444, 15642544, 15683922, 15685819, 15685887, 15685912, 15704133, 16573099, 15704782, 15769471, 16590122, 15769622, 16683102, 16613296, 16700143, 16816732, 17281958, 16819118, 16819125, 16819131, 16819134, 16819140, 16819151, 16819248, 16842968, 17450826, 17035255, 17530059, 18042963, 17043690, 17738687, 17370206, 17371314, 18147374, 17930014, 17978213, 18021647, 18026144, 18315938, 18446184, 18464659, 18466096, 17823896, 18342819, 18346158, 18347703, 18347716, 18347776, 18347780, 18347796, 18347808, 18347809, 18347819, 18347820, 18347830, 18347831, 18347833, 18347834, 18347835, 18347836, 18347848, 18347863, 18347890, 18347935, 18348449, 18374998, 18789638, 18792504, 18855948, 19042106, 19047934, 18897919, 18899486, 18925373, 18943607, 18977208, 18991900, 19019153, 19031064, 18860383, 18863350, 18877555, 19253018, 19413012, 19413496, 19315261, 20162469, 19483656, 19532875, 19861191, 19871264, 19871573, 19904939, 20234539, 19936172, 20334170, 20336302, 20337863, 20401626, 21351796, 20773921, 20785013, 20791019, 20851082, 21005471, 21439066, 21479276, 21731412, 21734866, 21740210, 21224328, 21250701, 21483460, 21501997, 21582428, 22111360, 22133520, 22382594, 22200396, 22215226, 21939352, 22409005, 22427391, 22734535, 22441624, 22511552, 22517790, 23345837, 22564246, 23069693, 23070140, 23070377, 23070395, 22848448, 22869331, 22877515, 22899058, 22907418, 22955441, 22979053, 22979589, 22980130, 23011180, 23174714, 23176783, 23176971, 23221705, 23221789, 23365123, 23455079, 23505725, 23508645, 23545481, 23597850, 23599168, 23729229, 23809347, 23921884, 23924111, 23944319, 23465101, 24134396, 24055623, 24059189, 24075172, 24249085, 24257614, 24189296, 24200638, 24225194, 24544020, 24298348, 24302484, 24313398, 24313598, 24317064, 24631368, 24673064, 24544330, 25230764, 25312761, 26074395, 25534115, 25537666, 25538517, 25944737, 25560556, 25582529, 25582530, 25582531, 25582532, 25582534, 25582535, 25582562, 25584602, 25586931, 25588419, 26074460, 26074605, 26074844, 26217586, 26598766, 25829938, 25838334, 25839455, 25840330, 25840667, 26543618, 26549908, 26394287, 26434779, 26435679, 26751150, 26630862, 26638651, 26638991, 26808701, 26808774, 26882341, 26882878, 27038823, 26997949, 27051800, 27060148, 27069846, 27079314, 27269166, 26893673, 27540367, 27497069, 27214210, 27226337, 27227767, 27229445, 27540387, 27540393, 27540443, 27540450, 27540464, 27792539, 27628435, 27668507, 27819859, 27821122, 27821148, 28274618, 27918529, 27920967, 27958807, 28274632, 28421100, 28345906, 28599337, 28022549, 28665110, 28422773, 28443011, 28446310, 28455577, 28922692, 28933533, 28952100, 30152225, 29753686, 29955779, 29970261, 30205118, 29071939, 29282558, 29843242, 30385968, 30338950, 31188743, 30561867, 30587963, 30656896, 30661297, 31022487, 31048485, 31195676, 31202357, 31202639, 31202862, 31203840, 31203942, 31204058, 31554001, 31401752, 31761828, 31636488, 31778258, 32189602, 32024675, 32121908, 32125531, 32126861, 32149347, 32158411, 31930487, 31942080, 31942208, 32318686, 34347217, 32908327, 32950782, 32954475, 33421273, 33497888, 33498542, 33498662, 33754619, 34107681, 32581778, 33211560, 33312519, 33427636, 33427648, 33427704, 33427764, 33427894, 33428133, 33428714, 33428778, 33611983, 33931271, 34391841, 34392208, 34392284, 34392376, 34392435, 34392571, 34420392, 34655801, 34675721, 34757919, 34648367, 34770409, 34783146, 35156331, 34937947, 34951944, 35086421, 35090945, 35109800, 35109805, 35188840, 35212390, 35213958, 35012035, 35368955, 35304830, 35304915, 35305073, 35305128, 35309593, 35369092, 35369131, 35369174, 35369235, 35369870, 35369914, 35369937, 35369953, 35369992, 35370025, 35435896, 35549088, 35436083, 35836317, 35486102, 35486142, 35486190, 35486233, 35486264, 35486304, 35486348, 35486477, 35486541, 35486664, 35486704, 35486750, 35486787, 35513182, 35888489, 35749134, 36057379, 36078023, 36169849, 36317835, 36332072, 36232127, 36698255, 36468333, 36731661, 37467652, 37023406, 37051576)
            AND vulnerability_reads.state IN (1, 4)
        GROUP BY
            vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id)
ON CONFLICT (project_id)
    DO UPDATE SET
        total = EXCLUDED.total,
        info = EXCLUDED.info,
        unknown = EXCLUDED.unknown,
        low = EXCLUDED.low,
        medium = EXCLUDED.medium,
        high = EXCLUDED.high,
        critical = EXCLUDED.critical,
        letter_grade = EXCLUDED.letter_grade,
        updated_at = EXCLUDED.updated_at

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25696/commands/81109

New Query 
INSERT INTO vulnerability_statistics (project_id, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) ( WITH project_ids AS (
        SELECT
            id AS project_id
        FROM
            projects
        WHERE
            projects.id IN (13083, 14022, 20699, 74823, 250833, 278964, 413007, 430285, 554859, 734943, 1507906, 1777822, 1794617, 2009901, 2670515, 2953390, 3828396, 3842996, 3843116, 3844141, 4176070, 4456656, 5261717, 5456231, 5457651, 5457755, 5465687, 5467143, 5467277, 5474112, 5486671, 5509547, 5647182, 5727416, 5737157, 5778095, 5964710, 5996549, 6126012, 6130122, 10400718, 10472408, 10476326, 10748426, 10846938, 10861561, 11139665, 11317506, 11432834, 11511606, 11520423, 11574953, 11625232, 11688089, 11915984, 12006272, 12274078, 12507547, 13017938, 13017973, 13150952, 13348998, 13467157, 13664986, 13672681, 13683636, 13822214, 13831684, 13922331, 14292404, 6237085, 6237088, 6237091, 6328050, 6392811, 6457868, 6523803, 6535935, 6802300, 7071551, 7145793, 7660893, 7688358, 7776928, 14645774, 7792567, 8368695, 8368700, 14853550, 15477483, 14921629, 15161313, 16239404, 9049889, 9358979, 9396716, 9438583, 9450192, 9450195, 9450197, 9762266, 15369510, 15393051, 15572444, 15642544, 15683922, 15685819, 15685887, 15685912, 15704133, 16573099, 15704782, 15769471, 16590122, 15769622, 16683102, 16613296, 16700143, 16816732, 17281958, 16819118, 16819125, 16819131, 16819134, 16819140, 16819151, 16819248, 16842968, 17450826, 17035255, 17530059, 18042963, 17043690, 17738687, 17370206, 17371314, 18147374, 17930014, 17978213, 18021647, 18026144, 18315938, 18446184, 18464659, 18466096, 17823896, 18342819, 18346158, 18347703, 18347716, 18347776, 18347780, 18347796, 18347808, 18347809, 18347819, 18347820, 18347830, 18347831, 18347833, 18347834, 18347835, 18347836, 18347848, 18347863, 18347890, 18347935, 18348449, 18374998, 18789638, 18792504, 18855948, 19042106, 19047934, 18897919, 18899486, 18925373, 18943607, 18977208, 18991900, 19019153, 19031064, 18860383, 18863350, 18877555, 19253018, 19413012, 19413496, 19315261, 20162469, 19483656, 19532875, 19861191, 19871264, 19871573, 19904939, 20234539, 19936172, 20334170, 20336302, 20337863, 20401626, 21351796, 20773921, 20785013, 20791019, 20851082, 21005471, 21439066, 21479276, 21731412, 21734866, 21740210, 21224328, 21250701, 21483460, 21501997, 21582428, 22111360, 22133520, 22382594, 22200396, 22215226, 21939352, 22409005, 22427391, 22734535, 22441624, 22511552, 22517790, 23345837, 22564246, 23069693, 23070140, 23070377, 23070395, 22848448, 22869331, 22877515, 22899058, 22907418, 22955441, 22979053, 22979589, 22980130, 23011180, 23174714, 23176783, 23176971, 23221705, 23221789, 23365123, 23455079, 23505725, 23508645, 23545481, 23597850, 23599168, 23729229, 23809347, 23921884, 23924111, 23944319, 23465101, 24134396, 24055623, 24059189, 24075172, 24249085, 24257614, 24189296, 24200638, 24225194, 24544020, 24298348, 24302484, 24313398, 24313598, 24317064, 24631368, 24673064, 24544330, 25230764, 25312761, 26074395, 25534115, 25537666, 25538517, 25944737, 25560556, 25582529, 25582530, 25582531, 25582532, 25582534, 25582535, 25582562, 25584602, 25586931, 25588419, 26074460, 26074605, 26074844, 26217586, 26598766, 25829938, 25838334, 25839455, 25840330, 25840667, 26543618, 26549908, 26394287, 26434779, 26435679, 26751150, 26630862, 26638651, 26638991, 26808701, 26808774, 26882341, 26882878, 27038823, 26997949, 27051800, 27060148, 27069846, 27079314, 27269166, 26893673, 27540367, 27497069, 27214210, 27226337, 27227767, 27229445, 27540387, 27540393, 27540443, 27540450, 27540464, 27792539, 27628435, 27668507, 27819859, 27821122, 27821148, 28274618, 27918529, 27920967, 27958807, 28274632, 28421100, 28345906, 28599337, 28022549, 28665110, 28422773, 28443011, 28446310, 28455577, 28922692, 28933533, 28952100, 30152225, 29753686, 29955779, 29970261, 30205118, 29071939, 29282558, 29843242, 30385968, 30338950, 31188743, 30561867, 30587963, 30656896, 30661297, 31022487, 31048485, 31195676, 31202357, 31202639, 31202862, 31203840, 31203942, 31204058, 31554001, 31401752, 31761828, 31636488, 31778258, 32189602, 32024675, 32121908, 32125531, 32126861, 32149347, 32158411, 31930487, 31942080, 31942208, 32318686, 34347217, 32908327, 32950782, 32954475, 33421273, 33497888, 33498542, 33498662, 33754619, 34107681, 32581778, 33211560, 33312519, 33427636, 33427648, 33427704, 33427764, 33427894, 33428133, 33428714, 33428778, 33611983, 33931271, 34391841, 34392208, 34392284, 34392376, 34392435, 34392571, 34420392, 34655801, 34675721, 34757919, 34648367, 34770409, 34783146, 35156331, 34937947, 34951944, 35086421, 35090945, 35109800, 35109805, 35188840, 35212390, 35213958, 35012035, 35368955, 35304830, 35304915, 35305073, 35305128, 35309593, 35369092, 35369131, 35369174, 35369235, 35369870, 35369914, 35369937, 35369953, 35369992, 35370025, 35435896, 35549088, 35436083, 35836317, 35486102, 35486142, 35486190, 35486233, 35486264, 35486304, 35486348, 35486477, 35486541, 35486664, 35486704, 35486750, 35486787, 35513182, 35888489, 35749134, 36057379, 36078023, 36169849, 36317835, 36332072, 36232127, 36698255, 36468333, 36731661, 37467652, 37023406, 37051576))
        SELECT
            project_ids.project_id AS project_id,
            COALESCE(severity_counts.total, 0) AS total,
            COALESCE(severity_counts.info, 0) AS info,
            COALESCE(severity_counts.unknown, 0) AS unknown,
            COALESCE(severity_counts.low, 0) AS low,
            COALESCE(severity_counts.medium, 0) AS medium,
            COALESCE(severity_counts.high, 0) AS high,
            COALESCE(severity_counts.critical, 0) AS critical,
            (
                CASE WHEN severity_counts.critical > 0 THEN
                    4
                WHEN severity_counts.high > 0
                    OR severity_counts.unknown > 0 THEN
                    3
                WHEN severity_counts.medium > 0 THEN
                    2
                WHEN severity_counts.low > 0 THEN
                    1
                ELSE
                    0
                END) AS letter_grade,
            now() AS created_at,
            now() AS updated_at
        FROM
            project_ids
        LEFT OUTER JOIN (
        SELECT
            vulnerability_reads.project_id AS project_id,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE severity = 1) AS info,
            COUNT(*) FILTER (WHERE severity = 2) AS unknown,
            COUNT(*) FILTER (WHERE severity = 4) AS low,
            COUNT(*) FILTER (WHERE severity = 5) AS medium,
            COUNT(*) FILTER (WHERE severity = 6) AS high,
            COUNT(*) FILTER (WHERE severity = 7) AS critical
        FROM
            vulnerability_reads
        WHERE
            vulnerability_reads.project_id IN (13083, 14022, 20699, 74823, 250833, 278964, 413007, 430285, 554859, 734943, 1507906, 1777822, 1794617, 2009901, 2670515, 2953390, 3828396, 3842996, 3843116, 3844141, 4176070, 4456656, 5261717, 5456231, 5457651, 5457755, 5465687, 5467143, 5467277, 5474112, 5486671, 5509547, 5647182, 5727416, 5737157, 5778095, 5964710, 5996549, 6126012, 6130122, 10400718, 10472408, 10476326, 10748426, 10846938, 10861561, 11139665, 11317506, 11432834, 11511606, 11520423, 11574953, 11625232, 11688089, 11915984, 12006272, 12274078, 12507547, 13017938, 13017973, 13150952, 13348998, 13467157, 13664986, 13672681, 13683636, 13822214, 13831684, 13922331, 14292404, 6237085, 6237088, 6237091, 6328050, 6392811, 6457868, 6523803, 6535935, 6802300, 7071551, 7145793, 7660893, 7688358, 7776928, 14645774, 7792567, 8368695, 8368700, 14853550, 15477483, 14921629, 15161313, 16239404, 9049889, 9358979, 9396716, 9438583, 9450192, 9450195, 9450197, 9762266, 15369510, 15393051, 15572444, 15642544, 15683922, 15685819, 15685887, 15685912, 15704133, 16573099, 15704782, 15769471, 16590122, 15769622, 16683102, 16613296, 16700143, 16816732, 17281958, 16819118, 16819125, 16819131, 16819134, 16819140, 16819151, 16819248, 16842968, 17450826, 17035255, 17530059, 18042963, 17043690, 17738687, 17370206, 17371314, 18147374, 17930014, 17978213, 18021647, 18026144, 18315938, 18446184, 18464659, 18466096, 17823896, 18342819, 18346158, 18347703, 18347716, 18347776, 18347780, 18347796, 18347808, 18347809, 18347819, 18347820, 18347830, 18347831, 18347833, 18347834, 18347835, 18347836, 18347848, 18347863, 18347890, 18347935, 18348449, 18374998, 18789638, 18792504, 18855948, 19042106, 19047934, 18897919, 18899486, 18925373, 18943607, 18977208, 18991900, 19019153, 19031064, 18860383, 18863350, 18877555, 19253018, 19413012, 19413496, 19315261, 20162469, 19483656, 19532875, 19861191, 19871264, 19871573, 19904939, 20234539, 19936172, 20334170, 20336302, 20337863, 20401626, 21351796, 20773921, 20785013, 20791019, 20851082, 21005471, 21439066, 21479276, 21731412, 21734866, 21740210, 21224328, 21250701, 21483460, 21501997, 21582428, 22111360, 22133520, 22382594, 22200396, 22215226, 21939352, 22409005, 22427391, 22734535, 22441624, 22511552, 22517790, 23345837, 22564246, 23069693, 23070140, 23070377, 23070395, 22848448, 22869331, 22877515, 22899058, 22907418, 22955441, 22979053, 22979589, 22980130, 23011180, 23174714, 23176783, 23176971, 23221705, 23221789, 23365123, 23455079, 23505725, 23508645, 23545481, 23597850, 23599168, 23729229, 23809347, 23921884, 23924111, 23944319, 23465101, 24134396, 24055623, 24059189, 24075172, 24249085, 24257614, 24189296, 24200638, 24225194, 24544020, 24298348, 24302484, 24313398, 24313598, 24317064, 24631368, 24673064, 24544330, 25230764, 25312761, 26074395, 25534115, 25537666, 25538517, 25944737, 25560556, 25582529, 25582530, 25582531, 25582532, 25582534, 25582535, 25582562, 25584602, 25586931, 25588419, 26074460, 26074605, 26074844, 26217586, 26598766, 25829938, 25838334, 25839455, 25840330, 25840667, 26543618, 26549908, 26394287, 26434779, 26435679, 26751150, 26630862, 26638651, 26638991, 26808701, 26808774, 26882341, 26882878, 27038823, 26997949, 27051800, 27060148, 27069846, 27079314, 27269166, 26893673, 27540367, 27497069, 27214210, 27226337, 27227767, 27229445, 27540387, 27540393, 27540443, 27540450, 27540464, 27792539, 27628435, 27668507, 27819859, 27821122, 27821148, 28274618, 27918529, 27920967, 27958807, 28274632, 28421100, 28345906, 28599337, 28022549, 28665110, 28422773, 28443011, 28446310, 28455577, 28922692, 28933533, 28952100, 30152225, 29753686, 29955779, 29970261, 30205118, 29071939, 29282558, 29843242, 30385968, 30338950, 31188743, 30561867, 30587963, 30656896, 30661297, 31022487, 31048485, 31195676, 31202357, 31202639, 31202862, 31203840, 31203942, 31204058, 31554001, 31401752, 31761828, 31636488, 31778258, 32189602, 32024675, 32121908, 32125531, 32126861, 32149347, 32158411, 31930487, 31942080, 31942208, 32318686, 34347217, 32908327, 32950782, 32954475, 33421273, 33497888, 33498542, 33498662, 33754619, 34107681, 32581778, 33211560, 33312519, 33427636, 33427648, 33427704, 33427764, 33427894, 33428133, 33428714, 33428778, 33611983, 33931271, 34391841, 34392208, 34392284, 34392376, 34392435, 34392571, 34420392, 34655801, 34675721, 34757919, 34648367, 34770409, 34783146, 35156331, 34937947, 34951944, 35086421, 35090945, 35109800, 35109805, 35188840, 35212390, 35213958, 35012035, 35368955, 35304830, 35304915, 35305073, 35305128, 35309593, 35369092, 35369131, 35369174, 35369235, 35369870, 35369914, 35369937, 35369953, 35369992, 35370025, 35435896, 35549088, 35436083, 35836317, 35486102, 35486142, 35486190, 35486233, 35486264, 35486304, 35486348, 35486477, 35486541, 35486664, 35486704, 35486750, 35486787, 35513182, 35888489, 35749134, 36057379, 36078023, 36169849, 36317835, 36332072, 36232127, 36698255, 36468333, 36731661, 37467652, 37023406, 37051576)
            AND vulnerability_reads.state IN (1, 4)
        GROUP BY
            vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id)
ON CONFLICT (project_id)
    DO UPDATE SET
        total = EXCLUDED.total,
        info = EXCLUDED.info,
        unknown = EXCLUDED.unknown,
        low = EXCLUDED.low,
        medium = EXCLUDED.medium,
        high = EXCLUDED.high,
        critical = EXCLUDED.critical,
        letter_grade = EXCLUDED.letter_grade,
        updated_at = EXCLUDED.updated_at

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25696/commands/81110

Edited by Mehmet Emin INAC

Merge request reports