Skip to content

Commit 3e583d9

Browse files
johnstcnjohnstcn
committed
improve query
1 parent a71a809 commit 3e583d9

File tree

5 files changed

+100
-86
lines changed

5 files changed

+100
-86
lines changed

‎coderd/activitybump.go

+66-57
Original file line numberDiff line numberDiff line change
@@ -2,13 +2,16 @@ package coderd
22

33
import (
44
"context"
5+
"database/sql"
6+
"errors"
57
"time"
68

79
"github.com/google/uuid"
810
"golang.org/x/xerrors"
911

1012
"cdr.dev/slog"
1113
"github.com/coder/coder/v2/coderd/database"
14+
"github.com/coder/coder/v2/coderd/database/dbtime"
1215
)
1316

1417
// activityBumpWorkspace automatically bumps the workspace's auto-off timer
@@ -18,72 +21,78 @@ func activityBumpWorkspace(ctx context.Context, log slog.Logger, db database.Sto
1821
// low priority operations fail first.
1922
ctx, cancel := context.WithTimeout(ctx, time.Second*15)
2023
defer cancel()
24+
// if err := db.ActivityBumpWorkspace(ctx, workspaceID); err != nil {
25+
// if !xerrors.Is(err, context.Canceled) && !database.IsQueryCanceledError(err) {
26+
// // Bump will fail if the context is canceled, but this is ok.
27+
// log.Error(ctx, "bump failed", slog.Error(err),
28+
// slog.F("workspace_id", workspaceID),
29+
// )
30+
// }
31+
// return
32+
// }
2133

2234
err := db.InTx(func(s database.Store) error {
23-
if err := s.ActivityBumpWorkspace(ctx, workspaceID); err != nil {
24-
return xerrors.Errorf("activity bump workspace: %w", err)
35+
build, err := s.GetLatestWorkspaceBuildByWorkspaceID(ctx, workspaceID)
36+
if errors.Is(err, sql.ErrNoRows) {
37+
return nil
38+
} else if err != nil {
39+
return xerrors.Errorf("get latest workspace build: %w", err)
2540
}
26-
// build, err := s.GetLatestWorkspaceBuildByWorkspaceID(ctx, workspaceID)
27-
// if errors.Is(err, sql.ErrNoRows) {
28-
// return nil
29-
// } else if err != nil {
30-
// return xerrors.Errorf("get latest workspace build: %w", err)
31-
// }
3241

33-
// job, err := s.GetProvisionerJobByID(ctx, build.JobID)
34-
// if err != nil {
35-
// return xerrors.Errorf("get provisioner job: %w", err)
36-
// }
42+
job, err := s.GetProvisionerJobByID(ctx, build.JobID)
43+
if err != nil {
44+
return xerrors.Errorf("get provisioner job: %w", err)
45+
}
46+
47+
if build.Transition != database.WorkspaceTransitionStart || !job.CompletedAt.Valid {
48+
return nil
49+
}
3750

38-
// if build.Transition != database.WorkspaceTransitionStart || !job.CompletedAt.Valid {
39-
// return nil
40-
// }
51+
if build.Deadline.IsZero() {
52+
// Workspace shutdown is manual
53+
return nil
54+
}
4155

42-
// if build.Deadline.IsZero() {
43-
// // Workspace shutdown is manual
44-
// return nil
45-
// }
56+
workspace, err := s.GetWorkspaceByID(ctx, workspaceID)
57+
if err != nil {
58+
return xerrors.Errorf("get workspace: %w", err)
59+
}
4660

47-
// workspace, err := s.GetWorkspaceByID(ctx, workspaceID)
48-
// if err != nil {
49-
// return xerrors.Errorf("get workspace: %w", err)
50-
// }
61+
var (
62+
// We bump by the original TTL to prevent counter-intuitive behavior
63+
// as the TTL wraps. For example, if I set the TTL to 12 hours, sign off
64+
// work at midnight, come back at 10am, I would want another full day
65+
// of uptime. In the prior implementation, the workspace would enter
66+
// a state of always expiring 1 hour in the future
67+
bumpAmount = time.Duration(workspace.Ttl.Int64)
68+
// DB writes are expensive so we only bump when 5% of the deadline
69+
// has elapsed.
70+
bumpEvery = bumpAmount / 20
71+
timeSinceLastBump = bumpAmount - time.Until(build.Deadline)
72+
)
5173

52-
// var (
53-
// We bump by the original TTL to prevent counter-intuitive behavior
54-
// as the TTL wraps. For example, if I set the TTL to 12 hours, sign off
55-
// work at midnight, come back at 10am, I would want another full day
56-
// of uptime. In the prior implementation, the workspace would enter
57-
// a state of always expiring 1 hour in the future
58-
// bumpAmount = time.Duration(workspace.Ttl.Int64)
59-
// DB writes are expensive so we only bump when 5% of the deadline
60-
// has elapsed.
61-
// bumpEvery = bumpAmount / 20
62-
// timeSinceLastBump = bumpAmount - time.Until(build.Deadline)
63-
// )
74+
if timeSinceLastBump < bumpEvery {
75+
return nil
76+
}
6477

65-
// if timeSinceLastBump < bumpEvery {
66-
// return nil
67-
// }
68-
//
69-
// if bumpAmount == 0 {
70-
// return nil
71-
// }
72-
//
73-
// newDeadline := dbtime.Now().Add(bumpAmount)
74-
// if !build.MaxDeadline.IsZero() && newDeadline.After(build.MaxDeadline) {
75-
// newDeadline = build.MaxDeadline
76-
// }
77-
//
78-
// if err := s.UpdateWorkspaceBuildByID(ctx, database.UpdateWorkspaceBuildByIDParams{
79-
// ID: build.ID,
80-
// UpdatedAt: dbtime.Now(),
81-
// ProvisionerState: build.ProvisionerState,
82-
// Deadline: newDeadline,
83-
// MaxDeadline: build.MaxDeadline,
84-
// }); err != nil {
85-
// return xerrors.Errorf("update workspace build: %w", err)
86-
// }
78+
if bumpAmount == 0 {
79+
return nil
80+
}
81+
82+
newDeadline := dbtime.Now().Add(bumpAmount)
83+
if !build.MaxDeadline.IsZero() && newDeadline.After(build.MaxDeadline) {
84+
newDeadline = build.MaxDeadline
85+
}
86+
87+
if err := s.UpdateWorkspaceBuildByID(ctx, database.UpdateWorkspaceBuildByIDParams{
88+
ID: build.ID,
89+
UpdatedAt: dbtime.Now(),
90+
ProvisionerState: build.ProvisionerState,
91+
Deadline: newDeadline,
92+
MaxDeadline: build.MaxDeadline,
93+
}); err != nil {
94+
return xerrors.Errorf("update workspace build: %w", err)
95+
}
8796
return nil
8897
}, nil)
8998
if err != nil {

‎coderd/database/dbmetrics/dbmetrics.go

+1-1
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbtestutil/db.go

+1
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ func NewDB(t testing.TB) (database.Store, pubsub.Pubsub) {
3333
)
3434
connectionURL, closePg, err = postgres.Open()
3535
require.NoError(t, err)
36+
t.Logf("using postgres connection url: %s", connectionURL)
3637
t.Cleanup(closePg)
3738
}
3839
sqlDB, err := sql.Open("postgres", connectionURL)

‎coderd/database/queries.sql.go

+16-14
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/activitybump.sql

+16-14
Original file line numberDiff line numberDiff line change
@@ -1,33 +1,35 @@
11
-- name: ActivityBumpWorkspace :exec
22
WITH latest AS (
33
SELECT
4-
workspace_builds.id,
5-
workspace_builds.deadline,
6-
workspace_builds.max_deadline,
7-
workspaces.ttl,
8-
(workspace_builds.deadline + (workspaces.ttl/1000 || ' microsecond')::interval ) AS new_deadline
4+
workspace_builds.id::uuid AS build_id,
5+
workspace_builds.deadline::timestamp AS build_deadline,
6+
workspace_builds.max_deadline::timestamp AS build_max_deadline,
7+
workspace_builds.transition AS build_transition,
8+
provisioner_jobs.completed_at::timestamp AS job_completed_at,
9+
(workspaces.ttl / 1000 / 1000 / 1000 || ' seconds')::interval AS ttl_interval,
10+
(NOW() AT TIME ZONE 'UTC')::timestamp as now_utc
911
FROM workspace_builds
1012
JOIN provisioner_jobs
1113
ON provisioner_jobs.id = workspace_builds.job_id
1214
JOIN workspaces
1315
ON workspaces.id = workspace_builds.workspace_id
1416
WHERE workspace_builds.workspace_id = $1::uuid
15-
AND workspace_builds.transition = 'start'
16-
AND workspace_builds.deadline > NOW()
17-
AND provisioner_jobs.completed_at IS NOT NULL
18-
ORDER BY workspace_builds.build_number ASC
17+
ORDER BY workspace_builds.build_number DESC
1918
LIMIT 1
2019
)
2120
UPDATE
2221
workspace_builds wb
2322
SET
2423
updated_at = NOW(),
2524
deadline = CASE
26-
WHEN l.max_deadline = '0001-01-01 00:00:00'
27-
THEN l.new_deadline
28-
ELSE LEAST(l.new_deadline, l.max_deadline)
25+
WHEN l.build_max_deadline = '0001-01-01 00:00:00'
26+
THEN l.now_utc + l.ttl_interval
27+
ELSE LEAST(l.now_utc + l.ttl_interval, l.build_max_deadline)
2928
END
3029
FROM latest l
31-
WHERE
32-
wb.id = l.id
30+
WHERE wb.id = l.build_id
31+
AND l.build_transition = 'start'
32+
AND l.build_deadline != '0001-01-01 00:00:00'
33+
AND l.job_completed_at IS NOT NULL
34+
AND l.build_deadline + (l.ttl_interval * 0.05) < NOW()
3335
;

0 commit comments

Comments
 (0)