Add project archive event handler for vulnerability_namespace_statistics

What does this MR do and why?

Add event handlers for project archive, unarchive, and delete events for vulnerability namespace statistics. It recalculates the project's group statistics after project records are archived or deleted. The resulting statistics diff is propagated to ancestor groups using UpdateService, with a modified diff to prevent redundant updates to the direct parent.

This MR also modifies the UpdateService to return the computed diff and upserts "empty" records into the database to support zero-value vulnerability counters.

Changelog: added
EE: true

Related issue

Update statistics on project or group delete or... (#534725 - closed) • Gal Katz, Yuval Siev • 18.2

Query plans

Original query:

Raw SQL

INSERT INTO vulnerability_namespace_statistics (total, info, unknown, low, medium, high, critical, traversal_ids, namespace_id, created_at, updated_at) ( WITH namespace_data (
        namespace_id, traversal_ids, next_traversal_id
) AS (
            VALUES (9970, ARRAY[9970]::bigint[], ARRAY[9971]::bigint[]))
            SELECT
                SUM(total) AS total,
                SUM(info) AS info,
                SUM(unknown) AS unknown,
                SUM(low) AS low,
                SUM(medium) AS medium,
                SUM(high) AS high,
                SUM(critical) AS critical,
                namespace_data.traversal_ids AS traversal_ids,
                namespace_data.namespace_id AS namespace_id,
                now() AS created_at,
                now() AS updated_at
            FROM
                vulnerability_statistics,
                namespace_data
            WHERE
                vulnerability_statistics.archived = FALSE
                AND vulnerability_statistics.traversal_ids >= namespace_data.traversal_ids
                AND vulnerability_statistics.traversal_ids < namespace_data.next_traversal_id
            GROUP BY
                namespace_data.traversal_ids,
                namespace_id)
    ON CONFLICT (namespace_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,
            updated_at = EXCLUDED.updated_at,
            traversal_ids = EXCLUDED.traversal_ids

Plan

See full plan here

ModifyTable on public.vulnerability_namespace_statistics  (cost=0.42..2265.99 rows=0 width=0) (actual time=681.751..681.753 rows=0 loops=1)
   Buffers: shared hit=150 read=1170 dirtied=166 written=4
   WAL: records=204 fpi=158 bytes=1049392
   I/O Timings: read=665.951 write=0.106
   ->  Subquery Scan on *SELECT*  (cost=0.42..2265.99 rows=1 width=92) (actual time=680.544..680.546 rows=1 loops=1)
         Buffers: shared hit=142 read=1167 dirtied=159
         WAL: records=196 fpi=158 bytes=1048688
         I/O Timings: read=665.319 write=0.000
         ->  Aggregate  (cost=0.42..2265.96 rows=1 width=108) (actual time=677.954..677.955 rows=1 loops=1)
               Buffers: shared hit=133 read=1162 dirtied=158
               WAL: records=195 fpi=158 bytes=1048589
               I/O Timings: read=662.883 write=0.000
               ->  Index Scan using idx_vulnerability_statistics_on_traversal_ids_and_letter_grade on public.vulnerability_statistics  (cost=0.42..2239.01 rows=1539 width=28) (actual time=5.024..677.059 rows=1349 loops=1)
                     Index Cond: ((vulnerability_statistics.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_statistics.traversal_ids < '{9971}'::bigint[]))
                     Buffers: shared hit=133 read=1162 dirtied=158
                     WAL: records=195 fpi=158 bytes=1048589
                     I/O Timings: read=662.883 write=0.000
Settings: seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5', jit = 'off', effective_cache_size = '472585MB'

Modified query:

postgres.ai have a write lock on sec_db tables for now. I created a copy of the vulnerability_namespace_statistics table, called copy_of_vulnerability_namespace_statistics for the plans below. Here are the commands used to create the copy:

exec CREATE TABLE copy_of_vulnerability_namespace_statistics (LIKE vulnerability_namespace_statistics INCLUDING ALL);
exec INSERT INTO copy_of_vulnerability_namespace_statistics SELECT * FROM vulnerability_namespace_statistics;

Raw SQL

WITH new_values AS (
    WITH namespace_data (
        namespace_id,
        traversal_ids,
        next_traversal_id
) AS (
        VALUES (9970, ARRAY[9970]::bigint[], ARRAY[9971]::bigint[]))
        SELECT
            COALESCE(SUM(vulnerability_statistics.total), 0) AS total,
            COALESCE(SUM(vulnerability_statistics.info), 0) AS info,
            COALESCE(SUM(vulnerability_statistics.unknown), 0) AS unknown,
            COALESCE(SUM(vulnerability_statistics.low), 0) AS low,
            COALESCE(SUM(vulnerability_statistics.medium), 0) AS medium,
            COALESCE(SUM(vulnerability_statistics.high), 0) AS high,
            COALESCE(SUM(vulnerability_statistics.critical), 0) AS critical,
            namespace_data.traversal_ids AS traversal_ids,
            namespace_data.namespace_id AS namespace_id,
            now() AS created_at,
            now() AS updated_at
        FROM
            namespace_data
        LEFT JOIN vulnerability_statistics ON vulnerability_statistics.archived = FALSE
            AND vulnerability_statistics.traversal_ids >= namespace_data.traversal_ids
            AND vulnerability_statistics.traversal_ids < namespace_data.next_traversal_id
    GROUP BY
        namespace_data.traversal_ids,
        namespace_id
),
old_values AS (
    SELECT
        namespace_id,
        traversal_ids,
        total,
        critical,
        high,
        medium,
        low,
        unknown,
        info
    FROM
        copy_of_vulnerability_namespace_statistics
    WHERE
        namespace_id IN (
            SELECT
                namespace_id
            FROM
                new_values)
),
upserted AS (
INSERT INTO copy_of_vulnerability_namespace_statistics (total, info, unknown, low, medium, high, critical, traversal_ids, namespace_id, created_at, updated_at) (
        SELECT
            total,
            info,
            unknown,
            low,
            medium,
            high,
            critical,
            traversal_ids,
            namespace_id,
            created_at,
            updated_at
        FROM
            new_values)
    ON CONFLICT (namespace_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,
            updated_at = EXCLUDED.updated_at,
            traversal_ids = EXCLUDED.traversal_ids
        RETURNING
            namespace_id
),
diff_values AS (
    SELECT
        new_values.namespace_id AS namespace_id,
        new_values.traversal_ids,
        new_values.total - COALESCE(old_values.total, 0) AS total,
        new_values.info - COALESCE(old_values.info, 0) AS info,
    new_values.unknown - COALESCE(old_values.unknown, 0) AS unknown,
    new_values.low - COALESCE(old_values.low, 0) AS low,
    new_values.medium - COALESCE(old_values.medium, 0) AS medium,
    new_values.high - COALESCE(old_values.high, 0) AS high,
    new_values.critical - COALESCE(old_values.critical, 0) AS critical
FROM
    new_values
    LEFT JOIN old_values ON new_values.namespace_id = old_values.namespace_id
    WHERE
        EXISTS (
            SELECT
                1
            FROM
                upserted
            WHERE
                upserted.namespace_id = new_values.namespace_id))
SELECT
    *
FROM
    diff_values
WHERE
    total != 0
    OR info != 0
    OR unknown != 0
    OR low != 0
    OR medium != 0
    OR high != 0
    OR critical != 0

Plan

See full plan here

 Nested Loop Semi Join  (cost=2227.83..2231.00 rows=1 width=92) (actual time=1159.657..1159.666 rows=1 loops=1)
   Buffers: shared hit=146 read=1254 dirtied=19
   WAL: records=19 fpi=17 bytes=129226
   I/O Timings: read=1145.006 write=0.000
   CTE new_values
     ->  Aggregate  (cost=0.42..2227.50 rows=1 width=108) (actual time=1147.552..1147.555 rows=1 loops=1)
           Buffers: shared hit=121 read=1249 dirtied=16
           WAL: records=16 fpi=16 bytes=122136
           I/O Timings: read=1133.245 write=0.000
           ->  Nested Loop Left Join  (cost=0.42..2201.66 rows=1476 width=28) (actual time=6.857..1146.148 rows=1407 loops=1)
                 Buffers: shared hit=121 read=1249 dirtied=16
                 WAL: records=16 fpi=16 bytes=122136
                 I/O Timings: read=1133.245 write=0.000
                 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
                       I/O Timings: read=0.000 write=0.000
                 ->  Index Scan using idx_vulnerability_statistics_on_traversal_ids_and_letter_grade on public.vulnerability_statistics  (cost=0.42..2186.89 rows=1476 width=28) (actual time=6.854..1145.231 rows=1407 loops=1)
                       Index Cond: ((vulnerability_statistics.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_statistics.traversal_ids < '{9971}'::bigint[]))
                       Buffers: shared hit=121 read=1249 dirtied=16
                       WAL: records=16 fpi=16 bytes=122136
                       I/O Timings: read=1133.245 write=0.000
   CTE upserted
     ->  ModifyTable on public.copy_of_vulnerability_namespace_statistics copy_of_vulnerability_namespace_statistics_1  (cost=0.00..0.04 rows=1 width=92) (actual time=11.994..11.996 rows=1 loops=1)
           Buffers: shared hit=19 read=5 dirtied=3
           WAL: records=3 fpi=1 bytes=7090
           I/O Timings: read=11.761 write=0.000
           ->  CTE Scan on new_values new_values_2  (cost=0.00..0.04 rows=1 width=92) (actual time=11.907..11.908 rows=1 loops=1)
                 Buffers: shared hit=9 read=5 dirtied=1
                 WAL: records=1 fpi=0 bytes=99
                 I/O Timings: read=11.761 write=0.000
   ->  Nested Loop Left Join  (cost=0.29..3.40 rows=1 width=120) (actual time=1147.656..1147.662 rows=1 loops=1)
         Filter: (((new_values.total - COALESCE(copy_of_vulnerability_namespace_statistics.total, 0)) <> 0) OR ((new_values.info - COALESCE(copy_of_vulnerability_namespace_statistics.info, 0)) <> 0) OR ((new_values.unknown - COALESCE(copy_of_vulnerability_namespace_statistics.unknown, 0)) <> 0) OR ((new_values.low - COALESCE(copy_of_vulnerability_namespace_statistics.low, 0)) <> 0) OR ((new_values.medium - COALESCE(copy_of_vulnerability_namespace_statistics.medium, 0)) <> 0) OR ((new_values.high - COALESCE(copy_of_vulnerability_namespace_statistics.high, 0)) <> 0) OR ((new_values.critical - COALESCE(copy_of_vulnerability_namespace_statistics.critical, 0)) <> 0))
         Rows Removed by Filter: 0
         Buffers: shared hit=127 read=1249 dirtied=16
         WAL: records=16 fpi=16 bytes=122136
         I/O Timings: read=1133.245 write=0.000
         ->  CTE Scan on new_values  (cost=0.00..0.02 rows=1 width=92) (actual time=1147.558..1147.560 rows=1 loops=1)
               Buffers: shared hit=121 read=1249 dirtied=16
               WAL: records=16 fpi=16 bytes=122136
               I/O Timings: read=1133.245 write=0.000
         ->  Nested Loop Semi Join  (cost=0.29..3.34 rows=1 width=36) (actual time=0.090..0.093 rows=1 loops=1)
               Buffers: shared hit=6
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using copy_of_vulnerability_namespace_statistics_namespace_id_idx on public.copy_of_vulnerability_namespace_statistics  (cost=0.29..3.31 rows=1 width=36) (actual time=0.085..0.087 rows=1 loops=1)
                     Index Cond: (copy_of_vulnerability_namespace_statistics.namespace_id = new_values.namespace_id)
                     Buffers: shared hit=6
                     I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on new_values new_values_1  (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                     I/O Timings: read=0.000 write=0.000
   ->  CTE Scan on upserted  (cost=0.00..0.02 rows=1 width=8) (actual time=11.996..11.996 rows=1 loops=1)
         Buffers: shared hit=19 read=5 dirtied=3
         WAL: records=3 fpi=1 bytes=7090
         I/O Timings: read=11.761 write=0.000
Settings: seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5', jit = 'off', effective_cache_size = '472585MB'