feat: add queue_position and queue_size to provisioner jobs

This should help with monitoring for large-scale deployments. We can add an indicator to the UI for the queue position of workspaces!
coder · kylecarbs · Jun 20, 2023 · Jun 17, 2023 · Jun 17, 2023 · Jun 20, 2023
commit 65fbf44bd772f576f70fbef080eb10a55a04a4b0
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1087,6 +1087,11 @@ func (q *querier) GetProvisionerJobsByIDs(ctx context.Context, ids []uuid.UUID)
 	return q.db.GetProvisionerJobsByIDs(ctx, ids)
 }
 
+// TODO: we need to add a provisioner job resource
+func (q *querier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+	return q.db.GetProvisionerJobsByIDsWithQueuePosition(ctx, ids)
+}
+
 // TODO: We need to create a ProvisionerJob resource type
 func (q *querier) GetProvisionerJobsCreatedAfter(ctx context.Context, createdAt time.Time) ([]database.ProvisionerJob, error) {
 	// if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -2051,6 +2051,35 @@ func (q *fakeQuerier) GetProvisionerJobsByIDs(_ context.Context, ids []uuid.UUID
 	return jobs, nil
 }
 
+func (q *fakeQuerier) GetProvisionerJobsByIDsWithQueuePosition(_ context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	jobs := make([]database.GetProvisionerJobsByIDsWithQueuePositionRow, 0)
+	queuePosition := int64(1)
+	for _, job := range q.provisionerJobs {
+		for _, id := range ids {
+			if id == job.ID {
+				jobs = append(jobs, database.GetProvisionerJobsByIDsWithQueuePositionRow{
+					ProvisionerJob: job,
+					QueuePosition:  queuePosition,
+				})
+				break
+			}
+		}
+		if !job.StartedAt.Valid {
+			queuePosition++
+		}
+	}
+	for _, job := range jobs {
+		if !job.ProvisionerJob.StartedAt.Valid {
+			// Set it to the max position!
+			job.QueueSize = queuePosition
+		}
+	}
+	return jobs, nil
+}
+
 func (q *fakeQuerier) GetProvisionerJobsCreatedAfter(_ context.Context, after time.Time) ([]database.ProvisionerJob, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/provisionerjobs.sql b/coderd/database/queries/provisionerjobs.sql
@@ -47,6 +47,26 @@ FROM
 WHERE
 	id = ANY(@ids :: uuid [ ]);
 
+-- name: GetProvisionerJobsByIDsWithQueuePosition :many
+SELECT
+	sqlc.embed(pj),
+	COALESCE(a.queue_position, 0) AS queue_position,
+	COALESCE(a.queue_size, 0) AS queue_size
+FROM
+	provisioner_jobs pj
+LEFT JOIN (
+	SELECT
+		id,
+		ROW_NUMBER() OVER (ORDER BY created_at) AS queue_position,
+		COUNT(*) OVER (PARTITION BY started_at IS NULL) AS queue_size
+	FROM
+		provisioner_jobs
+	WHERE
+		started_at IS NULL
+	GROUP BY id
+) a ON a.id = pj.id
+WHERE pj.id = ANY(@ids :: uuid [ ]);
+
 -- name: GetProvisionerJobsCreatedAfter :many
 SELECT * FROM provisioner_jobs WHERE created_at > $1;
 

diff --git a/coderd/provisionerjobs.go b/coderd/provisionerjobs.go
@@ -195,14 +195,17 @@ func convertProvisionerJobLog(provisionerJobLog database.ProvisionerJobLog) code
 	}
 }
 
-func convertProvisionerJob(provisionerJob database.ProvisionerJob) codersdk.ProvisionerJob {
+func convertProvisionerJob(pj database.GetProvisionerJobsByIDsWithQueuePositionRow) codersdk.ProvisionerJob {
+	provisionerJob := pj.ProvisionerJob
 	job := codersdk.ProvisionerJob{
-		ID:        provisionerJob.ID,
-		CreatedAt: provisionerJob.CreatedAt,
-		Error:     provisionerJob.Error.String,
-		ErrorCode: codersdk.JobErrorCode(provisionerJob.ErrorCode.String),
-		FileID:    provisionerJob.FileID,
-		Tags:      provisionerJob.Tags,
+		ID:            provisionerJob.ID,
+		CreatedAt:     provisionerJob.CreatedAt,
+		Error:         provisionerJob.Error.String,
+		ErrorCode:     codersdk.JobErrorCode(provisionerJob.ErrorCode.String),
+		FileID:        provisionerJob.FileID,
+		Tags:          provisionerJob.Tags,
+		QueuePosition: int(pj.QueuePosition),
+		QueueSize:     int(pj.QueueSize),
 	}
 	// Applying values optional to the struct.
 	if provisionerJob.StartedAt.Valid {

diff --git a/coderd/provisionerjobs_internal_test.go b/coderd/provisionerjobs_internal_test.go
@@ -126,7 +126,9 @@ func TestConvertProvisionerJob_Unit(t *testing.T) {
 		testCase := testCase
 		t.Run(testCase.name, func(t *testing.T) {
 			t.Parallel()
-			actual := convertProvisionerJob(testCase.input)
+			actual := convertProvisionerJob(database.GetProvisionerJobsByIDsWithQueuePositionRow{
+				ProvisionerJob: testCase.input,
+			})
 			assert.Equal(t, testCase.expected, actual)
 		})
 	}