search

vapor / fluent-postgres-driver

🐘 PostgreSQL driver for Fluent.
MIT License
146 stars 53 forks source link

Enum Migration: Adding multiple new cases to an already existing enum does only add the last case #189

Closed DavidWalter closed 2 years ago

DavidWalter commented 2 years ago

Thank you for providing this excellent framework! 🚀

Describe the bug

Adding multiple new cases to an already existing enum does only add the last case before the update() call.

To Reproduce

1) Add an enum

    struct Migration1: AsyncMigration {
        let name = "CreateTaskType"

        func prepare(on database: Database) async throws {
            let taskType = try await database.enum(TaskType.schema)
                .case(TaskType.simple.rawValue)
                .create()
        }

        func revert(on database: Database) async throws {
            try await database.enum(TaskType.schema)
                .delete()
        }
    }

2) Extend the enum in a second migration

    struct Migration2: AsyncMigration {
        let name = "AddMoreTaskTypes"

        func prepare(on database: Database) async throws {
            let taskTypeExtended = try await database.enum(TaskType.schema)
                            .case(TaskType.singleChoice.rawValue) // this does not get added.
                            .case(TaskType.multipleChoice.rawValue)
                            .update()

        }

        func revert(on database: Database) async throws {
            // none 
        }
    }

3) The migration goes through fine. But when I try to add a model with the .singleChoice type in it. This returns

failed: caught error: "server: invalid input value for enum task_type: "singleChoice" (enum_in)"

When then checking in the PostgreSQL schema, only the last case before the update has been added.

Expected behavior

I expected to be able to add multiple enum cases to an existing enum with a migration like database.enum().case().case().case().update().

Environment

Additional context

A workaround is to do the migration like this:

    struct Migration2: AsyncMigration {
        let name = "AddMoreTaskTypes"

        func prepare(on database: Database) async throws {
            _ = try await database.enum(TaskType.schema)
                            .case(TaskType.singleChoice.rawValue)
                            .update()
            _ = try await database.enum(TaskType.schema)
                            .case(TaskType.multipleChoice.rawValue)
                            .update()
        }

        func revert(on database: Database) async throws {
            // none 
        }
    }
0xTim commented 2 years ago

@DavidWalter what version of FluentKit are you using? 1.21.0 contained a number of fixes for enums. If you're on the latest and still getting the error can you post the generated SQL from the migration?

DavidWalter commented 2 years ago

Hi thanks for your response!

Yes I'm on 1.21.0. See the whole Package.resolved here Package.resolved.txt

(The "AddChoiceAndTextTaskTypes" is the real name of the "AddMoreTaskTypes" example above) I run this migration which resulted in the logs below:

struct Migration2: AsyncMigration {
        let name = "AddChoiceAndTextTaskTypes"

        func prepare(on database: Database) async throws {
            let taskTypes = try await database.enum(TaskType.schema)
                .case(TaskType.singleChoice.rawValue)
                .case(TaskType.multipleChoice.rawValue)
                .update()
         }
}

debug:

2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql ALTER TABLE "ev_envelopes" ADD "assigned_user" UUID REFERENCES "users" ("id") ON DELETE SET NULL ON UPDATE CASCADE []
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query create _fluent_migrations input=[[id: 5176F3C5-4B71-4BF8-A159-2D256D83FDBE, created_at: 2022-02-22 09:57:38 +0000, name: "UpdateEnvelopeAssignedUser", batch: 1, updated_at: 2022-02-22 09:57:38 +0000]]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_migrations" ("id", "created_at", "name", "batch", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [5176F3C5-4B71-4BF8-A159-2D256D83FDBE, 2022-02-22 09:57:38 +0000, "UpdateEnvelopeAssignedUser", 1, 2022-02-22 09:57:38 +0000]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql SQLAlterEnum(name: SQLKit.SQLIdentifier(string: "task_type"), value: Optional(SQLKit.SQLLiteral.string("multipleChoice")))
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql CREATE TABLE IF NOT EXISTS "_fluent_enums"("id" UUID PRIMARY KEY, "name" TEXT NOT NULL, "case" TEXT NOT NULL, CONSTRAINT "uq:_fluent_enums.name+_fluent_enums.case" UNIQUE ("name", "case")) []
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query create _fluent_enums input=[[case: "singleChoice", id: 38FEEF01-0FC6-4955-A9FB-45322D8CEBA0, name: "task_type"], [id: 9AA05F25-2DD4-4E98-B0DF-A39704AF85AA, name: "task_type", case: "multipleChoice"]]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_enums" ("case", "id", "name") VALUES ($1, $2, $3), ($4, $5, $6) RETURNING "id" ["singleChoice", 38FEEF01-0FC6-4955-A9FB-45322D8CEBA0, "task_type", "multipleChoice", 9AA05F25-2DD4-4E98-B0DF-A39704AF85AA, "task_type"]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query delete _fluent_enums filters=[_fluent_enums[name] = "task_type", _fluent_enums[case] ~~ []]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql DELETE FROM "_fluent_enums" WHERE "_fluent_enums"."name" = $1 AND 1 = 0 ["task_type"]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql Connection pool exhausted on this event loop, adding request to waitlist
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql Fulfilling connection waitlist request
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query read _fluent_enums filters=[_fluent_enums[name] = "task_type"]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql SELECT "_fluent_enums"."id" AS "_fluent_enums_id", "_fluent_enums"."name" AS "_fluent_enums_name", "_fluent_enums"."case" AS "_fluent_enums_case" FROM "_fluent_enums" WHERE "_fluent_enums"."name" = $1 ["task_type"]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query create _fluent_migrations input=[[id: 09E5C7D9-52CE-4158-979C-7C0AF85F99C3, created_at: 2022-02-22 09:57:38 +0000, name: "AddChoiceAndTextTaskTypes", batch: 1, updated_at: 2022-02-22 09:57:38 +0000]]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_migrations" ("id", "created_at", "name", "batch", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [09E5C7D9-52CE-4158-979C-7C0AF85F99C3, 2022-02-22 09:57:38 +0000, "AddChoiceAndTextTaskTypes", 1, 2022-02-22 09:57:38 +0000]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql CREATE TABLE "ev_choice"("id" UUID PRIMARY KEY, "task_id" UUID REFERENCES "ev_tasks" ("id") ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, "title" TEXT NOT NULL, "text_label" TEXT, "text_required" BOOL NOT NULL, "order" BIGINT NOT NULL) []
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql query create _fluent_migrations input=[[updated_at: 2022-02-22 09:57:38 +0000, name: "CreateChoice", batch: 1, id: 8B5883C8-80F7-4B37-9680-5F6F214CA336, created_at: 2022-02-22 09:57:38 +0000]]
2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_migrations" ("updated_at", "name", "batch", "id", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [2022-02-22 09:57:38 +0000, "CreateChoice", 1, 8B5883C8-80F7-4B37-9680-5F6F214CA336, 2022-02-22 09:57:38 +0000]

trace:

2022-02-22T10:41:52+0100 trace codes.vapor.application : database-id=psql psql_connection_action=read psql_connection_id=C72FCB11-028F-4144-8D13-2A43DD282E39 Run action
2022-02-22T10:41:52+0100 debug codes.vapor.application : database-id=psql query create _fluent_migrations input=[[id: 12F63872-FC23-403A-8418-DF0D4A04A5D0, created_at: 2022-02-22 09:41:52 +0000, updated_at: 2022-02-22 09:41:52 +0000, batch: 1, name: "AddChoiceAndTextTaskTypes"]]
2022-02-22T10:41:52+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_migrations" ("id", "created_at", "updated_at", "batch", "name") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [12F63872-FC23-403A-8418-DF0D4A04A5D0, 2022-02-22 09:41:52 +0000, 2022-02-22 09:41:52 +0000, 1, "AddChoiceAndTextTaskTypes"]
2022-02-22T10:41:52+0100 trace codes.vapor.application : database-id=psql Re-using available connection
2022-02-22T10:41:52+0100 trace codes.vapor.application : database-id=psql psql_connection_id=C72FCB11-028F-4144-8D13-2A43DD282E39 psql_connection_state=ConnectionStateMachine(state: .extendedQuery(PostgresNIO.ExtendedQueryStateMachine(state: PostgresNIO.ExtendedQueryStateMachine.State.parseDescribeBindExecuteSyncSent(PostgresNIO.ExtendedQueryContext)), connectionContext: (processID: 1248, secretKey: 0000000, parameters: ["application_name": "", "session_authorization": "heim_db_username", "integer_datetimes": "on", "is_superuser": "on", "standard_conforming_strings": "on", "TimeZone": "Etc/UTC", "server_version": "13.5 (Debian 13.5-1.pgdg110+1)", "client_encoding": "UTF8", "IntervalStyle": "postgres", "DateStyle": "ISO, MDY", "server_encoding": "UTF8"])), taskQueue: [ _ _ _ _ _ _ _ _ _ <_ _ _ _ _ _ _ ] (bufferCapacity: 16, ringLength: 0), quiescingState: .notQuiescing) Connection state changed
2022-02-22T10:41:52+0100 trace codes.vapor.application : database-id=psql psql_connection_action=sendParseDescribeBindExecuteSync(query: "INSERT INTO \"_fluent_migrations\" (\"id\", \"created_at\", \"updated_at\", \"batch\", \"name\") VALUES ($1, $2, $3, $4, $5) RETURNING \"id\"", binds: [12F63872-FC23-403A-8418-DF0D4A04A5D0, 2022-02-22 09:41:52 +0000, 2022-02-22 09:41:52 +0000, 1, "AddChoiceAndTextTaskTypes"]) psql_connection_id=C72FCB11-028F-4144-8D13-2A43DD282E39 Run action
2022-02-22T10:41:52+0100 trace codes.vapor.application : database-id=psql psql_connection_id=C72FCB11-028F-4144-8D13-2A43DD282E39 psql_message=.parseComplete Backend message received

Just let me know if you need more info.

0xTim commented 2 years ago

@DavidWalter can you check the DB after the migration? It looks like both options should be added

2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql INSERT INTO "_fluent_enums" ("case", "id", "name") VALUES ($1, $2, $3), ($4, $5, $6) RETURNING "id" ["singleChoice", 38FEEF01-0FC6-4955-A9FB-45322D8CEBA0, "task_type", "multipleChoice", 9AA05F25-2DD4-4E98-B0DF-A39704AF85AA, "task_type"]
DavidWalter commented 2 years ago

Yes sure I checked and found something that perhaps helps:

They seem to be added to the _fluent_enums table: _fluent_enums

But on the pg_catalog/pg_enum table only the multipleChoice is added pg_catalog__pg_enum

Perhaps it needs another one of the SQLAlterEnum commands because it looks like in the debug log only the multipleChoice is done 2022-02-22T10:57:38+0100 debug codes.vapor.application : database-id=psql SQLAlterEnum(name: SQLKit.SQLIdentifier(string: "task_type"), value: Optional(SQLKit.SQLLiteral.string("multipleChoice")))

0xTim commented 2 years ago

Ah good spot - yep that's where the bug is. Thanks for reporting, we'll add this to the list to be fixed