Context

I am developing an Npgsql compatibility plugin for Babelfish for PostgreSQL. This requires globally remapping standard .NET CLR types (like System.Guid) to their equivalent, non-standard, schema-qualified Babelfish types (like sys.uniqueidentifier). I will be using Guid as an example for the whole issue

When manually setting NpgsqlParameter.DataTypeName = "sys.uniqueidentifier", my custom IPgTypeInfoResolver is correctly utilized, and the data is written successfully. The challenge lies in making this mapping automatic and global for parameter inference.

Roadblock 1: ORM Inference and Bypass

My application makes use of Dapper. When creating parameters for known CLR types Dapper infers the type of the CLR value and sets the corresponding base ADO.NET type, DbType.Guid, on the parameter object before passing it to Npgsql.

I attempted to workaround this by creating an custom TypeHandler<Guid> where I could set the correct DataTypeName, but for primitive types Dapper the automatic inference from Dapper takes precedence and the registered TypeHandler<Guid> is not called.
But even if TypeHandler<Guid> worked, relying on it would introduce massive boilerplate (needing a handler for every type, nullable, and array variant) that defeats the purpose of centralized type mapping.

I think the ideal solution would be within the Npgsql.

Roadblock 2: The Npgsql Internal Validation Constraint

When Dapper passes a parameter with only DbType.Guid, Npgsql's internal resolution chain runs:

1. Npgsql Infers: Npgsql's default primitive mapping infers the target internal PgTypeId corresponding to pg_catalog.uuid.

2. Custom Resolver Runs: My resolver runs and returns a PgTypeInfo associated with the type ID for sys.uniqueidentifier ().

3. Validation Failure: The resolution process then hits the strict validation check in TypeInfoCache.CreateInfo:
   

   npgsql/src/Npgsql/Internal/TypeInfoCache.cs

   Line 122 in 7042543

   if (pgTypeId is not null && info.PgTypeId != pgTypeId)

This validation enforces that the resolved type info must have the same internal PgTypeId as the one initially determined (the built-in uuid's ID). Since our goal is to swap the default PgTypeId (uuid) for our custom one sys.uniqueidentifier while keeping the CLR type the same, this check throws an exception, rendering global type overrides for primitives impossible, causing all my queries to fail with the following error:

Writing values of 'System.Guid' is not supported for parameters having NpgsqlDbType 'Uuid'

Proposed solution

The internal validation logic could be modified to check if the PgTypeInfo came from a custom resolver attempting to define the default mapping for that CLR type. If so, the validation should be skipped, allowing the resolved info.PgTypeId to effectively replace the default.