fix: stop query returning dormant workspaces

DanielleMaywood · DanielleMaywood · commit 2d239a6d8dea · 2024-11-28T14:51:02.000Z
diff --git a/coderd/autobuild/lifecycle_executor.go b/coderd/autobuild/lifecycle_executor.go
@@ -205,7 +205,7 @@ func (e *Executor) runOnce(t time.Time) Stats {
 						return xerrors.Errorf("get template scheduling options: %w", err)
 					}
 
-					// If next start at is not valid we need to re-compute it.
+					// If next start at is not valid we need to re-compute it
 					if !ws.NextStartAt.Valid && ws.AutostartSchedule.Valid {
 						next, err := schedule.NextAllowedAutostart(currentTick, ws.AutostartSchedule.String, templateSchedule)
 						if err == nil {
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000278_workspace_next_start_at.down.sql b/coderd/database/migrations/000278_workspace_next_start_at.down.sql
@@ -1,7 +1,10 @@
 DROP VIEW workspaces_expanded;
 
-DROP TRIGGER IF EXISTS trigger_update_workspaces_schedule ON workspaces;
-DROP FUNCTION IF EXISTS nullify_workspace_next_start_at;
+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_next_start_at_idx;
 
diff --git a/coderd/database/migrations/000278_workspace_next_start_at.up.sql b/coderd/database/migrations/000278_workspace_next_start_at.up.sql
@@ -2,12 +2,17 @@ ALTER TABLE ONLY workspaces ADD COLUMN IF NOT EXISTS next_start_at TIMESTAMPTZ D
 
 CREATE INDEX workspace_next_start_at_idx ON workspaces USING btree (next_start_at) WHERE (deleted=false);
 
-CREATE FUNCTION nullify_workspace_next_start_at() RETURNS trigger
+CREATE FUNCTION nullify_next_start_at_on_workspace_autostart_modification() RETURNS trigger
 	LANGUAGE plpgsql
 AS $$
 DECLARE
 BEGIN
-	IF (NEW.autostart_schedule <> OLD.autostart_schedule) AND (NEW.next_start_at = OLD.next_start_at) THEN
+	-- A workspace's next_start_at might be invalidated by the following:
+	--   * The autostart schedule has changed independent to next_start_at
+	--   * The workspace has been marked as dormant
+	IF (NEW.autostart_schedule <> OLD.autostart_schedule AND NEW.next_start_at = OLD.next_start_at)
+		OR (NEW.dormant_at IS NOT NULL AND NEW.next_start_at IS NOT NULL)
+	THEN
 		UPDATE workspaces
 		SET next_start_at = NULL
 		WHERE id = NEW.id;
@@ -16,10 +21,10 @@ BEGIN
 END;
 $$;
 
-CREATE TRIGGER trigger_update_workspaces_schedule
+CREATE TRIGGER trigger_nullify_next_start_at_on_workspace_autostart_modification
 	AFTER UPDATE ON workspaces
 	FOR EACH ROW
-EXECUTE PROCEDURE nullify_workspace_next_start_at();
+EXECUTE PROCEDURE nullify_next_start_at_on_workspace_autostart_modification();
 
 -- Recreate view
 DROP VIEW workspaces_expanded;
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -627,18 +627,22 @@ WHERE
 		--   * The workspace's owner is active.
 		--   * The provisioner job did not fail.
 		--   * The workspace build was a stop transition.
+		--   * The workspace is not dormant
 		--   * The workspace has an autostart schedule.
 		--   * It is after the workspace's next start time.
 		(
 			users.status = 'active'::user_status AND
 			provisioner_jobs.job_status != 'failed'::provisioner_job_status AND
 			workspace_builds.transition = 'stop'::workspace_transition AND
+			workspaces.dormant_at IS NULL AND
 			workspaces.autostart_schedule IS NOT NULL AND
 			(
-				-- next_start_at might be null when a coder instance has been updated
-				-- and we haven't yet had an opportunity to set next_start_at. When
-				-- this happens we leave it up to the Coder server to figure out if
-				-- the workspace is ready to autostart.
+				-- next_start_at might be null in these two scenarios:
+				--   * A coder instance was updated and we haven't updated next_start_at yet.
+				--   * A database trigger made it null because of an update to a related column.
+				--
+				-- When this occurs, we return the workspace so the Coder server can
+				-- compute a valid next start at and update it.
 				workspaces.next_start_at IS NULL OR
 				workspaces.next_start_at <= @now :: timestamptz
 			)

Original file line number	Diff line number	Diff line change
`@@ -205,7 +205,7 @@ func (e *Executor) runOnce(t time.Time) Stats {`
`205`	`205`	`return xerrors.Errorf("get template scheduling options: %w", err)`
`206`	`206`	`}`
`207`	`207`
`208`		`- // If next start at is not valid we need to re-compute it.`
	`208`	`+ // If next start at is not valid we need to re-compute it`
`209`	`209`	`if !ws.NextStartAt.Valid && ws.AutostartSchedule.Valid {`
`210`	`210`	`next, err := schedule.NextAllowedAutostart(currentTick, ws.AutostartSchedule.String, templateSchedule)`
`211`	`211`	`if err == nil {`