mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
e21a301682
Relates to https://github.com/coder/coder/issues/15082 Further to https://github.com/coder/coder/pull/15429, this reduces the amount of false-positives returned by the 'is eligible for autostart' part of the query. We achieve this by calculating the 'next start at' time of the workspace, storing it in the database, and using it in our `GetWorkspacesEligibleForTransition` query. The prior implementation of the 'is eligible for autostart' query would return _all_ workspaces that at some point in the future _might_ be eligible for autostart. This now ensures we only return workspaces that _should_ be eligible for autostart. We also now pass `currentTick` instead of `t` to the `GetWorkspacesEligibleForTransition` query as otherwise we'll have one round of workspaces that are skipped by `isEligibleForTransition` due to `currentTick` being a truncated version of `t`.
47 lines
1.4 KiB
SQL
47 lines
1.4 KiB
SQL
DROP VIEW workspaces_expanded;
|
|
|
|
DROP TRIGGER IF EXISTS trigger_nullify_next_start_at_on_template_autostart_modification ON templates;
|
|
DROP FUNCTION IF EXISTS nullify_next_start_at_on_template_autostart_modification;
|
|
|
|
DROP TRIGGER IF EXISTS trigger_nullify_next_start_at_on_workspace_autostart_modification ON workspaces;
|
|
DROP FUNCTION IF EXISTS nullify_next_start_at_on_workspace_autostart_modification;
|
|
|
|
DROP INDEX workspace_template_id_idx;
|
|
DROP INDEX workspace_next_start_at_idx;
|
|
|
|
ALTER TABLE ONLY workspaces DROP COLUMN IF EXISTS next_start_at;
|
|
|
|
CREATE VIEW
|
|
workspaces_expanded
|
|
AS
|
|
SELECT
|
|
workspaces.*,
|
|
-- Owner
|
|
visible_users.avatar_url AS owner_avatar_url,
|
|
visible_users.username AS owner_username,
|
|
-- Organization
|
|
organizations.name AS organization_name,
|
|
organizations.display_name AS organization_display_name,
|
|
organizations.icon AS organization_icon,
|
|
organizations.description AS organization_description,
|
|
-- Template
|
|
templates.name AS template_name,
|
|
templates.display_name AS template_display_name,
|
|
templates.icon AS template_icon,
|
|
templates.description AS template_description
|
|
FROM
|
|
workspaces
|
|
INNER JOIN
|
|
visible_users
|
|
ON
|
|
workspaces.owner_id = visible_users.id
|
|
INNER JOIN
|
|
organizations
|
|
ON workspaces.organization_id = organizations.id
|
|
INNER JOIN
|
|
templates
|
|
ON workspaces.template_id = templates.id
|
|
;
|
|
|
|
COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';
|