Enum/Types are not removed when reverted

[mootensai]

Steps to reproduce

I follow guide from here
https://www.npgsql.org/efcore/mapping/enum.html?tabs=tabid-1

    public MyContext()
    {
        NpgsqlConnection.GlobalTypeMapper.MapEnum<Role>();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresEnum<Role>();
    }

public enum Role
{
    Role1,

    Role2,

    Role3,

    Role4,
}

The issue

it works well when running the migrations, but when I revert the migrations & re-run the migrations, this exception arises

Npgsql.PostgresException (0x80004005): 42710: type "role" already exists
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42710
    MessageText: type "role" already exists
    File: typecmds.c
    Line: 1172
    Routine: DefineEnum

Further technical details

Npgsql version: 6.0.5
PostgreSQL version: 14
Operating system: MacOS 12.4 (21F79) Monterey

[roji]

(transferring to EFCore.PG repo)

Sorry for not answering this earlier.

it works well when running the migrations, but when I revert the migrations & re-run the migrations, this exception arises

I've done a quick test where I add an enum column with a new enum type to an existing table. I can see that the Up SQL script creates the type (CREATE TYPE role AS ENUM ('role1', 'role2', 'role3', 'role4');), and that the Down script properly drops it (DROP TYPE role;).

I'm not sure what exactly you're doing in order to "revert the migrations" - are you sure you've properly executed the Down script? If you've just deleted the migration, that leaves your database in the previous state (with the enum) - that's the expected behavior.

[roji]

Closing as no response was provided.

[iPilot]

I'm encountering the same problem. More details:

    public enum PaymentNetwork
    {
        Mastercard = 1,
        Visa = 2,
        Discover = 3,
        AmericanExpress = 4
    }

            modelBuilder.HasPostgresEnum<PaymentNetwork>(name: nameof(PaymentNetwork), nameTranslator: new NpgsqlNullNameTranslator());

Migration contains, as expected, a command:

CREATE TYPE "PaymentNetwork" AS ENUM ('Mastercard', 'Visa', 'Discover', 'AmericanExpress');

But neither migration Down nor script do not contain any instructions for created type removing, so migration reapply after reverting leads to exception "type is already exist". Enum is used by the only column of the only new table and down migration contains drop command for that table.

[roji]

@iPilot in my previous comment I tried this and couldn't see a problem; I'll need a proper repro in order to investigate, including the precise steps, versions, etc.

[iPilot]

@iPilot in my previous comment I tried this and couldn't see a problem; I'll need a proper repro in order to investigate, including the precise steps, versions, etc.

Sure, I've a made a repro: https://github.com/iPilot/NpgSqlEnumBug

[iPilot]

It looks like the bug appears only on the first migration for the project.

[md-redwan-hossain]

Facing the same issue.In the Down method, the enums are not removed.

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "ProductPhoto");

    migrationBuilder.DropTable(
        name: "Products");
}

Failed executing DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TYPE gender AS ENUM ('male', 'female', 'others');
CREATE TYPE image_file_extension AS ENUM ('png', 'jpg', 'jpeg', 'webp');
Npgsql.PostgresException (0x80004005): 42710: type "gender" already exists

[pinkfloydx33]

Experiencing the same thing. In our case it's the first migration in the project that includes the enum type and it's not dropped in the corresponding down script

[roji]

Duplicate of dotnet/efcore#35016

[roji]

Note that this is the same as #3183, where dotnet/efcore#35016 manifests via collations.