Fix blocking issues count cache refresh
What does this MR do?
Fixes blocking issues count cache refresh.
!43870 (merged) introduced a change for when reopening any issue it refreshes its blocking issues count. Some situations are still not covered by this:
- When reopening a issue that is blocked we should update its blocking source issue(s)
blocking_issues_count + 1. - When closing a blocked issue we should update its blocking source issue(s)
blocking_issues_count - 1.
To be more consistent instead of adding more logic to Issue::CloseService and Issue::ReopenService i moved the cache refresh into state machine callback, refreshing the cache of blocking and blocked issues with one single query.
Query to update issues
UPDATE issues
SET blocking_issues_count = grouped_counts.count
FROM (SELECT blocking_issue_id,
Sum(count) AS count
FROM ((SELECT Count(CASE
WHEN issues.state_id = 1 THEN 1
ELSE NULL
END),
issue_links.source_id AS blocking_issue_id
FROM "issue_links"
INNER JOIN "issues"
ON "issues"."id" = "issue_links"."target_id"
WHERE "issue_links"."link_type" = 1
AND "issue_links"."source_id" IN (1220746, 2317363, 2803186, 2803186, 3126241)
GROUP BY "blocking_issue_id")
UNION ALL
(SELECT Count(CASE
WHEN issues.state_id = 1 THEN 1
ELSE NULL
END),
issue_links.target_id AS blocking_issue_id
FROM "issue_links"
INNER JOIN "issues"
ON "issues"."id" = "issue_links"."source_id"
WHERE "issue_links"."link_type" = 2
AND "issue_links"."target_id" IN (1220746, 2317363, 2803186, 2803186, 3126241)
GROUP BY "blocking_issue_id")) issue_links
GROUP BY blocking_issue_id) AS grouped_counts
WHERE issues.id = grouped_counts.blocking_issue_id
Query Plan with temp indexes
---------------------------------------------------------------------------------------------------------------------------------------------
------------------
Update on issues (cost=2.28..28.27 rows=2 width=1359)
-> Nested Loop (cost=2.28..28.27 rows=2 width=1359)
-> Subquery Scan on grouped_counts (cost=1.72..21.10 rows=2 width=96)
-> GroupAggregate (cost=1.72..21.08 rows=2 width=36)
Group Key: "*SELECT* 1".blocking_issue_id
-> Merge Append (cost=1.72..21.04 rows=2 width=12)
Sort Key: "*SELECT* 1".blocking_issue_id
-> Subquery Scan on "*SELECT* 1" (cost=0.85..10.51 rows=1 width=12)
-> GroupAggregate (cost=0.85..10.50 rows=1 width=12)
Group Key: issue_links.source_id
-> Nested Loop (cost=0.85..10.49 rows=1 width=6)
-> Index Scan using tmp_idx_blocking_type_links on issue_links (cost=0.29..6.90 rows=1 width=8
)
Index Cond: (source_id = ANY ('{3,1,2}'::integer[]))
-> Index Scan using issues_pkey on issues issues_1 (cost=0.56..3.58 rows=1 width=6)
Index Cond: (id = issue_links.target_id)
-> Subquery Scan on "*SELECT* 2" (cost=0.85..10.50 rows=1 width=12)
-> GroupAggregate (cost=0.85..10.49 rows=1 width=12)
Group Key: issue_links_1.target_id
-> Nested Loop (cost=0.85..10.47 rows=1 width=6)
-> Index Scan using tmp_idx_blocked_by_type_links on issue_links issue_links_1 (cost=0.29..6.8
9 rows=1 width=8)
Index Cond: (target_id = ANY ('{3,1,2}'::integer[]))
-> Index Scan using issues_pkey on issues issues_2 (cost=0.56..3.58 rows=1 width=6)
Index Cond: (id = issue_links_1.source_id)
-> Index Scan using issues_pkey on issues (cost=0.56..3.58 rows=1 width=1267)
Index Cond: (id = grouped_counts.blocking_issue_id)
Query Plan without temp indexes
ModifyTable on public.issues (cost=2.55..57.61 rows=2 width=1361) (actual time=4.451..4.457 rows=0 loops=1)
Buffers: shared hit=259 read=9 dirtied=5
I/O Timings: read=1.587
-> Nested Loop (cost=2.55..57.61 rows=2 width=1361) (actual time=0.151..0.250 rows=4 loops=1)
Buffers: shared hit=87
-> Subquery Scan on grouped_counts (cost=1.99..50.44 rows=2 width=96) (actual time=0.121..0.174 rows=4 loops=1)
Buffers: shared hit=67
-> Aggregate (cost=1.99..50.42 rows=2 width=36) (actual time=0.116..0.165 rows=4 loops=1)
Group Key: "SELECT 1".blocking_issue_id
Buffers: shared hit=67
-> Merge Append (cost=1.99..50.39 rows=2 width=12) (actual time=0.096..0.150 rows=4 loops=1)
Sort Key: "SELECT 1".blocking_issue_id
Buffers: shared hit=67
-> Subquery Scan on SELECT 1 (cost=0.99..25.16 rows=1 width=12) (actual time=0.060..0.110 rows=4 loops=1)
Buffers: shared hit=47
-> Aggregate (cost=0.99..25.15 rows=1 width=12) (actual time=0.060..0.108 rows=4 loops=1)
Group Key: issue_links.source_id
Buffers: shared hit=47
-> Nested Loop (cost=0.99..25.14 rows=1 width=6) (actual time=0.042..0.097 rows=5 loops=1)
Buffers: shared hit=47
-> Index Scan using index_issue_links_on_source_id on public.issue_links (cost=0.42..21.55 rows=1 width=8) (actual time=0.025..0.049 rows=5 loops=1)
Index Cond: (issue_links.source_id = ANY ('{1220746,2317363,2803186,2803186,3126241}'::integer[]))
Filter: (issue_links.link_type = 1)
Rows Removed by Filter: 6
Buffers: shared hit=22
-> Index Scan using issues_pkey on public.issues issues_1 (cost=0.56..3.58 rows=1 width=6) (actual time=0.008..0.008 rows=1 loops=5)
Index Cond: (issues_1.id = issue_links.target_id)
Buffers: shared hit=25
-> Subquery Scan on SELECT 2 (cost=0.99..25.19 rows=1 width=12) (actual time=0.035..0.036 rows=0 loops=1)
Buffers: shared hit=20
-> Aggregate (cost=0.99..25.18 rows=1 width=12) (actual time=0.034..0.035 rows=0 loops=1)
Group Key: issue_links_1.target_id
Buffers: shared hit=20
-> Nested Loop (cost=0.99..25.16 rows=1 width=6) (actual time=0.034..0.035 rows=0 loops=1)
Buffers: shared hit=20
-> Index Scan using index_issue_links_on_target_id on public.issue_links issue_links_1 (cost=0.42..21.58 rows=1 width=8) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: (issue_links_1.target_id = ANY ('{1220746,2317363,2803186,2803186,3126241}'::integer[]))
Filter: (issue_links_1.link_type = 2)
Rows Removed by Filter: 8
Buffers: shared hit=20
-> Index Scan using issues_pkey on public.issues issues_2 (cost=0.56..3.58 rows=1 width=6) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (issues_2.id = issue_links_1.source_id)
-> Index Scan using issues_pkey on public.issues (cost=0.56..3.58 rows=1 width=1269) (actual time=0.015..0.015 rows=1 loops=4)
Index Cond: (issues.id = grouped_counts.blocking_issue_id)