efcore.pg: Types not being reloaded properly after adding extension and running Migrate()

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.

var dbContext = scope.ServiceProvider.GetRequiredService<ISDbContext>();
if (dbContext.Database.GetPendingMigrations().Any())
{
    dbContext.Database.Migrate();

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

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

Reopening as many people seem to be running into this.

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

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();
} 

@marekott that sounds similar to https://github.com/npgsql/npgsql/issues/4153, where load balancing is done against two servers with OID type discrepancies. ReloadTypes should indeed help out with this.

@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.

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.

((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();