erickirt · pull · May 28, 2025 · May 28, 2025 · May 28, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2341,7 +2341,7 @@ func (q *querier) GetProvisionerJobsByIDs(ctx context.Context, ids []uuid.UUID)
 	return provisionerJobs, nil
 }
 
-func (q *querier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+func (q *querier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, ids database.GetProvisionerJobsByIDsWithQueuePositionParams) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
 	// TODO: Remove this once we have a proper rbac check for provisioner jobs.
 	// Details in https://github.com/coder/coder/issues/16160
 	return q.db.GetProvisionerJobsByIDsWithQueuePosition(ctx, ids)

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -4345,7 +4345,7 @@ func (s *MethodTestSuite) TestSystemFunctions() {
 		check.Args([]uuid.UUID{uuid.New()}).Asserts(rbac.ResourceSystem, policy.ActionRead)
 	}))
 	s.Run("GetProvisionerJobsByIDsWithQueuePosition", s.Subtest(func(db database.Store, check *expects) {
-		check.Args([]uuid.UUID{}).Asserts()
+		check.Args(database.GetProvisionerJobsByIDsWithQueuePositionParams{}).Asserts()
 	}))
 	s.Run("GetReplicaByID", s.Subtest(func(db database.Store, check *expects) {
 		check.Args(uuid.New()).Asserts(rbac.ResourceSystem, policy.ActionRead).Errors(sql.ErrNoRows)

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -4684,14 +4684,14 @@ func (q *FakeQuerier) GetProvisionerJobsByIDs(_ context.Context, ids []uuid.UUID
 	return jobs, nil
 }
 
-func (q *FakeQuerier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, ids []uuid.UUID) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
+func (q *FakeQuerier) GetProvisionerJobsByIDsWithQueuePosition(ctx context.Context, arg database.GetProvisionerJobsByIDsWithQueuePositionParams) ([]database.GetProvisionerJobsByIDsWithQueuePositionRow, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
-	if ids == nil {
-		ids = []uuid.UUID{}
+	if arg.IDs == nil {
+		arg.IDs = []uuid.UUID{}
 	}
-	return q.getProvisionerJobsByIDsWithQueuePositionLockedTagBasedQueue(ctx, ids)
+	return q.getProvisionerJobsByIDsWithQueuePositionLockedTagBasedQueue(ctx, arg.IDs)
 }
 
 func (q *FakeQuerier) GetProvisionerJobsByOrganizationAndStatusWithQueuePositionAndProvisioner(ctx context.Context, arg database.GetProvisionerJobsByOrganizationAndStatusWithQueuePositionAndProvisionerParams) ([]database.GetProvisionerJobsByOrganizationAndStatusWithQueuePositionAndProvisionerRow, error) {

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000332_workspace_agent_name_unique_trigger.down.sql b/coderd/database/migrations/000332_workspace_agent_name_unique_trigger.down.sql
@@ -0,0 +1,2 @@
+DROP TRIGGER IF EXISTS workspace_agent_name_unique_trigger ON workspace_agents;
+DROP FUNCTION IF EXISTS check_workspace_agent_name_unique();
diff --git a/coderd/database/migrations/000332_workspace_agent_name_unique_trigger.up.sql b/coderd/database/migrations/000332_workspace_agent_name_unique_trigger.up.sql
@@ -0,0 +1,45 @@
+CREATE OR REPLACE FUNCTION check_workspace_agent_name_unique()
+RETURNS TRIGGER AS $$
+DECLARE
+	workspace_build_id uuid;
+	agents_with_name int;
+BEGIN
+	-- Find the workspace build the workspace agent is being inserted into.
+	SELECT workspace_builds.id INTO workspace_build_id
+	FROM workspace_resources
+	JOIN workspace_builds ON workspace_builds.job_id = workspace_resources.job_id
+	WHERE workspace_resources.id = NEW.resource_id;
+
+	-- If the agent doesn't have a workspace build, we'll allow the insert.
+	IF workspace_build_id IS NULL THEN
+		RETURN NEW;
+	END IF;
+
+	-- Count how many agents in this workspace build already have the given agent name.
+	SELECT COUNT(*) INTO agents_with_name
+	FROM workspace_agents
+	JOIN workspace_resources ON workspace_resources.id = workspace_agents.resource_id
+	JOIN workspace_builds ON workspace_builds.job_id = workspace_resources.job_id
+	WHERE workspace_builds.id = workspace_build_id
+		AND workspace_agents.name = NEW.name
+		AND workspace_agents.id != NEW.id;
+
+	-- If there's already an agent with this name, raise an error
+	IF agents_with_name > 0 THEN
+		RAISE EXCEPTION 'workspace agent name "%" already exists in this workspace build', NEW.name
+			USING ERRCODE = 'unique_violation';
+	END IF;
+
+	RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER workspace_agent_name_unique_trigger
+	BEFORE INSERT OR UPDATE OF name, resource_id ON workspace_agents
+	FOR EACH ROW
+	EXECUTE FUNCTION check_workspace_agent_name_unique();
+
+COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS
+'Use a trigger instead of a unique constraint because existing data may violate
+the uniqueness requirement. A trigger allows us to enforce uniqueness going
+forward without requiring a migration to clean up historical data.';
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		DROP TRIGGER IF EXISTS workspace_agent_name_unique_trigger ON workspace_agents;
		DROP FUNCTION IF EXISTS check_workspace_agent_name_unique();