npgsql: Misleading error message when type registration is missing for NpgsqlDbType parameters (error says check the database, but the problem is in type registration in the ADO.net level)

Steps to reproduce

create database something;
create extension postgis;
create tablespace
alter database set tablespace new
alter database set tablespace pg_default;
Launch project that connects to this database.

The issue

Exception message:
Npgsql.NpgsqlException (0x80004005): The NpgsqlDbType 'Geography' isn't present in your database. You may need to install an extension or upgrade to a newer version.

Stack trace:
 at Npgsql.TypeMapping.TypeMapper.ResolveByNpgsqlDbType(NpgsqlDbType npgsqlDbType)
   at Npgsql.NpgsqlParameter.ResolveHandler(TypeMapper typeMapper)
   at Npgsql.NpgsqlBinaryImporter.Write[T](T value, NpgsqlParameter param, Boolean async, CancellationToken cancellationToken)

Further technical details

Npgsql version: 7.0.0 PostgreSQL version: 15.1 with postgis Operating system: Windows 11

Other details about my project setup: The code that triggers this is a script which uses the binary importer to bulk load some data. This was working fine before, the only thing that changed between it working and not working is changing the tablespace then changing it back to pg_default.

\dT geography: List of data types ±-------±----------±-----------------------------------------------------------------------------------------------------+ | Schema | Name | Description | ±-------±----------±-----------------------------------------------------------------------------------------------------+ | public | geography | postgis type: The type representing spatial features with geodetic (ellipsoidal) coordinate systems. | ±-------±----------±-----------------------------------------------------------------------------------------------------+ (1 row)

I’m confused, I have no idea what is wrong or why/how did this break.

CREATE EXTENSION postgis: ERROR: 42710: extension “postgis” already exists LOCATION: CreateExtension, extension.c:1729 Time: 2.002 ms

alter extension postgis update; NOTICE: 00000: version “3.3.2” of extension “postgis” is already installed LOCATION: ExecAlterExtensionStmt, extension.c:3006 ALTER EXTENSION

I feel like I’m getting gaslit. If I try to drop the extension, it says objects depend on it. It’s definitely 100% there. The connection string in my app was not touched.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 19 (13 by maintainers)

Most upvoted comments

I think I’m getting the same error (with Npgsql 7.0.1), which I was trying to solve like stated in this issue: https://github.com/npgsql/efcore.pg/issues/292. It is occurring because I have a specific scenario (but I’m not sure how to workaround it).

What I need to do:

Drop the database (if exists);
Create (the same) the database;
Apply every migration to it;
Save data to it;

In my scenario, the error (not finding the “geography” type from “postgis”, which is being defined in model builder with “HasPostgresExtension(“postgis”)”) only occurs when data is saved to the database (using “context.SaveChangesAsync()” after setting an entity that has a “geography” column). And it only happens if the database is dropped before (because when I run a second time without dropping it, the migration is already there and everything works as desired).

One thing that I’m not sure is beging done correctly is the global types registration. As I’m using DI in .NET, I need to register everything using AddDbContext or AddPooledDbContextFactory. But when I register it using a NpgsqlDataSource instance (instead of connectionString directly), it does not work (it seems that extensions like “UseNetTopologSuite()” does not get registered right if is not used in “UseNpgsql()” options). So I’m using this way:

        NpgsqlConnection.GlobalTypeMapper
            .MapEnum<DayOfWeek>()
            .MapEnum<FuelType>();

        var connectionString = configuration.GetConnectionString(nameof(ApplicationDbContext));

        services.AddPooledDbContextFactory<ApplicationDbContext>(options => options
            .UseLazyLoadingProxies()
            .UseNpgsql(connectionString, options => options
                .EnableRetryOnFailure()
                .MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)
                .MigrationsHistoryTable("migrations")
                .UseNetTopologySuite()
                .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery))
            .UseSnakeCaseNamingConvention());

Another point: I missed, for version 7.0, more examples of using NpgsqlDataSource with DI (that is why I’m using this way, statically, which was the way it worked for me). If I can solve this problems that I’m having in my scenario and understand what I’m doing wrong, I could help and submit a PR to improve the documentation for DI (if needed/encouraged).

Thanks in advance for all the help and attention!

luanmm on Jan 4, 2023

Sure, we can tweak the message.

roji on Nov 30, 2022