Post pipeline analyzers status worker

What does this MR do and why?

Adds a post pipeline async worker that updates project-level analyzers statuses in the analyzer_project_statuses table based on the relevant job status.

Relevant issue

Update scan processing to update new project le... (#524197 - closed) • Gal Katz • 18.0

Query plans

upsert_analyzer_statuses

Raw SQL

INSERT INTO "analyzer_project_statuses" ("project_id", "traversal_ids", "analyzer_type", "status", "last_call", "created_at", "updated_at")
    VALUES (66101996, '{9970,96981785,97058478,98867842,100569068}', 0, 1, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (66101996, '{9970,96981785,97058478,98867842,100569068}', 4, 2, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (66101996, '{9970,96981785,97058478,98867842,100569068}', 5, 2, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (66101996, '{9970,96981785,97058478,98867842,100569068}', 6, 1, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (66101996, '{9970,96981785,97058478,98867842,100569068}', 2, 1, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    (66101996, '{9970,96981785,97058478,98867842,100569068}', 1, 1, '2025-04-06 08:51:29', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT ("project_id", "analyzer_type")
    DO UPDATE SET
        updated_at = (
            CASE WHEN ("analyzer_project_statuses"."traversal_ids" IS NOT DISTINCT FROM excluded."traversal_ids"
                AND "analyzer_project_statuses"."status" IS NOT DISTINCT FROM excluded."status"
                AND "analyzer_project_statuses"."last_call" IS NOT DISTINCT FROM excluded."last_call") THEN
                "analyzer_project_statuses".updated_at
            ELSE
                CURRENT_TIMESTAMP
            END),
        "traversal_ids" = excluded."traversal_ids",
        "status" = excluded."status",
        "last_call" = excluded."last_call"
    RETURNING
        "id"

Query plan

Full details here

 ModifyTable on public.analyzer_project_statuses  (cost=0.00..0.09 rows=6 width=76) (actual time=3.271..3.455 rows=6 loops=1)
   Buffers: shared hit=105 read=9 dirtied=8 written=4
   WAL: records=35 fpi=0 bytes=3320
   I/O Timings: read=2.815 write=0.075
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.09 rows=6 width=76) (actual time=2.865..2.883 rows=6 loops=1)
         Buffers: shared hit=19 read=5 dirtied=1
         WAL: records=2 fpi=0 bytes=242
         I/O Timings: read=2.714 write=0.000
Settings: seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5'

And:

Raw SQL

EXPLAIN UPDATE
    "analyzer_project_statuses"
SET
    "status" = 0
WHERE
    "analyzer_project_statuses"."project_id" = 66101996
    AND "analyzer_project_statuses"."analyzer_type" NOT IN (0, 4, 5, 6, 2, 1)

Query plan

Full details here

 ModifyTable on public.analyzer_project_statuses  (cost=0.14..3.17 rows=0 width=0) (actual time=0.036..0.036 rows=0 loops=1)
   Buffers: shared hit=6
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_analyzer_project_statuses_status on public.analyzer_project_statuses  (cost=0.14..3.17 rows=1 width=8) (actual time=0.035..0.035 rows=0 loops=1)
         Index Cond: (analyzer_project_statuses.project_id = 66101996)
         Filter: (analyzer_project_statuses.analyzer_type <> ALL ('{0,4,5,6,2,1}'::integer[]))
         Rows Removed by Filter: 0
         Buffers: shared hit=6
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off'