[Bug ?] procedure department_user_remove(uuid, uuid) does not exist

[pvi-github]

Hello,

I installed for testing a fresh new thunder dome that in run with docker-compose.

I managed to create an admin user, a company, a department, a team and created a first story.

Then i made a user register himself and added it to the company.

After a few tests itried to delete the user and i received an error message: "procedure department_user_remove(uuid, uuid) does not exist"

The procedure is yet to be coded ? Or it was renamed but not completely yet ? I made a huge mistake ? What do you think ?

Here is where i am on git :

$ date
Sat Apr 27 19:21:28 UTC 2024
$ git status
On branch main
Your branch is up to date with 'origin/main'.

Here is the full log of docker-compose up --build

b_1           | 2024-04-27 19:13:05.661 UTC [35] ERROR:  procedure department_user_remove(uuid, uuid) does not exist at character 6
db_1           | 2024-04-27 19:13:05.661 UTC [35] HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
db_1           | 2024-04-27 19:13:05.661 UTC [35] QUERY:  CALL department_user_remove(temprow.id, userId)
db_1           | 2024-04-27 19:13:05.661 UTC [35] CONTEXT:  PL/pgSQL function thunderdome.organization_user_remove(uuid,uuid) line 7 at CALL
db_1           | 2024-04-27 19:13:05.661 UTC [35] STATEMENT:  CALL thunderdome.organization_user_remove($1, $2);
thunderdome_1  | {"level":"error","ts":1714245185.6619315,"caller":"http/organization.go:472","msg":"handleOrganizationRemoveUser error","version":"","error":"organization remove user query error: ERROR: procedure department_user_remove(uuid, uuid) does not exist (SQLSTATE 42883)","user_id":"53536a0f-e82f-4058-90d8-99323b9755ae","session_user_id":"450e0625-cedf-4962-8c21-ad1e1e8b8d76","organization_id":"1c63a9b4-5bf0-42d9-b1a1-3f4cb763b99f","stacktrace":"github.com/StevenWeathers/thunderdome-planning-poker/internal/http.New.(*Service).handleOrganizationRemoveUser.func243\n\t/go/src/github.com/stevenweathers/thunderdome-planning-poker/internal/http/organization.go:472\ngithub.com/StevenWeathers/thunderdome-planning-poker/internal/http.New.(*Service).orgAdminOnly.func244\n\t/go/src/github.com/stevenweathers/thunderdome-planning-poker/internal/http/middleware.go:270\ngithub.com/StevenWeathers/thunderdome-planning-poker/internal/http.New.(*Service).userOnly.func245\n\t/go/src/github.com/stevenweathers/thunderdome-planning-poker/internal/http/middleware.go:77\nnet/http.HandlerFunc.ServeHTTP\n\t/usr/local/go/src/net/http/server.go:2166\ngo.opentelemetry.io/contrib/instrumentation/github.com/gorilla/mux/otelmux.traceware.ServeHTTP\n\t/go/pkg/mod/go.opentelemetry.io/contrib/instrumentation/github.com/gorilla/mux/otelmux@v0.39.0/mux.go:155\ngithub.com/StevenWeathers/thunderdome-planning-poker/internal/http.(*Service).panicRecovery-fm.(*Service).panicRecovery.func1\n\t/go/src/github.com/stevenweathers/thunderdome-planning-poker/internal/http/middleware.go:25\nnet/http.HandlerFunc.ServeHTTP\n\t/usr/local/go/src/net/http/server.go:2166\ngithub.com/gorilla/mux.(*Router).ServeHTTP\n\t/go/pkg/mod/github.com/gorilla/mux@v1.8.0/mux.go:210\nnet/http.serverHandler.ServeHTTP\n\t/usr/local/go/src/net/http/server.go:3137\nnet/http.(*conn).serve\n\t/usr/local/go/src/net/http/server.go:2039"}

[pvi-github]

Hello ... I'm adding and auto comment:

In the code i found this...

$ grep -r department_user_remove .
./internal/db/team/department.go:               `CALL thunderdome.department_user_remove($1, $2);`,
./internal/db/migrations/20230823233842_create_funcs_procs_triggers.sql:CREATE OR REPLACE PROCEDURE thunderdome.department_user_remove(IN departmentid uuid, IN userid uuid)
./internal/db/migrations/20230823233842_create_funcs_procs_triggers.sql:        CALL department_user_remove(temprow.id, userId);
./internal/db/migrations/20230823233842_create_funcs_procs_triggers.sql:DROP PROCEDURE thunderdome.department_user_remove(IN departmentid uuid, IN userid uuid);

It seems to be a PostgreSQL stored procedure. Is is only dealt with in a migration sql file : 20230823233842_create_funcs_procs_triggers.sql

And this file ends with a DROP PROCEDURE CALL... which seems weird if it is supposed to be called from the department.go file... maybe the DROP PROCEDURE should not be there ?

Hope that can help a bit.

[pvi-github]

Hello ... I'm adding and auto answer to comment:

I just added (manually) in PostgreSQL the (potentially) missing stored procedures...

CREATE OR REPLACE PROCEDURE thunderdome.organization_user_remove(orgid uuid, userid uuid)
    LANGUAGE plpgsql
    AS $$
DECLARE temprow record;
BEGIN
    FOR temprow IN
        SELECT id FROM thunderdome.organization_department WHERE organization_id = orgId
    LOOP
        CALL department_user_remove(temprow.id, userId);
    END LOOP;
    DELETE FROM thunderdome.team_user tu WHERE tu.team_id IN (
        SELECT t.id
        FROM thunderdome.team t
        WHERE t.organization_id = orgId
    ) AND tu.user_id = userId;
    DELETE FROM thunderdome.organization_user WHERE organization_id = orgId AND user_id = userId;

    COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE thunderdome.department_user_remove(departmentid uuid, userid uuid)
    LANGUAGE plpgsql
    AS $$
BEGIN
    DELETE FROM thunderdome.team_user tu WHERE tu.team_id IN (
        SELECT t.id
        FROM thunderdome.team t
        WHERE t.department_id = departmentId
    ) AND tu.user_id = userId;
    DELETE FROM thunderdome.department_user WHERE department_id = departmentId AND user_id = userId;

    COMMIT;
END;
$$;

=> Result : the error still occurs. So i'd be tempted to think that the missing procedure is in the GO code ?

[StevenWeathers]

Sorry for the delay I was on leave from working, I will look into this one as soon as possible.