Remove cascading delete of scan_result_policy_reads
What does this MR do and why?
Addresses #404080 (closed)
This MR fixes a bug in Security::ProcessScanResultPolicyWorker
because of cascading delete of ScanResultPolicyRead
. The problem was, we delete all ScanResultPolicyReads associated to a security_orchestration_policy_configuration
which leads to deletion of ApprovalMergeRequestRule
and ApprovalProjectRule
of other projects within the group too.
We create ScanResultPolicyRead
either if scan_result_role_action
feature flag is enabled or the rule is of license_finding
type. After we enabled the scan_result_role_action
feature flag globally (internal link), we started creating ScanResultPolicyRead
for all both type of scan result policies. When Security::ProcessScanResultPolicyWorker
is executed for the project (with group level policy project), it deletes all ScanResultPolicyRead
which results in this bug.
This MR removes the deletion of ScanResultPolicyRead
and deletes the software_license_policies
associated to the project.
This change will create a lot of duplicate ScanResultPolicyRead
, but we will tackle this as part of Add unique index to ScanResultPolicyRead (#404073 - closed) in which we will create unique index and prepare migration to remove the stale rows.
Database queries
Limit Query
SELECT
"software_license_policies"."id"
FROM
"software_license_policies"
WHERE
"software_license_policies"."project_id" = 10501328
AND "software_license_policies"."scan_result_policy_id" IN (
173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612
)
ORDER BY
"software_license_policies"."id" ASC,
"software_license_policies"."updated_at" ASC LIMIT 1
Limit (cost=50.74..50.74 rows=1 width=12) (actual time=24.110..24.113 rows=1 loops=1)
Buffers: shared hit=8 read=5
I/O Timings: read=23.928 write=0.000
-> Sort (cost=50.74..50.74 rows=2 width=12) (actual time=24.109..24.110 rows=1 loops=1)
Sort Key: software_license_policies.id, software_license_policies.updated_at
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=8 read=5
I/O Timings: read=23.928 write=0.000
-> Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy on public.software_license_policies (cost=0.29..50.73 rows=2 width=12) (actual time=16.199..24.039 rows=38 loops=1)
Index Cond: (software_license_policies.project_id = 10501328)
Filter: (software_license_policies.scan_result_policy_id = ANY ('{173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612}'::bigint[]))
Rows Removed by Filter: 0
Buffers: shared hit=2 read=5
I/O Timings: read=23.928 write=0.000
Delete Query
DELETE
FROM
"software_license_policies"
WHERE
"software_license_policies"."project_id" = 10501328
AND "software_license_policies"."scan_result_policy_id" IN (
173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612
)
AND "software_license_policies"."id" >= 96802
ModifyTable on public.software_license_policies (cost=35.71..41.24 rows=1 width=6) (actual time=7.129..7.132 rows=0 loops=1)
Buffers: shared hit=43 read=9 dirtied=4
I/O Timings: read=6.712 write=0.000
-> Bitmap Heap Scan on public.software_license_policies (cost=35.71..41.24 rows=1 width=6) (actual time=6.357..6.381 rows=38 loops=1)
Filter: (software_license_policies.scan_result_policy_id = ANY ('{173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612}'::bigint[]))
Rows Removed by Filter: 0
Buffers: shared hit=5 read=8
I/O Timings: read=6.113 write=0.000
-> BitmapAnd (cost=35.71..35.71 rows=3 width=0) (actual time=6.338..6.340 rows=0 loops=1)
Buffers: shared hit=2 read=8
I/O Timings: read=6.113 write=0.000
-> Bitmap Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy (cost=0.00..2.06 rows=36 width=0) (actual time=0.028..0.028 rows=38 loops=1)
Index Cond: (software_license_policies.project_id = 10501328)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using software_license_policies_pkey (cost=0.00..33.40 rows=2015 width=0) (actual time=6.303..6.304 rows=2128 loops=1)
Index Cond: (software_license_policies.id >= 96802)
Buffers: shared read=8
I/O Timings: read=6.113 write=0.000
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.