Can't manage to map the custom pguint type

[EvgkvOutlook]

Steps to reproduce

A simple function that reads a row from the plain table with custom field type:

try
{
    using var connection = new NpgsqlConnection(someConnectionString);

    connection.Open();

    var cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT nd FROM gg;"; 

    using var reader = cmd.ExecuteReader();

    reader.Read();

    var row = (uint)reader[0]; //<-- exception is here
}
catch (Exception exc)
{
    Console.WriteLine(exc.Message);
}

The issue

The database uses a custom type provided by the pguint extension (https://github.com/petere/pguint).
The specific field type is uint4.

1. SQL script to recreate the environment:

CREATE EXTENSION uint;

That thing registers a set of custom types.
Then we make a target table with the one of these types.

CREATE TABLE gg (nd uint4);
INSERT INTO gg (nd) VALUES (255);

2. Create a connection and try to read the data (the listing above).
3. Got Exception:

Root Exception:

Exception message: "Reading as 'System.Object' is not supported for fields having DataTypeName 'public.uint4'"
Stack trace:
   at Npgsql.Internal.AdoSerializerHelpers.<GetTypeInfoForReading>g__ThrowReadingNotSupported|0_0(Type type, String displayName, Exception inner)
   at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForReading(Type type, PostgresType postgresType, PgSerializerOptions options)
   at Npgsql.BackendMessages.FieldDescription.<GetInfo>g__GetInfoSlow|50_0(Type type, ColumnInfo& lastColumnInfo)
   at Npgsql.BackendMessages.FieldDescription.GetInfo(Type type, ColumnInfo& lastColumnInfo)
   at Npgsql.BackendMessages.FieldDescription.get_ObjectOrDefaultInfo()
   at Npgsql.NpgsqlDataReader.GetDefaultInfo(Int32 ordinal, PgConverter& converter, Size& bufferRequirement)
   at Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)
   at Npgsql.NpgsqlDataReader.get_Item(Int32 ordinal)

Inner Exception:

Exception message: "Composite mapping must be to a composite type"
Data: {System.Collections.ListDictionaryInternal}
Stack trace:
   at Npgsql.Internal.AdoSerializerHelpers.<GetTypeInfoForReading>g__ThrowReadingNotSupported|0_0(Type type, String displayName, Exception inner)
   at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForReading(Type type, PostgresType postgresType, PgSerializerOptions options)
   at Npgsql.BackendMessages.FieldDescription.<GetInfo>g__GetInfoSlow|50_0(Type type, ColumnInfo& lastColumnInfo)
   at Npgsql.BackendMessages.FieldDescription.GetInfo(Type type, ColumnInfo& lastColumnInfo)
   at Npgsql.BackendMessages.FieldDescription.get_ObjectOrDefaultInfo()
   at Npgsql.NpgsqlDataReader.GetDefaultInfo(Int32 ordinal, PgConverter& converter, Size& bufferRequirement)
   at Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)
   at Npgsql.NpgsqlDataReader.get_Item(Int32 ordinal)

pguint is C-based extension and does not create any composite type.

I also tried to use NpgsqlDataSourceBuilder, but that ends up the same way:

    var dataSourceBuilder = new NpgsqlDataSourceBuilder(someConnectionString);
    dataSourceBuilder.EnableUnmappedTypes();

    var dataSource = dataSourceBuilder.Build();
    var connection = dataSource.OpenConnection();
    ... etc

Have you any suggestions and/or hints?

I've started using PostgreSQL not so long ago. Maybe I'm missing some obvious things.

Further technical details

Npgsql version: 8.0.2
PostgreSQL version: 16
Operating system: Linux (Ubuntu)

Other details about my project setup:
.NET 7

[roji]

To handle custom types, you need to implement a type converter - which will handle encoding and decoding the PostgreSQL binary representation received over the network to .NET uint. This should be quite easy for this kind of type - the converter can then be released as a nuget package for others to use.

The alternative is to transfer the value as a string and parse it yourself.

[EvgkvOutlook]

@roji

uint4 is the base type.

All I've found about Npgsql mapping are referenced to the Composite or Enum types (NpgsqlConnection.GlobalTypeMapper (MapComposite, MapEnum)).

I didn't find any public Npgsql interface which I can use to make own Type converter.

Could you provide an example or a link to one (for a base type converter), or share any tips on implementing a type converter?

[roji]

Yeah, contrary to composite/enum types, supporting a new base type involves implementing a type converter.

So we already have UInt32Converter internally, since various internal types are stored as uints (oid, xid...). This converter should probably work for this type as well - it's mostly a matter of wiring it up correctly; for that, you can take a look at how the NodaTime plugin adds a resolver factory, and follow that example to register UInt32Converter etc.

[NinoFloris]

Unfortunately this extension does not have binary support yet, it's tracked in petere/pguint#23. We can't easily add support for it in Npgsql when this is missing.

@EvgkvOutlook any particular reason the builtin int2/4/8 types aren't enough? If you just want to prevent negative values you could decide to define some domain types for them as described in https://www.postgresql.org/docs/current/domains.html

These will be automatically supported by Npgsql if the underlying type is.

[lsoft]

@NinoFloris @roji like @EvgkvOutlook, I am also interested in supporting pguint in npgsql. In my case we want to keep database as smaller as possible and want to use uint1 (byte, tinyint) for some columns in a really big tables (1e9 rows-scale).

So, DB size is in our focus, not "unsignedness".

Could you share any ideas how to support these types in npgsql without petere/pguint#23 ? is it possible at all? I'm ready to provide PR for (or fork of) npgsql (if this will be possible for me).

[NinoFloris]

It won't really be possible no, we currently don't have support for creating arrays, ranges or multiranges over textual format types. Our infrastructural support for the text format in general is quite new, mostly unused, and still needs tweaks.

Your best bet would be to get that PR merged.

[EvgkvOutlook]

@roji Thank you! I'll try out on your tip.

[roji]

@EvgkvOutlook I'm not sure which tip you're referring to there - if it's the one about UInt32Converter, then as @NinoFloris wrote above, that works only once binary support is added to the uint4 type in the PostgreSQL extension.

[EvgkvOutlook]

@roji You're right. I took commit from PR which is noted as "Added (and tested) support for uint4, unit8" PR24. I recompiled it and successfully installed extension in PG with binary support. It is enough for exeriments and test purposes.

[NinoFloris]

Any update @EvgkvOutlook? Do you consider the issue solved from the npgsql side of things?

[EvgkvOutlook]

@NinoFloris I ask you to keep the issue open for now: we are conversating with the developer of the pguint extension to bring it to an official state. And I haven't finished some tests yet.