Skip to content

Commit 2aadf9f

Browse files
DanielleMaywoodDanielleMaywood
committed
fix: unique per resource not build
1 parent 2417efc commit 2aadf9f

File tree

3 files changed

+83
-75
lines changed

3 files changed

+83
-75
lines changed

‎coderd/database/dump.sql

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

‎coderd/database/migrations/000330_workspace_agent_name_unique_trigger.up.sql

Lines changed: 37 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,37 +1,49 @@
11
CREATE OR REPLACE FUNCTION check_workspace_agent_name_unique()
22
RETURNS TRIGGER AS $$
33
DECLARE
4-
workspace_build_id uuid;
5-
existing_count integer;
4+
provisioner_job_id uuid;
5+
agents_with_name integer;
66
BEGIN
7-
-- Get the workspace_build.id for this agent by following the relationship chain:
8-
-- workspace_agents -> workspace_resources -> provisioner_jobs -> workspace_builds
9-
SELECT wb.id INTO workspace_build_id
10-
FROM workspace_resources wr
11-
JOIN provisioner_jobs pj ON wr.job_id = pj.id
12-
JOIN workspace_builds wb ON pj.id = wb.job_id
13-
WHERE wr.id = NEW.resource_id;
7+
-- Count how many agents in this resource already have
8+
-- the given agent ID.
9+
SELECT COUNT(*) INTO agents_with_name
10+
FROM workspace_agents
11+
WHERE workspace_agents.resource_id = NEW.resource_id
12+
AND workspace_agents.name = NEW.name
13+
AND workspace_agents.id != NEW.id;
1414

15-
-- If we couldn't find a workspace_build_id, allow the insert (might be a template import or other edge case)
16-
IF workspace_build_id IS NULL THEN
17-
RETURN NEW;
15+
-- If there's already an agent with this name, raise an error
16+
IF agents_with_name > 0 THEN
17+
RAISE EXCEPTION 'workspace agent name "%" already exists in this workspace resource', NEW.name
18+
USING ERRCODE = 'unique_violation';
1819
END IF;
1920

20-
-- Check if there's already an agent with this name for this workspace build
21-
SELECT COUNT(*) INTO existing_count
22-
FROM workspace_agents wa
23-
JOIN workspace_resources wr ON wa.resource_id = wr.id
24-
JOIN provisioner_jobs pj ON wr.job_id = pj.id
25-
JOIN workspace_builds wb ON pj.id = wb.job_id
26-
WHERE wb.id = workspace_build_id
27-
AND wa.name = NEW.name
28-
AND wa.id != NEW.id; -- Exclude the current agent (for updates)
21+
-- Get the provisioner_jobs.id for this agent by following the relationship chain:
22+
-- workspace_agents -> workspace_resources -> provisioner_jobs
23+
-- SELECT pj.id INTO provisioner_job_id
24+
-- FROM workspace_resources wr
25+
-- JOIN provisioner_jobs pj ON wr.job_id = pj.id
26+
-- WHERE wr.id = NEW.resource_id;
27+
28+
-- If we couldn't find a provisioner_job.id, allow the insert (might be a template import or other edge case)
29+
-- IF provisioner_job_id IS NULL THEN
30+
-- RETURN NEW;
31+
-- END IF;
32+
33+
-- Check if there's already an agent with this name for this provisioner job
34+
-- SELECT COUNT(*) INTO existing_count
35+
-- FROM workspace_agents wa
36+
-- JOIN workspace_resources wr ON wa.resource_id = wr.id
37+
-- JOIN provisioner_jobs pj ON wr.job_id = pj.id
38+
-- WHERE pj.id = provisioner_job_id
39+
-- AND wa.name = NEW.name
40+
-- AND wa.id != NEW.id;
2941

3042
-- If there's already an agent with this name, raise an error
31-
IF existing_count > 0 THEN
32-
RAISE EXCEPTION 'workspace agent name "%" already exists in this workspace', NEW.name
33-
USING ERRCODE = 'unique_violation';
34-
END IF;
43+
-- IF existing_count > 0 THEN
44+
-- RAISE EXCEPTION 'workspace agent name "%" already exists in this provisioner job', NEW.name
45+
-- USING ERRCODE = 'unique_violation';
46+
-- END IF;
3547

3648
RETURN NEW;
3749
END;

‎coderd/database/querier_test.go

Lines changed: 8 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,6 @@ import (
77
"errors"
88
"fmt"
99
"sort"
10-
"sync/atomic"
1110
"testing"
1211
"time"
1312

@@ -4711,13 +4710,11 @@ func TestGetPresetsAtFailureLimit(t *testing.T) {
47114710
func TestWorkspaceAgentNameUniqueTrigger(t *testing.T) {
47124711
t.Parallel()
47134712

4714-
var builds atomic.Int32
4715-
47164713
if !dbtestutil.WillUsePostgres() {
47174714
t.Skip("This test makes use of a database trigger not implemented in dbmem")
47184715
}
47194716

4720-
createWorkspaceWithAgent := func(t *testing.T, db database.Store, org database.Organization, agentName string) (database.WorkspaceTable, database.TemplateVersion, database.WorkspaceAgent) {
4717+
createWorkspaceWithAgent := func(t *testing.T, db database.Store, org database.Organization, agentName string) (database.WorkspaceTable, database.WorkspaceResource, database.WorkspaceAgent) {
47214718
t.Helper()
47224719

47234720
user := dbgen.User(t, db, database.User{})
@@ -4740,7 +4737,7 @@ func TestWorkspaceAgentNameUniqueTrigger(t *testing.T) {
47404737
OrganizationID: org.ID,
47414738
})
47424739
build := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
4743-
BuildNumber: builds.Add(1),
4740+
BuildNumber: 1,
47444741
JobID: job.ID,
47454742
WorkspaceID: workspace.ID,
47464743
TemplateVersionID: templateVersion.ID,
@@ -4753,40 +4750,27 @@ func TestWorkspaceAgentNameUniqueTrigger(t *testing.T) {
47534750
Name: agentName,
47544751
})
47554752

4756-
return workspace, templateVersion, agent
4753+
return workspace, resource, agent
47574754
}
47584755

4759-
t.Run("DuplicateNamesInSameWorkspace", func(t *testing.T) {
4756+
t.Run("DuplicateNamesInSameWorkspaceBuild", func(t *testing.T) {
47604757
t.Parallel()
47614758

47624759
db, _ := dbtestutil.NewDB(t)
47634760
org := dbgen.Organization(t, db, database.Organization{})
47644761
ctx := testutil.Context(t, testutil.WaitShort)
47654762

47664763
// Given: A workspace with an agent
4767-
workspace1, templateVersion1, agent1 := createWorkspaceWithAgent(t, db, org, "duplicate-agent")
4768-
require.Equal(t, "duplicate-agent", agent1.Name)
4764+
_, resource, agent := createWorkspaceWithAgent(t, db, org, "duplicate-agent")
4765+
require.Equal(t, "duplicate-agent", agent.Name)
47694766

47704767
// When: Another agent is created for that workspace with the same name.
4771-
job2 := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
4772-
Type: database.ProvisionerJobTypeWorkspaceBuild,
4773-
OrganizationID: org.ID,
4774-
})
4775-
build2 := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
4776-
BuildNumber: builds.Add(1),
4777-
JobID: job2.ID,
4778-
WorkspaceID: workspace1.ID,
4779-
TemplateVersionID: templateVersion1.ID,
4780-
})
4781-
resource2 := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
4782-
JobID: build2.JobID,
4783-
})
47844768
_, err := db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
47854769
ID: uuid.New(),
47864770
CreatedAt: time.Now(),
47874771
UpdatedAt: time.Now(),
47884772
Name: "duplicate-agent", // Same name as agent1
4789-
ResourceID: resource2.ID,
4773+
ResourceID: resource.ID,
47904774
AuthToken: uuid.New(),
47914775
Architecture: "amd64",
47924776
OperatingSystem: "linux",
@@ -4798,7 +4782,7 @@ func TestWorkspaceAgentNameUniqueTrigger(t *testing.T) {
47984782
var pqErr *pq.Error
47994783
require.True(t, errors.As(err, &pqErr))
48004784
require.Equal(t, pq.ErrorCode("23505"), pqErr.Code) // unique_violation
4801-
require.Contains(t, pqErr.Message, `workspace agent name "duplicate-agent" already exists in this workspace`)
4785+
require.Contains(t, pqErr.Message, `workspace agent name "duplicate-agent" already exists in this workspace resource`)
48024786
})
48034787

48044788
t.Run("SameNamesInDifferentWorkspaces", func(t *testing.T) {

0 commit comments

Comments
 (0)