Fix incorrect `has_issues` on `vulnerability_reads`

What does this MR do and why?

Describe in detail what your merge request does and why.

!112233 (merged) fixed a bug where vulnerability_reads could be created with an incorrect has_issues value if there were already issues links prior to creation. This MR adds a background migration which fixes the data for existing records. It does this by iterating through all vulnerability_issue_links records and updating the respective vulnerability_reads record if it has has_issues set to false.

Relates to: #393670 (closed)

💾 Database

Queries

Fetch sub_batch: https://console.postgres.ai/shared/b74ddd4c-2c27-4577-a291-1e32ad759643

SELECT
    vulnerability_issue_links.vulnerability_id
FROM
    vulnerability_issue_links
WHERE
    vulnerability_issue_links.vulnerability_id BETWEEN 60006000 AND 60007000 AND
    vulnerability_issue_links.vulnerability_id >= 60006000 AND
    vulnerability_issue_links.vulnerability_id < 600005201;

 Index Only Scan using idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id on public.vulnerability_issue_links  (cost=0.42..2.00 rows=3 width=8) (actual time=0.155..0.156 rows=0 loops=1)
   Index Cond: ((vulnerability_issue_links.vulnerability_id >= 60006000) AND (vulnerability_issue_links.vulnerability_id <= 60007000) AND (vulnerability_issue_links.vulnerability_id >= 60006000) AND (vulnerability_issue_links.vulnerability_id < 600005201))
   Heap Fetches: 0
   Buffers: shared hit=16 read=2
   I/O Timings: read=0.091 write=0.000

Update vulnerability reads: https://console.postgres.ai/shared/cc338fca-d9fe-427b-bf3e-df14b4902404

UPDATE vulnerability_reads
SET
    has_issues = true
WHERE
    vulnerability_reads.vulnerability_id IN (
    SELECT
        vulnerability_issue_links.vulnerability_id
    FROM
        vulnerability_issue_links
    WHERE
        vulnerability_issue_links.vulnerability_id BETWEEN 60006000 AND 60007000 AND
        vulnerability_issue_links.vulnerability_id >= 60006000 AND
        vulnerability_issue_links.vulnerability_id < 600005201
);

 ModifyTable on public.vulnerability_reads  (cost=6.89..17.11 rows=3 width=174) (actual time=0.918..0.920 rows=0 loops=1)
   Buffers: shared hit=16 read=2
   I/O Timings: read=0.843 write=0.000
   ->  Nested Loop  (cost=6.89..17.11 rows=3 width=174) (actual time=0.916..0.917 rows=0 loops=1)
         Buffers: shared hit=16 read=2
         I/O Timings: read=0.843 write=0.000
         ->  HashAggregate  (cost=6.33..6.36 rows=3 width=14) (actual time=0.915..0.916 rows=0 loops=1)
               Group Key: vulnerability_issue_links.vulnerability_id
               Buffers: shared hit=16 read=2
               I/O Timings: read=0.843 write=0.000
               ->  Index Scan using idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id on public.vulnerability_issue_links  (cost=0.42..6.32 rows=3 width=14) (actual time=0.914..0.914 rows=0 loops=1)
                     Index Cond: ((vulnerability_issue_links.vulnerability_id >= 60006000) AND (vulnerability_issue_links.vulnerability_id <= 60007000) AND (vulnerability_issue_links.vulnerability_id >= 60006000) AND (vulnerability_issue_links.vulnerability_id < 600005201))
                     Buffers: shared hit=16 read=2
                     I/O Timings: read=0.843 write=0.000
         ->  Index Scan using index_vulnerability_reads_on_vulnerability_id on public.vulnerability_reads  (cost=0.56..3.58 rows=1 width=167) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (vulnerability_reads.vulnerability_id = vulnerability_issue_links.vulnerability_id)
               I/O Timings: read=0.000 write=0.000

Migrations

$ scripts/db_tasks db:migrate:down VERSION=20230302185739
Running: `bundle exec rake db:migrate:down:main VERSION=20230302185739`
main: == 20230302185739 QueueFixVulnerabilityReadsHasIssues: reverting ==============
main: == 20230302185739 QueueFixVulnerabilityReadsHasIssues: reverted (0.0496s) =====
$ scripts/db_tasks db:migrate
Running: `bundle exec rake db:migrate:main`
main: == 20230302185739 QueueFixVulnerabilityReadsHasIssues: migrating ==============
main: == 20230302185739 QueueFixVulnerabilityReadsHasIssues: migrated (0.1733s) =====

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.