Skip to content

Commit da75520

Browse files
johnstcnjohnstcn
committed
run both queries and diff results
1 parent 16b59c9 commit da75520

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
@@ -2546,6 +2546,14 @@ func (q *querier) GetRunningPrebuiltWorkspaces(ctx context.Context) ([]database.
25462546
return q.db.GetRunningPrebuiltWorkspaces(ctx)
25472547
}
25482548

2549+
func (q *querier) GetRunningPrebuiltWorkspacesOptimized(ctx context.Context) ([]database.GetRunningPrebuiltWorkspacesOptimizedRow, error) {
2550+
// This query returns only prebuilt workspaces, but we decided to require permissions for all workspaces.
2551+
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.All()); err != nil {
2552+
return nil, err
2553+
}
2554+
return q.db.GetRunningPrebuiltWorkspacesOptimized(ctx)
2555+
}
2556+
25492557
func (q *querier) GetRuntimeConfig(ctx context.Context, key string) (string, error) {
25502558
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
25512559
return "", err

‎coderd/database/dbmem/dbmem.go

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

5088+
func (q *FakeQuerier) GetRunningPrebuiltWorkspacesOptimized(ctx context.Context) ([]database.GetRunningPrebuiltWorkspacesOptimizedRow, error) {
5089+
panic("not implemented")
5090+
}
5091+
50885092
func (q *FakeQuerier) GetRuntimeConfig(_ context.Context, key string) (string, error) {
50895093
q.mutex.Lock()
50905094
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
@@ -11,6 +11,8 @@ import (
1111
"sync/atomic"
1212
"time"
1313

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

@@ -375,11 +377,21 @@ func (c *StoreReconciler) SnapshotState(ctx context.Context, store database.Stor
375377
return xerrors.Errorf("failed to get preset prebuild schedules: %w", err)
376378
}
377379

380+
// Get results from both original and optimized queries for comparison
378381
allRunningPrebuilds, err := db.GetRunningPrebuiltWorkspaces(ctx)
379382
if err != nil {
380383
return xerrors.Errorf("failed to get running prebuilds: %w", err)
381384
}
382385

386+
// Compare with optimized query to ensure behavioral correctness
387+
optimized, err := db.GetRunningPrebuiltWorkspacesOptimized(ctx)
388+
if err != nil {
389+
// Log the error but continue with original results
390+
c.logger.Error(ctx, "optimized GetRunningPrebuiltWorkspacesOptimized failed", slog.Error(err))
391+
} else {
392+
CompareGetRunningPrebuiltWorkspacesResults(ctx, c.logger, allRunningPrebuilds, optimized)
393+
}
394+
383395
allPrebuildsInProgress, err := db.CountInProgressPrebuilds(ctx)
384396
if err != nil {
385397
return xerrors.Errorf("failed to get prebuilds in progress: %w", err)
@@ -884,3 +896,29 @@ func (c *StoreReconciler) trackResourceReplacement(ctx context.Context, workspac
884896

885897
return notifErr
886898
}
899+
900+
// CompareGetRunningPrebuiltWorkspacesResults compares the original and optimized
901+
// query results and logs any differences found. This function can be easily
902+
// removed once we're confident the optimized query works correctly.
903+
func CompareGetRunningPrebuiltWorkspacesResults(
904+
ctx context.Context,
905+
logger slog.Logger,
906+
original []database.GetRunningPrebuiltWorkspacesRow,
907+
optimized []database.GetRunningPrebuiltWorkspacesOptimizedRow,
908+
) {
909+
// Convert optimized results to the same type as original for comparison
910+
var optimizedConverted []database.GetRunningPrebuiltWorkspacesRow
911+
if original != nil {
912+
optimizedConverted := make([]database.GetRunningPrebuiltWorkspacesRow, len(optimized))
913+
for i, row := range optimized {
914+
optimizedConverted[i] = database.GetRunningPrebuiltWorkspacesRow(row)
915+
}
916+
}
917+
918+
// Compare the results and log an error if they differ.
919+
// NOTE: explicitly not sorting here as both query results are ordered by ID.
920+
if diff := cmp.Diff(original, optimizedConverted); diff != "" {
921+
logger.Error(ctx, "results differ for GetRunningPrebuiltWorkspacesOptimized",
922+
slog.F("diff", diff))
923+
}
924+
}

0 commit comments

Comments
 (0)