search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.54k stars 225 forks source link

After upgrading to EF Core 6 - 42704: type "tstzmultirange" does not exist #2098

Closed BenjiFarquhar closed 2 years ago

BenjiFarquhar commented 2 years ago

I have this database field:

public List<NpgsqlRange<System.DateTime>> Dates { get; set; }

But today I have upgraded EF Core from 5 to 6.

Which caused an error to appear about the above field:

42704: type "tstzmultirange" does not exist

I just confirmed that downgrading EF Core back to 5 from 6 completely fixes this issue. So I guess EF Core 6 has a bug for this specific field type.

Full error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "VeganItems" (
          "Id" integer GENERATED BY DEFAULT AS IDENTITY,
          "Name" character varying(256) NOT NULL,
          "CompanyName" character varying(256) NOT NULL,
          "Description" character varying(256) NULL,
          "IsNotVeganCount" integer NOT NULL,
          "IsVeganCount" integer NOT NULL,
          "RatingsCount" integer NOT NULL,
          "Rating" integer NOT NULL,
          "Tags" text NULL,
          "Images" text[] NULL,
          "Dates" tstzmultirange NULL,
          "Age" text NULL,
          "Gender" text NULL,
          "ServesCount" integer NULL,
          "CookTime" interval NULL,
          "PrepTime" interval NULL,
          "Difficulty" text NULL,
          "Method" text[] NULL,
          "Tips" text[] NULL,
          "Ingredients" text[] NULL,
          "CreatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
          "UpdatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
          "CreatedById" character varying(256) NULL,
          "UpdatedById" character varying(256) NULL,
          "Discriminator" character varying(256) NOT NULL,
          CONSTRAINT "PK_VeganItems" PRIMARY KEY ("Id"),
          CONSTRAINT "FK_VeganItems_User_CreatedById" FOREIGN KEY ("CreatedById") REFERENCES "User" ("Id"),
          CONSTRAINT "FK_VeganItems_User_UpdatedById" FOREIGN KEY ("UpdatedById") REFERENCES "User" ("Id")
      );
Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "VeganItems" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(256) NOT NULL,
    "CompanyName" character varying(256) NOT NULL,
    "Description" character varying(256) NULL,
    "IsNotVeganCount" integer NOT NULL,
    "IsVeganCount" integer NOT NULL,
    "RatingsCount" integer NOT NULL,
    "Rating" integer NOT NULL,
    "Tags" text NULL,
    "Images" text[] NULL,
    "Dates" tstzmultirange NULL,
    "Age" text NULL,
    "Gender" text NULL,
    "ServesCount" integer NULL,
    "CookTime" interval NULL,
    "PrepTime" interval NULL,
    "Difficulty" text NULL,
    "Method" text[] NULL,
    "Tips" text[] NULL,
    "Ingredients" text[] NULL,
    "CreatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    "UpdatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    "CreatedById" character varying(256) NULL,
    "UpdatedById" character varying(256) NULL,
    "Discriminator" character varying(256) NOT NULL,
    CONSTRAINT "PK_VeganItems" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_VeganItems_User_CreatedById" FOREIGN KEY ("CreatedById") REFERENCES "User" ("Id"),
    CONSTRAINT "FK_VeganItems_User_UpdatedById" FOREIGN KEY ("UpdatedById") REFERENCES "User" ("Id")
);
fail: 14/11/2021 13:39:53.723 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "VeganItems" (
          "Id" integer GENERATED BY DEFAULT AS IDENTITY,
          "Name" character varying(256) NOT NULL,
          "CompanyName" character varying(256) NOT NULL,
          "Description" character varying(256) NULL,
          "IsNotVeganCount" integer NOT NULL,
          "IsVeganCount" integer NOT NULL,
          "RatingsCount" integer NOT NULL,
          "Rating" integer NOT NULL,
          "Tags" text NULL,
          "Images" text[] NULL,
          "Dates" tstzmultirange NULL,
          "Age" text NULL,
          "Gender" text NULL,
          "ServesCount" integer NULL,
          "CookTime" interval NULL,
          "PrepTime" interval NULL,
          "Difficulty" text NULL,
          "Method" text[] NULL,
          "Tips" text[] NULL,
          "Ingredients" text[] NULL,
          "CreatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
          "UpdatedDate" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
          "CreatedById" character varying(256) NULL,
          "UpdatedById" character varying(256) NULL,
          "Discriminator" character varying(256) NOT NULL,
          CONSTRAINT "PK_VeganItems" PRIMARY KEY ("Id"),
          CONSTRAINT "FK_VeganItems_User_CreatedById" FOREIGN KEY ("CreatedById") REFERENCES "User" ("Id"),
          CONSTRAINT "FK_VeganItems_User_UpdatedById" FOREIGN KEY ("UpdatedById") REFERENCES "User" ("Id")
      );
dbug: 14/11/2021 13:39:53.729 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Disposing transaction.
dbug: 14/11/2021 13:39:53.730 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closing connection to database 'vepo_dev_db' on server 'tcp://localhost:5432'.
dbug: 14/11/2021 13:39:53.730 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closed connection to database 'vepo_dev_db' on server ''.
dbug: 14/11/2021 13:39:53.732 CoreEventId.ContextDisposed[10407] (Microsoft.EntityFrameworkCore.Infrastructure) 
      'VepoContext' disposed.
Npgsql.PostgresException (0x80004005): 42704: type "tstzmultirange" does not exist

POSITION: 427
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_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: 42704
    MessageText: type "tstzmultirange" does not exist
    Position: 427
    File: parse_type.c
    Line: 275
    Routine: typenameType
42704: type "tstzmultirange" does not exist

POSITION: 427
roji commented 2 years ago

@BenjaminFarquhar PostgreSQL 14 introduced a new multirange type, which is very similar to an array of ranges but supports various range-related operations efficiently. Npgsql 6 maps List<NpgsqlRange<T>> to these new types by default - that indeed doesn't work on PG versions before 13.

You can explicitly tell the provider to map to old-style arrays over ranges, e.g. via [Column(TypeName="tzrange[]")], but if upgrading to PG14 is option, I'd suggest doing that instead and switching to the new multirange type.

I'll add a breaking change note in the release notes.

roji commented 2 years ago

See https://github.com/npgsql/doc/pull/149

BenjiFarquhar commented 2 years ago

@BenjaminFarquhar PostgreSQL 14 introduced a new multirange type, which is very similar to an array of ranges but supports various range-related operations efficiently. Npgsql 6 maps List<NpgsqlRange<T>> to these new types by default - that indeed doesn't work on PG versions before 13.

You can explicitly tell the provider to map to old-style arrays over ranges, e.g. via [Column(TypeName="tzrange[]")], but if upgrading to PG14 is option, I'd suggest doing that instead and switching to the new multirange type.

I'll add a breaking change note in the release notes.

@roji Thanks, that's good to know. I updated to PostgreSQL 14 and that made it work!

roji commented 2 years ago

Great! Let me know if you run into any other trouble!

BenjiFarquhar commented 2 years ago

Great! Let me know if you run into any other trouble!

@roji Actually, I'm getting this:

System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.

When I try to save this field to the db: public List<NpgsqlRange<System.DateTime>> Dates { get; set; }

I think the bottom of the release notes talk about this.

What is the fix? It mentions to do: [Column(TypeName = "int4range[]")] (I assume translating my code to that would actually be [Column(TypeName = "daterange[]")]?) but it kinda sounds like it is saying that that is how you force it to use the old postgreSQL way of doing it?

The problem code may be here where I map the dto to the db entity type:

            CreateMap<EventItemDto, EventItem>()
                .ForMember(dest => dest.Dates,
                           opt => opt.MapFrom(src => src.Dates.Select(
                               date => new NpgsqlRange<System.DateTime>(
                                   date.StartDate, 
                                   date.EndDate
                                )
                            )
                    .ToList()));

Okay wait the solution was to add the time zone, makes sense:

            CreateMap<EventItemDto, EventItem>()
                .ForMember(dest => dest.Dates,
                           opt => opt.MapFrom(src => src.Dates.Select(
                               date => new NpgsqlRange<System.DateTime>(
                                   date.StartDate.ToUniversalTime(), 
                                   date.EndDate.ToUniversalTime()
                                )
                            )
                    .ToList()));
roji commented 2 years ago

Yeah. Npgsql 6 clearly enforces the difference between PostgreSQL timestamptz (which is a UTC timestamp) and timestamp which is a timestamp in an unspecified/unknown time zone). If your intention is to store UTC timestamps in the database, you should be using the tstzmultirange type (multirange over timestamptz), and then you must make sure that all DateTimes you write have Kind=Utc; this is why ToUniversalTime makes things work.

I recommend giving this blog post a read, and possibly also this one, in case you're interested in non-UTC timestamps (it's important to think about why you're choosing UTC or not UTC, and not just do UTC without knowing why). I also really recommend giving NodaTime a look - this is a far better way of working with timestamps and date/time data in .NET.