8000 refactor: join wlb with pj · coder/coder@f870d7e · GitHub
[go: up one dir, main page]
Skip to content

Commit f870d7e

Browse files
evgeniy-scherbinaevgeniy-scherbina
committed
refactor: join wlb with pj
1 parent 8d9cd45 commit f870d7e

File tree

5 files changed

+39
-35
lines changed

5 files changed

+39
-35
lines changed

‎coderd/database/dump.sql

Lines changed: 11 additions & 9 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000312_prebuilds.up.sql

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,17 @@
11
-- workspace_latest_builds contains latest build for every workspace
22
CREATE VIEW workspace_latest_builds AS
3-
SELECT DISTINCT ON (workspace_id) id, workspace_id, template_version_id, job_id, template_version_preset_id, transition, created_at
4-
FROM workspace_builds
5-
ORDER BY workspace_id, build_number DESC;
3+
SELECT DISTINCT ON (workspace_id)
4+
wb.id,
5+
wb.workspace_id,
6+
wb.template_version_id,
7+
wb.job_id,
8+
wb.template_version_preset_id,
9+
wb.transition,
10+
wb.created_at,
11+
pj.job_status
12+
FROM workspace_builds wb
13+
INNER JOIN provisioner_jobs pj ON wb.job_id = pj.id
14+
ORDER BY wb.workspace_id, wb.build_number DESC;
615

716
-- workspace_prebuilds contains all prebuilt workspaces with corresponding agent information
817
-- (including lifecycle_state which indicates is agent ready or not) and corresponding preset_id for prebuild

‎coderd/database/models.go

Lines changed: 8 additions & 7 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 4 additions & 8 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/prebuilds.sql

Lines changed: 4 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -31,17 +31,15 @@ SELECT p.id AS workspace_id,
3131
p.created_at
3232
FROM workspace_prebuilds p
3333
INNER JOIN workspace_latest_builds b ON b.workspace_id = p.id
34-
INNER JOIN provisioner_jobs pj ON b.job_id = pj.id -- See https://github.com/coder/internal/issues/398.
3534
WHERE (b.transition = 'start'::workspace_transition
36-
AND pj.job_status = 'succeeded'::provisioner_job_status);
35+
AND b.job_status = 'succeeded'::provisioner_job_status);
3736

3837
-- name: GetPrebuildsInProgress :many
3938
SELECT t.id AS template_id, wpb.template_version_id, wpb.transition, COUNT(wpb.transition)::int AS count
4039
FROM workspace_latest_builds wlb
41-
INNER JOIN provisioner_jobs pj ON wlb.job_id = pj.id
4240
INNER JOIN workspace_prebuild_builds wpb ON wpb.id = wlb.id
4341
INNER JOIN templates t ON t.active_version_id = wlb.template_version_id
44-
WHERE pj.job_status IN ('pending'::provisioner_job_status, 'running'::provisioner_job_status)
42+
WHERE wlb.job_status IN ('pending'::provisioner_job_status, 'running'::provisioner_job_status)
4543
GROUP BY t.id, wpb.template_version_id, wpb.transition;
4644

4745
-- GetPresetsBackoff groups workspace builds by template version ID.
@@ -60,10 +58,9 @@ GROUP BY t.id, wpb.template_version_id, wpb.transition;
6058
-- name: GetPresetsBackoff :many
6159
WITH filtered_builds AS (
6260
-- Only select builds which are for prebuild creations
63-
SELECT wlb.template_version_id, wlb.created_at, tvp.id AS preset_id, pj.job_status, tvp.desired_instances
61+
SELECT wlb.template_version_id, wlb.created_at, tvp.id AS preset_id, wlb.job_status, tvp.desired_instances
6462
FROM template_version_presets tvp
6563
INNER JOIN workspace_latest_builds wlb ON wlb.template_version_preset_id = tvp.id
66-
INNER JOIN provisioner_jobs pj ON wlb.job_id = pj.id
6764
INNER JOIN template_versions tv ON wlb.template_version_id = tv.id
6865
INNER JOIN templates t ON tv.template_id = t.id AND t.active_version_id = tv.id
6966
WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a prebuild configuration.
@@ -103,10 +100,9 @@ WHERE w.id IN (
103100
SELECT p.id
104101
FROM workspace_prebuilds p
105102
INNER JOIN workspace_latest_builds b ON b.workspace_id = p.id
106-
INNER JOIN provisioner_jobs pj ON b.job_id = pj.id
107103
INNER JOIN templates t ON p.template_id = t.id
108104
WHERE (b.transition = 'start'::workspace_transition
109-
AND pj.job_status IN ('succeeded'::provisioner_job_status))
105+
AND b.job_status IN ('succeeded'::provisioner_job_status))
110106
-- The prebuilds system should never try to claim a prebuild for an inactive template version.
111107
-- Nevertheless, this filter is here as a defensive measure:
112108
AND b.template_version_id = t.active_version_id

0 commit comments

Comments
 (0)
0