Improve documentation on composite types

[ericmutta]

The npgsql docs briefly mention that you can map CLR classes to composite types in Postgres. But since no example class is given, it is not clear how the mapping works (i.e. what scenarios can or can't be handled). For example, looking at the CompositeHandler.cs code seems to indicate npgsql can map classes with or without explicit constructors...is that correct?

Generally, it would be useful to document the following mapping issues:

1. What happens if the CLR class has a member that is not in the Postgres type (and vice versa)?
2. Can we mark some CLR class members as excluded so they don't partake in the mapping?
3. Must the CLR type be a class or are structs also supported? The code hints that structs are supported.
4. If constructors with parameters are allowed, is there a limit on number of parameters? The code hints at 8 parameters max.

I imagine, automatic mapping between CLR classes and Postgres composite types is a huge selling point for many people. I personally would love to know what is or isn't possible so I can take full advantage of this feature. I would also like to know if it is possible to extend/customize the npgsql type handler facility for composite types so I can make up my own rules for mapping.

PS: Postgres is truly amazing and having npgsql so us .NET folks can use Postgres is really cool. My thanks to all who contribute! 🙏

[roji]

Thanks, it's true that the docs are a bit light - I'll try to find time to improve them later this year, when the 7.0 work quiets down. In the meantime, I advise looking at the tests to see what's possible or not, and simply to trying things out in your code.

Extending/customizing the type handler probably wouldn't be workable; you can generally add type handlers for specific PG types, but composite types are quite different (not just another base type), and it would likely require various changes in the core. You can certainly try though, and we'd accept PRs to Npgsql itself to improve its composite type support.

(and thanks for the kind words!)

[ericmutta]

Thanks @roji for the prompt follow up as always!

I've spent a couple of hours reading the source and here is a brain dump of what I have gleaned so far (I am sharing in the hopes that it may help others and serve as a reference point when updating the docs - but I urge anyone reading this to TEST the observations for themselves).

The following files contain the interesting code:

• CompositeTests.cs is where all the tests live.
• CompositeHandler.cs contains the functions to read and write composite values.
• CompositeConstructorHandler.cs handles deserializing Postgres composites to a CLR type with a constructor.
• CompositeConstructorHandler`.cs like above, but for constructors where the parameters use generic types.
• PostgresDatabaseInfo.cs contains the SQL used to load all the properties/attributes of a Postgres composite type. The query reveals the following:
  • System attributes like xmin and xmax are NOT included. This makes it possible to map a CLR type to the composite type that Postgres automatically creates for a table (be sure to include Load Table Composites=true in your connection string).
  • Attributes are ordered by pg_attribute.attnum (i.e. in the order defined within the composite type) and they should appear in the same order within your CLR type.
  • If you drop an attribute in the composite type, it will not be included, so you should delete the corresponding member (or constructor parameter) in your CLR type.

Some of the rules as can be seen in the code:

• The CLR type can be a class or a struct - both are supported.
• You can map the CLR type to a composite in a specific schema.
• If the CLR type defines a suitable constructor, it will be used when reading the composite.
• Properties in the CLR type are mapped.
• Plain fields in the CLR type are also mapped.
• The properties and fields can be nullable.
• The properties and fields can be of other composite types and arrays not just scalars.
• Only public instance properties and fields are mapped (see above links).
• Properties and fields in the CLR type are ignored if no corresponding attribute is found in the Postgres type.
  • Every attribute in the Postgres type must have a corresponding member in the CLR type.
  • ...otherwise an exception is thrown when reading/writing the composite.
  • ...the same exception is thrown when reading a composite via a constructor.
• You can use the [PgName] attribute to customize mapping of names and the names can be translated between PascalCase used in C# and snake_case used in Postgres.
• The CLR type can have a constructor with up to a 8 generic parameters.
• The CLR type can have constructors that take less than the number of attributes in the composite and these constructors will be ignored.
• If the CLR type defines a constructor but it can't be mapped (e.g. because it doesn't match the number of attributes in the composite type), then it must have a default constructor or an exception is thrown.
• To do the mapping, the getter and setter for each property in the CLR type is used. For fields compiled versions are generated and used. These getters and setters are cached and performance should be good.

Long story short: it pretty much just works (even if your CLR type isn't a simple POCO) 👍

My own personal interest in all of this, is using npgsql as a light-weight ORM. I can define stored procedures and functions in Postgres that take/return composite types, then call them easily from C# much like JsonSerializer makes it easy to send/receive model objects when using ASP.NET Web APIs.

[roji]

@ericmutta it's great that it's all working for you. Yes, for the limitation on constructor parameters, we decided to wait on user feedback before investing more effort into this.

I generally believe in succinct docs - docs which are overly long generally don't get read, and confuse users more than they help. As a result, I believe it's better to just show basic usage and list limitations, rather than listing every possible thing that a user may ask, or detailing how things are done under the hood. From my experience, people just try doing their thing (e.g. let's try to map a struct, or fields) - I've rarely seen confusion around these things.

[PgIgnore] specifically was briefly raised in #1090, but I haven't seen any actually requests for it. People generally tend to have a POCO corresponding to the PG type, it seems.
For any missing stuff (e.g.

[ericmutta]

@roji thanks for following up!

We are definitely on the same page regarding succinct docs, so I'll defer to you on how best to summarize the composite mapping features whenever you get round to it (meanwhile this issue will be a good reference for curious people like myself) 👍

Regarding [PgIgnore] I can see now it is not necessary because the composite handler matches CLR type properties to Postgres composite attributes by name and ultimately ignores the property in the CLR type if no matching attribute is found in the Postgres composite types. This is excellent (I will update my notes above)!