Skip to content

Commit 51773ec

Browse files
dannykoppingdannykopping
committed
Simplify workspace_latest_build view
Signed-off-by: Danny Kopping <dannykopping@gmail.com>
1 parent 412d198 commit 51773ec

File tree

3 files changed

+47
-70
lines changed

3 files changed

+47
-70
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -362,7 +362,7 @@ var (
362362

363363
subjectPrebuildsOrchestrator = rbac.Subject{
364364
FriendlyName: "Prebuilds Orchestrator",
365-
ID: prebuilds.OwnerID.String(),
365+
ID: prebuilds.SystemUserID.String(),
366366
Roles: rbac.Roles([]rbac.Role{
367367
{
368368
Identifier: rbac.RoleIdentifier{Name: "prebuilds-orchestrator"},

‎coderd/database/dump.sql

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

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

Lines changed: 29 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -1,56 +1,38 @@
11
CREATE VIEW workspace_latest_build AS
2-
SELECT wb.*
3-
FROM (SELECT tv.template_id,
4-
wbmax.workspace_id,
5-
MAX(wbmax.build_number) as max_build_number
6-
FROM workspace_builds wbmax
7-
JOIN template_versions tv ON (tv.id = wbmax.template_version_id)
8-
GROUP BY tv.template_id, wbmax.workspace_id) wbmax
9-
JOIN workspace_builds wb ON (
10-
wb.workspace_id = wbmax.workspace_id
11-
AND wb.build_number = wbmax.max_build_number
12-
);
2+
SELECT DISTINCT ON (workspace_id) *
3+
FROM workspace_builds
4+
ORDER BY workspace_id, build_number DESC;
135

146
CREATE VIEW workspace_prebuilds AS
157
WITH
16-
-- All workspaces owned by the "prebuilds" user.
17-
all_prebuilds AS (SELECT w.*
18-
FROM workspaces w
19-
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'), -- The system user responsible for prebuilds.
20-
-- All workspace agents belonging to the workspaces owned by the "prebuilds" user.
21-
workspace_agents AS (SELECT w.id AS workspace_id, wa.id AS agent_id, wa.lifecycle_state, wa.ready_at
22-
FROM workspaces w
23-
INNER JOIN workspace_latest_build wlb ON wlb.workspace_id = w.id
24-
INNER JOIN workspace_resources wr ON wr.job_id = wlb.job_id
25-
INNER JOIN workspace_agents wa ON wa.resource_id = wr.id
26-
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0' -- The system user responsible for prebuilds.
27-
GROUP BY w.id, wa.id),
28-
-- We can't rely on the template_version_preset_id in the workspace_builds table because this value is only set on the
29-
-- initial workspace creation. Subsequent stop/start transitions will not have a value for template_version_preset_id,
30-
-- and therefore we can't rely on (say) the latest build's chosen template_version_preset_id.
31-
--
32-
-- See https://github.com/coder/internal/issues/398
33-
current_presets AS (SELECT w.id AS prebuild_id, lps.template_version_preset_id
34-
FROM workspaces w
35-
INNER JOIN (
36-
-- The latest workspace build which had a preset explicitly selected
37-
SELECT wb.*
38-
FROM (SELECT tv.template_id,
39-
wbmax.workspace_id,
40-
MAX(wbmax.build_number) as max_build_number
41-
FROM workspace_builds wbmax
42-
JOIN template_versions tv ON (tv.id = wbmax.template_version_id)
43-
WHERE wbmax.template_version_preset_id IS NOT NULL
44-
GROUP BY tv.template_id, wbmax.workspace_id) wbmax
45-
JOIN workspace_builds wb ON (
46-
wb.workspace_id = wbmax.workspace_id
47-
AND wb.build_number = wbmax.max_build_number
48-
)) lps ON lps.workspace_id = w.id
49-
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0') -- The system user responsible for prebuilds.
8+
-- All workspaces owned by the "prebuilds" user.
9+
all_prebuilds AS (SELECT w.*
10+
FROM workspaces w
11+
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'), -- The system user responsible for prebuilds.
12+
-- We can't rely on the template_version_preset_id in the workspace_builds table because this value is only set on the
13+
-- initial workspace creation. Subsequent stop/start transitions will not have a value for template_version_preset_id,
14+
-- and therefore we can't rely on (say) the latest build's chosen template_version_preset_id.
15+
--
16+
-- See https://github.com/coder/internal/issues/398
17+
latest_prebuild_builds AS (SELECT *
18+
FROM workspace_latest_build
19+
WHERE template_version_preset_id IS NOT NULL),
20+
-- All workspace agents belonging to the workspaces owned by the "prebuilds" user.
21+
workspace_agents AS (SELECT w.id AS workspace_id, wa.id AS agent_id, wa.lifecycle_state, wa.ready_at
22+
FROM workspaces w
23+
INNER JOIN workspace_latest_build wlb ON wlb.workspace_id = w.id
24+
INNER JOIN workspace_resources wr ON wr.job_id = wlb.job_id
25+
INNER JOIN workspace_agents wa ON wa.resource_id = wr.id
26+
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0' -- The system user responsible for prebuilds.
27+
GROUP BY w.id, wa.id),
28+
current_presets AS (SELECT w.id AS prebuild_id, lpb.template_version_preset_id
29+
FROM workspaces w
30+
INNER JOIN latest_prebuild_builds lpb ON lpb.workspace_id = w.id
31+
WHERE w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0') -- The system user responsible for prebuilds.
5032
SELECT p.*, a.agent_id, a.lifecycle_state, a.ready_at, cp.template_version_preset_id AS current_preset_id
5133
FROM all_prebuilds p
52-
LEFT JOIN workspace_agents a ON a.workspace_id = p.id
53-
INNER JOIN current_presets cp ON cp.prebuild_id = p.id;
34+
LEFT JOIN workspace_agents a ON a.workspace_id = p.id
35+
INNER JOIN current_presets cp ON cp.prebuild_id = p.id;
5436

5537
CREATE VIEW workspace_prebuild_builds AS
5638
SELECT *

0 commit comments

Comments
 (0)