Skip to content

Commit 53c3ba5

Browse files
johnstcnjohnstcn
committed
run both queries and diff results
1 parent 6b4d6dc commit 53c3ba5

File tree

9 files changed

+317
-6
lines changed

9 files changed

+317
-6
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2560,6 +2560,14 @@ func (q *querier) GetRunningPrebuiltWorkspaces(ctx context.Context) ([]database.
25602560
return q.db.GetRunningPrebuiltWorkspaces(ctx)
25612561
}
25622562

2563+
func (q *querier) GetRunningPrebuiltWorkspacesOptimized(ctx context.Context) ([]database.GetRunningPrebuiltWorkspacesOptimizedRow, error) {
2564+
// This query returns only prebuilt workspaces, but we decided to require permissions for all workspaces.
2565+
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.All()); err != nil {
2566+
return nil, err
2567+
}
2568+
return q.db.GetRunningPrebuiltWorkspacesOptimized(ctx)
2569+
}
2570+
25632571
func (q *querier) GetRuntimeConfig(ctx context.Context, key string) (string, error) {
25642572
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
25652573
return "", err

‎coderd/database/dbmem/dbmem.go

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5101,6 +5101,10 @@ func (q *FakeQuerier) GetRunningPrebuiltWorkspaces(ctx context.Context) ([]datab
51015101
return nil, ErrUnimplemented
51025102
}
51035103

5104+
func (q *FakeQuerier) GetRunningPrebuiltWorkspacesOptimized(ctx context.Context) ([]database.GetRunningPrebuiltWorkspacesOptimizedRow, error) {
5105+
panic("not implemented")
5106+
}
5107+
51045108
func (q *FakeQuerier) GetRuntimeConfig(_ context.Context, key string) (string, error) {
51055109
q.mutex.Lock()
51065110
defer q.mutex.Unlock()

‎coderd/database/dbmetrics/querymetrics.go

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

‎coderd/database/dbmock/dbmock.go

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

‎coderd/database/querier.go

Lines changed: 1 addition & 0 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: 63 additions & 5 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: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a pre
4848
-- AND NOT t.deleted -- We don't exclude deleted templates because there's no constraint in the DB preventing a soft deletion on a template while workspaces are running.
4949
AND (t.id = sqlc.narg('template_id')::uuid OR sqlc.narg('template_id') IS NULL);
5050

51-
-- name: GetRunningPrebuiltWorkspaces :many
51+
-- name: GetRunningPrebuiltWorkspacesOptimized :many
5252
WITH latest_prebuilds AS (
5353
SELECT
5454
workspaces.id,
@@ -96,6 +96,23 @@ SELECT
9696
FROM latest_prebuilds
9797
LEFT JOIN ready_agents ON ready_agents.job_id = latest_prebuilds.job_id
9898
LEFT JOIN workspace_latest_presets ON workspace_latest_presets.workspace_id = latest_prebuilds.id
99+
ORDER BY latest_prebuilds.id
100+
;
101+
102+
-- name: GetRunningPrebuiltWorkspaces :many
103+
SELECT
104+
p.id,
105+
p.name,
106+
p.template_id,
107+
b.template_version_id,
108+
p.current_preset_id AS current_preset_id,
109+
p.ready,
110+
p.created_at
111+
FROM workspace_prebuilds p
112+
INNER JOIN workspace_latest_builds b ON b.workspace_id = p.id
113+
WHERE (b.transition = 'start'::workspace_transition
114+
AND b.job_status = 'succeeded'::provisioner_job_status)
115+
ORDER BY p.id;
99116
;
100117

101118
-- name: CountInProgressPrebuilds :many

‎enterprise/coderd/prebuilds/reconcile.go

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@ import (
1212
"sync/atomic"
1313
"time"
1414

15+
"github.com/google/go-cmp/cmp"
16+
1517
"github.com/hashicorp/go-multierror"
1618
"github.com/prometheus/client_golang/prometheus"
1719

@@ -398,11 +400,21 @@ func (c *StoreReconciler) SnapshotState(ctx context.Context, store database.Stor
398400
return xerrors.Errorf("failed to get preset prebuild schedules: %w", err)
399401
}
400402

403+
// Get results from both original and optimized queries for comparison
401404
allRunningPrebuilds, err := db.GetRunningPrebuiltWorkspaces(ctx)
402405
if err != nil {
403406
return xerrors.Errorf("failed to get running prebuilds: %w", err)
404407
}
405408

409+
// Compare with optimized query to ensure behavioral correctness
410+
optimized, err := db.GetRunningPrebuiltWorkspacesOptimized(ctx)
411+
if err != nil {
412+
// Log the error but continue with original results
413+
c.logger.Error(ctx, "optimized GetRunningPrebuiltWorkspacesOptimized failed", slog.Error(err))
414+
} else {
415+
CompareGetRunningPrebuiltWorkspacesResults(ctx, c.logger, allRunningPrebuilds, optimized)
416+
}
417+
406418
allPrebuildsInProgress, err := db.CountInProgressPrebuilds(ctx)
407419
if err != nil {
408420
return xerrors.Errorf("failed to get prebuilds in progress: %w", err)
@@ -922,3 +934,29 @@ func SetPrebuildsReconciliationPaused(ctx context.Context, db database.Store, pa
922934
}
923935
return db.UpsertPrebuildsSettings(ctx, string(settingsJSON))
924936
}
937+
938+
// CompareGetRunningPrebuiltWorkspacesResults compares the original and optimized
939+
// query results and logs any differences found. This function can be easily
940+
// removed once we're confident the optimized query works correctly.
941+
func CompareGetRunningPrebuiltWorkspacesResults(
942+
ctx context.Context,
943+
logger slog.Logger,
944+
original []database.GetRunningPrebuiltWorkspacesRow,
945+
optimized []database.GetRunningPrebuiltWorkspacesOptimizedRow,
946+
) {
947+
// Convert optimized results to the same type as original for comparison
948+
var optimizedConverted []database.GetRunningPrebuiltWorkspacesRow
949+
if original != nil {
950+
optimizedConverted := make([]database.GetRunningPrebuiltWorkspacesRow, len(optimized))
951+
for i, row := range optimized {
952+
optimizedConverted[i] = database.GetRunningPrebuiltWorkspacesRow(row)
953+
}
954+
}
955+
956+
// Compare the results and log an error if they differ.
957+
// NOTE: explicitly not sorting here as both query results are ordered by ID.
958+
if diff := cmp.Diff(original, optimizedConverted); diff != "" {
959+
logger.Error(ctx, "results differ for GetRunningPrebuiltWorkspacesOptimized",
960+
slog.F("diff", diff))
961+
}
962+
}

0 commit comments

Comments
 (0)