Skip to content

Commit 106e8ca

Browse files
mafredrimafredri
committed
fix prebuilds view
1 parent 7d73585 commit 106e8ca

File tree

3 files changed

+88
-1
lines changed

3 files changed

+88
-1
lines changed

‎coderd/database/dump.sql

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000338_use_deleted_boolean_for_subagents.down.sql

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,3 +50,46 @@ COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS
5050
'Use a trigger instead of a unique constraint because existing data may violate
5151
the uniqueness requirement. A trigger allows us to enforce uniqueness going
5252
forward without requiring a migration to clean up historical data.';
53+
54+
-- Restore prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql.
55+
DROP VIEW workspace_prebuilds;
56+
57+
CREATE VIEW workspace_prebuilds AS
58+
WITH all_prebuilds AS (
59+
SELECT w.id,
60+
w.name,
61+
w.template_id,
62+
w.created_at
63+
FROM workspaces w
64+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
65+
), workspaces_with_latest_presets AS (
66+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
67+
workspace_builds.template_version_preset_id
68+
FROM workspace_builds
69+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
70+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
71+
), workspaces_with_agents_status AS (
72+
SELECT w.id AS workspace_id,
73+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
74+
FROM (((workspaces w
75+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
76+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
77+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
78+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
79+
GROUP BY w.id
80+
), current_presets AS (
81+
SELECT w.id AS prebuild_id,
82+
wlp.template_version_preset_id
83+
FROM (workspaces w
84+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
85+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
86+
)
87+
SELECT p.id,
88+
p.name,
89+
p.template_id,
90+
p.created_at,
91+
COALESCE(a.ready, false) AS ready,
92+
cp.template_version_preset_id AS current_preset_id
93+
FROM ((all_prebuilds p
94+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
95+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

‎coderd/database/migrations/000338_use_deleted_boolean_for_subagents.up.sql

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,3 +53,47 @@ COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS
5353
'Use a trigger instead of a unique constraint because existing data may violate
5454
the uniqueness requirement. A trigger allows us to enforce uniqueness going
5555
forward without requiring a migration to clean up historical data.';
56+
57+
-- Handle agent deletion in prebuilds, previously modified in 000323_workspace_latest_builds_optimization.up.sql.
58+
DROP VIEW workspace_prebuilds;
59+
60+
CREATE VIEW workspace_prebuilds AS
61+
WITH all_prebuilds AS (
62+
SELECT w.id,
63+
w.name,
64+
w.template_id,
65+
w.created_at
66+
FROM workspaces w
67+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
68+
), workspaces_with_latest_presets AS (
69+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
70+
workspace_builds.template_version_preset_id
71+
FROM workspace_builds
72+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
73+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
74+
), workspaces_with_agents_status AS (
75+
SELECT w.id AS workspace_id,
76+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
77+
FROM (((workspaces w
78+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
79+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
80+
-- ADD: deleted check for sub agents.
81+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id AND wa.deleted = FALSE)))
82+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
83+
GROUP BY w.id
84+
), current_presets AS (
85+
SELECT w.id AS prebuild_id,
86+
wlp.template_version_preset_id
87+
FROM (workspaces w
88+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
89+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
90+
)
91+
SELECT p.id,
92+
p.name,
93+
p.template_id,
94+
p.created_at,
95+
COALESCE(a.ready, false) AS ready,
96+
cp.template_version_preset_id AS current_preset_id
97+
FROM ((all_prebuilds p
98+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
99+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

0 commit comments

Comments
 (0)