Add project to attributes update service

What does this MR do and why?

Add project to attributes update service that attaches and detaches projects with attributes, while respecting the category multiple_selection value.

Changelog: added
EE: true

Query plans

upsert_all

Raw SQL

INSERT INTO "project_to_security_attributes" ("project_id", "security_attribute_id", "traversal_ids", "created_at", "updated_at")
    VALUES (71329491, 1, '{9970,96981785,97058478,98867842,100569068}', '2025-08-31 15:24:21.809314', '2025-08-31 15:24:21.809314'),
    (71329491, 2, '{9970,96981785,97058478,98867842,100569068}', '2025-08-31 15:24:21.809314', '2025-08-31 15:24:21.809314')
ON CONFLICT ("id")
    DO UPDATE SET
        "project_id" = excluded."project_id",
        "security_attribute_id" = excluded."security_attribute_id",
        "traversal_ids" = excluded."traversal_ids",
        "created_at" = excluded."created_at",
        "updated_at" = excluded."updated_at"
    RETURNING
        "id"

Plan

See full plan here.

 ModifyTable on public.project_to_security_attributes  (cost=0.00..0.03 rows=2 width=72) (actual time=0.991..1.119 rows=2 loops=1)
   Buffers: shared hit=79 read=5 dirtied=10 written=5
   WAL: records=17 fpi=0 bytes=1507
   I/O Timings: read=0.192 write=0.138
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=72) (actual time=0.216..0.224 rows=2 loops=1)
         Buffers: shared hit=15 read=1 dirtied=1
         WAL: records=1 fpi=0 bytes=99
         I/O Timings: read=0.088 write=0.000
Trigger RI_ConstraintTrigger_c_3639295303 for constraint fk_rails_5fe496b3fb: time=2.254 calls=2
Settings: seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5', effective_cache_size = '338688MB', jit = 'off'

---

delete_all - previous version

Raw SQL

DELETE FROM "project_to_security_attributes"
WHERE "project_to_security_attributes"."project_id" = 71329491
    AND "project_to_security_attributes"."id" IN (1, 2, 3, 4)

Plan

See full plan here.

 ModifyTable on public.project_to_security_attributes  (cost=0.14..3.17 rows=0 width=0) (actual time=0.056..0.057 rows=0 loops=1)
   Buffers: shared hit=7
   WAL: records=2 fpi=0 bytes=108
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_project_security_attributes_project_id_unique on public.project_to_security_attributes  (cost=0.14..3.17 rows=1 width=6) (actual time=0.028..0.029 rows=2 loops=1)
         Index Cond: (project_to_security_attributes.project_id = 71329491)
         Filter: (project_to_security_attributes.id = ANY ('{1,2,3,4}'::bigint[]))
         Rows Removed by Filter: 0
         Buffers: shared hit=5
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5'

delete_all - new version

Raw SQL

DELETE FROM "project_to_security_attributes"
WHERE "project_to_security_attributes"."id" IN (1, 2, 3, 4)

Plan

See full plan here.

 ModifyTable on public.project_to_security_attributes  (cost=0.00..0.00 rows=0 width=0) (actual time=0.005..0.005 rows=0 loops=1)
   I/O Timings: read=0.000 write=0.000
   ->  Seq Scan on public.project_to_security_attributes  (cost=0.00..0.00 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (project_to_security_attributes.id = ANY ('{1,2,3,4}'::bigint[]))
         Rows Removed by Filter: 0
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

category_multiple_selection_constraint

Raw SQL

SELECT
    1 AS one
FROM
    "security_attributes"
    INNER JOIN "project_to_security_attributes" ON "security_attributes"."id" = "project_to_security_attributes"."security_attribute_id"
WHERE
    "project_to_security_attributes"."project_id" = 68794259
    AND "security_attributes"."security_category_id" = 1
    AND "security_attributes"."id" != 1
LIMIT 1

Plan

See full plan here.

 Limit  (cost=0.00..0.01 rows=1 width=4) (actual time=0.006..0.007 rows=0 loops=1)
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.00..0.01 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
         I/O Timings: read=0.000 write=0.000
         ->  Seq Scan on public.security_attributes  (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
               Filter: ((security_attributes.id <> 1) AND (security_attributes.security_category_id = 1))
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000
         ->  Seq Scan on public.project_to_security_attributes  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
               Filter: (project_to_security_attributes.project_id = 68794259)
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', random_page_cost = '1.5', work_mem = '100MB'

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Relates to Add attributes attach service (#566385 - closed) • Gal Katz • 18.4