Efcore.pg: Types not being reloaded properly after adding extension in migrations

[kusey]

Hi, I have installed the newest Npgsql.EntityFrameworkCore.PostgreSQL package in my project, and I added the 'hstore' extension like this:
modelBuilder.HasPostgresExtension("hstore");
The migration file content generated like this:
migrationBuilder.AlterDatabase().Annotation("Npgsql:PostgresExtension:hstore", "'hstore', '', ''");

After this, I have my own database initialize process in main function:
```c#
var host = BuildWebHost(args);

        var serviceProvider = host.Services;
        // initialize database
        using (var scope = serviceProvider.CreateScope())
        {
            var dbContext = scope.ServiceProvider.GetRequiredService<ISDbContext>();
            if (dbContext.Database.GetPendingMigrations().Any())
            {
                dbContext.Database.Migrate();
            }
            StorageInitializer.Initialize(dbContext, BuilderContext.HostingEnvironment);
        }

```

I invoke the Migrate() function in order to update my database automatically, after this, I checked that the extension 'hstore' already added to my database, but when I insert records to table(which contains a field with data type Dictionary) I got this exception: "NpgsqlException: The PostgreSQL type 'hstore', mapped to NpgsqlDbType 'Hstore' isn't present in your database. You may need to install an extension or upgrade to a newer version."

I'm not sure how to resolve this issue, can anybody help me?
Thanks.

[kusey]

In addition, if I do not invoke dbContext.Database.Migrate() function explicitly, just use common line: "dotnet ef database update" to update my database, everything is ok when inserting records to database.

[roji]

This is probably because after adding the extension in the database, Npgsql (the ADO layer) needs to reload the types to discover the newly-added hstore... This was supposed to be taken care of, but there may be an issue - I'll try to take a look soon.

The workaround is indeed to run the migration from a different process - if you simply restart your application everything should work fine.

[kusey]

Thanks for your reply.
If it's a bug I will find some workarounds temporary.

[myshon]

I create database in integration test, I use context.Database.Migrate() but it fails with same error. When I added ReloadTypes after database migration and it started working

using (var ctx = server.Host.Services.GetService<MyContext>())
using (var conn = new NpgsqlConnection(ctx.Database.GetDbConnection().ConnectionString))
{
   conn.Open();
   conn.ReloadTypes();
   conn.Close();
} 

[roji]

Oops, I ran into this today and fixed it as #313, forgetting that this issue was open.

Can you please test version 2.0.2-166 from the stable feed and confirm that the problem goes away?

[myshon]

Pulled 2.0.2-166 and unfortunetelty still not works, but an error is different, without explicit ReloadTypes I have:

System.NotSupportedException: The field 'Code' has a type currently unknown to Npgsql (OID 22894). You can retrieve it as a string by marking it as unknown, please see the FAQ

[roji]

@myshon what's the type of Code? Any chance you could post a small code sample reproducing the issue? Things seem to work well on my end.

[myshon]

@roji I use citext extension:
modelBuilder.HasPostgresExtension("citext");
Code has type citext
builder.Property(x => x.Code).HasColumnType("citext");
Give me some time, I will try to reproduce it later.

[roji]

Great, thanks - a simple small context with some minimal code would be ideal.

[kusey]

@roji I still got the same error using pkg 2.0.2-ci-00166.

[roji]

@kusey as I wrote above, a small code sample would be very helpful here. In my scenario it's OK, and I don't know what you're doing differently.

[kusey]

@roji Here is my code, executing pending migrations when website startup:
c# var host = BuildWebHost(args); var serviceProvider = host.Services; // initialize database using (var scope = serviceProvider.CreateScope()) { var dbContext = scope.ServiceProvider.GetRequiredService<ISDbContext>(); if (dbContext.Database.GetPendingMigrations().Any()) { dbContext.Database.Migrate(); } StorageInitializer.Initialize(dbContext); }

and the database initialize code like this:

```c#
if (!dbContext.Set().Any())
{
dbContext.AddRange(entities);
dbContext.SaveChanges();
}
````

Sorry for the late reply, i'm going crazy that github always show error message 'You can't comment at this time'.

[roji]

Ah, that makes sense, I'll look into this for 2.1.

[roji]

This affects applications applying migrations programmatically (i.e. not from the command line) which install new extensions (which create new types).

In a nutshell, the first time Npgsql (the ADO provider, not the EF Core) connects to a database, it loads all the type definitions and caches it for all subsequent connections to that database. If new types are created - either manually or via an extension - the cache doesn't contain them and Npgsql must be told to reload type definitions via NpgsqlConnection.ReloadTypes().

In the EF Core provider, when the database creator is used (ctx.Database.EnsureCreated()), there's already code which detects migrations which add extensions, and executes ReloadTypes(). However, the same thing doesn't happen when migrating (ctx.Database.Migrate()).

Providers are expected (and in fact required) to have their own DatabaseCreator, which is where the former check is done. However, they are not supposed to have their own migrator (the component which executes migrations) - the Migrator class is internal. Because of that, and because this is a bit of an edge case (programmatic migration + PostgreSQL extensions) I prefer for applications to handle this themselves by including a call to NpgsqlConnection.ReloadType() after applying new migrations.

```c#
var dbContext = scope.ServiceProvider.GetRequiredService();
if (dbContext.Database.GetPendingMigrations().Any())
{
dbContext.Database.Migrate();

dbContext.Database.OpenConnection();
((NpgsqlConnect)dbContext.GetDbConnection()).ReloadTypes();

}
```

However, if lots of people run into this I'll consider handling it within the provider.

[kusey]

((NpgsqlConnection)dbContext.Database.GetDbConnection()).ReloadTypes();
Yes, this works fine.

[roji]

OK great, let's leave it this way for now, unless lots of people encounter this.

[idudinov]

((NpgsqlConnection)dbContext.Database.GetDbConnection()).ReloadTypes();

Thanks, it fully helped me with exception like this:

Npgsql.NpgsqlException: The PostgreSQL type 'hstore', mapped to NpgsqlDbType 'Hstore' isn't present in your database. You may need to install an extension or upgrade to a newer version.

in case I had 'hstore' extension actually installed and using dbContext.Database.Migrate();

[myshon]

Verified in 2.1.1.1 and bug still exists. Npgsql.NpgsqlException: The NpgsqlDbType 'Citext' isn't present in your database. You may need to install an extension or upgrade to a newer version.

[roji]

Reopening as many people seem to be running into this.

[dharmaturtle]

I have automated integration tests that hit the database. The database for each test is optionally created and deleted before and after each run. If I opt into having the database created and deleted, the test fails as shown below. (The code is in F#, but I think it's readable. LMK if any syntax is confusing.)

There's another mode where the db is created if it doesn't exist, and then the test must manually clean up after itself by deleting any added records. (Runs significantly faster.)

The .ReloadTypes() and MapEnum call don't seem to be working for me in the screenshot above, which is running in "delete then create new database mode". However, it _does_ work in "don't delete the database mode." It's not a showstopper, but it's annoying. Any advice?

[roji]

@dharmaturtle for tests it's common to use Database.EnsureCreated rather than Migrate, and types should be properly reloaded.

[dharmaturtle]

@roji I'm not using migrations or Database.EnsureCreated, I should have specified that. I'm posting here because I think my error is related to everyone else's. I export my schema to a file using pgdump, then tests use it to create their isolated databases. This is faster than EnsureCreated by about an order of magnitude, and with other optimizations, I have it down to under a second to get a clean, isolated database.

Anyway, I moved the ReloadTypes literally right before the call that throws by overriding db.SaveChanges():

I'm not sure if I'm using MapEnum correctly, but it's also in the static constructor, which works as expected if the database exists when the test runs. If it doesn't exist, the test creates a db, which leads to my issue because the process's type cache is out of date.

In order to create the db if it doesn't exist, I first connect to "Host=localhost;Username=postgres;" to run CREATE DATABASE. Is it possible that is messing with my backend type cache somehow? The test dbs use connection strings like Host=localhost;Username=postgres;Database=ztest_sut_condition_assertion;

Below is my stacktrace for the screenshot above below, just in case.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at CardOverflow.Entity.CardOverflowDb.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) in C:\Code\Personal\CardOverflow\CardOverflow.Entity\CardOverflowDbOverride.cs:line 99
   at FSharp.Control.Tasks.TaskBuilder.bindTask[a,b](Task`1 task, FSharpFunc`2 continuation) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 119
   at [email protected](Unit unitVar) in C:\Code\Personal\CardOverflow\CardOverflow.Api\Helpers.fs:line 14
   at FSharp.Control.Tasks.TaskBuilder.run[a](FSharpFunc`2 firstStep) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 226
--- End of stack trace from previous location where exception was thrown ---
   at FSharp.Control.Tasks.TaskBuilder.bindTask[a,b](Task`1 task, FSharpFunc`2 continuation) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 117
   at [email protected](CardOverflowDb _arg1) in C:\Code\Personal\CardOverflow\CardOverflow.Test\NotificationRepositoryTests.fs:line 62
   at FSharp.Control.Tasks.TaskBuilder.tryFinally[a](FSharpFunc`2 step, FSharpFunc`2 fin) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 182
   at FSharp.Control.Tasks.TaskBuilder.using[a,a](a disp, FSharpFunc`2 body) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 212
   at [email protected](Unit unitVar) in C:\Code\Personal\CardOverflow\CardOverflow.Test\NotificationRepositoryTests.fs:line 49
   at FSharp.Control.Tasks.TaskBuilder.run[a](FSharpFunc`2 firstStep) in C:\Users\humbo\source\repos\TaskBuilder.fs\TaskBuilder.fs:line 226
--- End of stack trace from previous location where exception was thrown ---
   at NotificationRepositoryTests.NotificationTests.Can insert and retrieve notifications(NotificationEntity notification) in C:\Code\Personal\CardOverflow\CardOverflow.Test\NotificationRepositoryTests.fs:line 48
--- End of stack trace from previous location where exception was thrown ---
   at [email protected](Object[] o)
   at <StartupCode$FSharp-Core>[email protected](T1 inp) in E:\A\_work\130\s\src\fsharp\FSharp.Core\reflect.fs:line 776
   at FsCheck.Testable.evaluate[a,b](FSharpFunc`2 body, a a)
System.NotSupportedException: The CLR enum type NotificationType must be registered with Npgsql before usage, please refer to the documentation.
   at Npgsql.TypeMapping.ConnectorTypeMapper.GetByClrType(Type type) in C:\projects\npgsql\src\Npgsql\TypeMapping\ConnectorTypeMapper.cs:line 122
   at Npgsql.NpgsqlParameter.ResolveHandler(ConnectorTypeMapper typeMapper) in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 490
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 782
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1111
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1061
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

[roji]

@dharmaturtle you only need to call ReloadTypes after adding your type to the database - not after calling the MapEnum method. I recommend you apply your pgdump file at the very start of all tests, and immediately call ReloadTypes after that - preferably not in a connection that's also being used by EF Core (as in your above sample).

If you're still having issues, it may be better to just post a minimal runnable code sample, it's difficult to get context on what exactly you're doing.

PS Exporting a database with pgdump may allow for faster test startup, but you need to manually keep that file in sync, which isn't ideal. It sounds like you're recreating your database schema for every test - if that's the case consider not doing that, and simply cleaning it between tests (resetting it back to the initial state, see these docs). That would allow you to simply use EnsureCreated without messing with any SQL scripts.

[dharmaturtle]

@roji That worked, thanks so much!

Related F# for posterity's sake:

I've written a PowerShell script to keep that file in sync (called InitializeDatabase.sql in the screenshot above). I agree it isn't idiomatic, but to me ideal is fast tests :)

I've not considered sharing databases between tests, thanks for the link! It looks quite interesting.