Repository: jackc/pgx
Pull request #2046 (2024-06-15 07:57:50)

Faster/easier loading of types
As discussed in https://github.com/jackc/pgx/issues/2030 , improve the `LoadType` method to use a single SQL call.

Add `LoadTypes()` which adds multiple types in a single call, and supports recursively loading of other implicitly-required types in the same SQL call.

nicois: @jackc when tests run against cockroachdb, I see:
```
ERROR: unknown signature: array_agg(name[]) (SQLSTATE 42883)
```

I don't have any experience with this database, and I don't see any mention of being incompatible with this function. If pgx needs to support cockroachdb, how would you suggest we do so? I could try using an alternate syntax, but I'd like your advice before doing so.
nicois: All regression tests apart from cockroachdb are passing: https://github.com/nicois/pgx/actions/runs/9526586068
jackc: Regarding Cockroach DB, if it is something that is not supported by that database engine then there is a helper method to skip tests. But as far as I know, `array_agg` is supported by CRDB. I'm not sure where that error is coming from but the `name[]` is suspicious. I would expect the call to aggregate `name` not `name[]`. As in, it looks like it is trying to aggregate an array of `name` into an array of arrays of `name`s instead of aggregating `name` into an array of `name`.

I also was wondering if the loading all custom types includes table types. 
nicois: `array_agg` is being used by the new single-SQL statement, to select everything in one go - so it's not viable to simply skip the test.
This is related to supporting composite fields, where I am collecting multiple 2-tuples of composite fields into an array of 2-tuples, so it is in fact intended that this is an array of arrays of strings. 
I will try to flatten the structure to a single array of 2`X` elements, where `X` is the number of composite fields.

Regarding the loading of all types: yes, there is further work needed there. I've added a simple test and there are complications. I think this needs some experimentation/discussion, and it's probably better to cover that in its own PR. I will remove the public method to invoke the loading of all types from this PR.
nicois: It's passing with cockroachdb now. I've also reinstated the original code for `LoadType` to minimise the chance of causing any regressions for existing users.
When loading multiple types, it's pretty much essential to load each type into the map as it's found, so I've also renamed the methods to more clearly capture this fact.
nicois: I will refine the SQL further, to deal with more complex heirachies of types/domains/arrays/composite types.
jackc: I suppose there is no way around the bit recursive query. But, wow, that is scary looking. I'd hate to have to debug that. 🤷 

I also think the server version should be an internal function.
nicois: Sure, I'll make it internal. I thought it might be helpful for a client, but perhaps it's better to not expose it.

That query isn't nice, and it's quite possible it could be optimised in some way too; it takes longer than I'd like it to. However, my upcoming pgxpool PR has support for caching this mapping (for non-replicated environments), meaning the overhead is only felt once per invocation.
nicois: I'll add a few comments to the scary SQL to make it a little less nightmarish.
nicois: I've changed the `serverVersion` to being private, added some comments to the SQL, and also modified the structure a little, so there is a `LoadTypes` and `RegisterTypes` call. This is both more similar to the `LoadType` which exists, and will be a better structure for what's coming.
jackc: Hmmm... The functionality looks good, but I'm not so sure about the interface.

At first glance, I didn't like `TypeInfo`. I didn't understand why `pgtype.Type` wouldn't be used directly. But then I realized that since types dependent on other types directly refer to the registered `Codec` of those types it wouldn't be possible to safely share the type. Be that as it may, it's still confusing to have two types that are so similar. Not sure what the solution is.

Then `LoadTypes` and `RegisterTypes` are named such that I would expect they are exactly the same as the singular versions except they do multiple at a time. But they are different from their singular counterparts. It is counterintuitive that `LoadType` returns a `*pgtype.Type` but `LoadTypes` returns `[]*TypeInfo`.

I think I understand at this point why `*pgtype.Type` couldn't be used directly, but the naming, at least, is very unfortunate.

Upon further reflection, I also question if `LoadTypes` and `RegisterTypes` should be members of `*pgx.Conn` at all. `LoadTypes` doesn't use any private values or functions of the `*pgx.Conn` except `serverVersion` and that doesn't need to be a member either. `RegisterTypes` has even less justification to be a member function. Not only does it not use the receiver it actually uses the `registerWith` argument instead.

---

So here's some suggestions for your consideration. I'm not sure if they are good ideas, but I think they are worth looking into.

* Rename `LoadTypes` to disambiguate loading a `pgtype.Type` and `pgx.TypeInfo`.
* Rename `RegisterTypes` to disambiguate registering a `pgtype.Type` and `pgx.TypeInfo`.
* Make `LoadTypes` a function instead of a method.
* Make `RegisterTypes` a function instead of a method. Or perhaps it should be a method on `*pgtype.Map`.
* Perhaps `TypeInfo` should be part of the `pgtype` instead of the `pgx` package.
* I wish there was a overall naming scheme. Perhaps something to do with derived or dependent types would be better. `LoadType(s)` only works on types that are derived from known types. e.g. You can't use `LoadType` for an entirely unknown type like something from PostGIS. But you do use `pgtype.Map.RegisterType`. Maybe there should be something like `LoadDerivedTypes` / `RegisterDerivedType` / `DerivedTypeInfo`. 🤷‍♂️ 
* Possibly `Conn.LoadType` should be deprecated in favor of this new functionality.
 
---

Also, one other random thing. I noticed `LoadTypes` is purposely using the simple protocol. Any particular reason?
nicois: I've addressed the feedback.

- Renamed methods and struct as suggested
- Turned methods into functions where possible
- Moved some things into pgtype
- Made the registration part into a method on `Map`
- Added tests, including showing how arrays work: https://github.com/jackc/pgx/pull/2046/files#diff-af46ecc2001b2a06a9990fee3299e7e2cd95e99cbbb914cd7fd90dc1d2c186d4R33

There are a couple of reasons I didn't change the original `LoadType`:
- when only a single type is being loaded, in some situations it might be faster, if the database connection is low latency and there are many other types which would be scanned by the mega query
- people who want speed will switch over to the new syntax, probably via the (coming) pgxpool PR
- if there are any bugs, they can be ironed out without upsetting existing users
nicois: Regarding the use of the simple protocol: when I remove this, at least when running locally the test fails:

```
While scanning type information: can't scan into dest[1]: cannot scan char (OID 18) in binary format into *string
```
jackc: Regarding scanning `"char"`, that is a special type. I believe the issue is scanning `pg_type.typtype` into a string. Try scanning it into a `byte` or `rune` instead.

---

I like the progress that is being made here. But I've been reviewing this PR, looking back at the existing pgx type code, and mulling it over. And I think there might be more fundamental improvements that can be made.

As I understand it, you are trying to improve two issues. 1. This PR is making loading types faster. 2. The pgxpool PR is trying to make it unnecessary to introspect the database for each connection. They are somewhat related and improvements in one may affect what is desirable or necessary for the other.

Regarding loading types faster, as we've discussed above, I consider it unfortunate that we end up with 2 similar things `pgtype.Type` and `pgtype.DerivedTypeInfo`. The naming, at least, is better now, but two similar things still bother me. That's makes me wonder what if instead of adding `pgtype.DerivedTypeInfo` we instead added all of its fields to `pgtype.Type`? `LoadDerivedTypes` would again become `LoadTypes`.

... 🤔 ... 🤔 ... 🤔 ...

Actually, wait a second. Why do we need the `DerivedTypeInfo` fields at all?

My previous observation was wrong:

> At first glance, I didn't like TypeInfo. I didn't understand why pgtype.Type wouldn't be used directly. But then I realized that since types dependent on other types directly refer to the registered Codec of those types it wouldn't be possible to safely share the type. 

It **is** possible to safely share the `*pgtype.Type`. According to the docs `*pgtype.Type` and `*pgtype.Codec` must not be altered once they are registered with a `pgtype.Map`.

I think this means PR this and the pgxpool PR can be significantly simplified. We still need the complicated SQL query, but I think much of the rest could go away.

Here's an idea: We add a `LoadTypes` function. It runs your big SQL query, but it returns `[]*pgtype.Type`. It's slightly tricky because it needs to build up its own map of types in addition to the `*pgtype.Map. For example, `_mytype` depends on `mytype` which is a composite which contains `_myothertype`. The `*pgtype.Type`s would need to be created in the order `myothertype`, `_myothertype`, `mytype`, `_mytype`. So `LoadTypes` would have to keep track of what `*pgtype.Type` it has created.

`RegisterDerivedTypes` goes away. The existing `*Map.RegisterType` function is used.

Add another small change and we also solve the `_hstore` problem discussed on the other PR. `LoadTypes` could support types where typtype = 'b' (base type). It would leave the `Codec` nil in that case. Derived types like `_hstore` would still be initialized because they would have a pointer to the `pgtype.Type` for `hstore`. The caller of `LoadTypes` would be responsible for filling in the `Codec` before registering the types.

This should allow for all type introspection to be done in a single SQL query regardless of how many types or what kind types are used.

Regarding the pgxpool change to avoid having to introspect the database for each connection, I'm not sure how much of that would still be necessary either. Since `pgtype.Type` and `pgtype.Codec` are immutable once registered, there's no reason we can't just copy one `pgtype.Map`'s type information to another `pgtype.Map`. 


nicois: Ok, I see where you're going with this. I will try to avoid the need to define a second type, if I can. 

Regarding pgxpool: the type map has to be populated for each connection, doesn't it? And there is still the need to support situations where the pool does not always connect to servers sharing the same OID mapping. Assuming that, there are wins in making it easy to let the custom and automatic loaders be defined more easily, and to cache the results when it's safe to do so.

Either way, it's good to keep the PRs separate as the first one is needed either way, and once it's solid, it's easier to look at the incremental benefit of the other.
nicois: I've made some changes which didn't take very long at all, and am pretty happy with the results:
- there is now `LoadTypes` which returns `[]*pgtype.Type`. It copies the connection's `Map` and then mutates that copy while it builds the list of types, preventing types from complaining about not being able to find other types which they expect to already be registered. But this is just an temporary internal map; what is returned is a clean list of `Type`s. Note that this also deals with the `hstore` issue: as long as someone has registered `hstore` in their connection's `Map` before calling `LoadTypes`, that `hstore` type will be found and respected.
- I created a `Map.Copy()` method to support the above
- A `Map.RegisterTypes()` convenience method is trivial, and can be removed if you think it's too trivial. 
nicois: > Regarding scanning "char", that is a special type. I believe the issue is scanning pg_type.typtype into a string. Try scanning it into a byte or rune instead.

I've tried removing the simple protocol designator, then using `byte` and `rune`, and they both error. I'll leave it as-is for now, unless you can suggest something else.
jackc: I mentioned this idea on #2056, but I will restate it here.

What if `pgx.ConnConfig` had a list of PG type names to load when the connection is established. For base types like `hstore` or PostGIS types, a map of name to function could be provided that would contain the loading logic.

This would remove the need to manually load types after connection, and would probably remove most uses of the after connect hooks provided by pgxpool and stdlib.

It would also remove the need to have any new functions or methods. The only new interface would be additional fields on `*pgx.ConnConfig`.

This would be an even simpler interface for loading types. 
nicois: @jackc I have tried to modify these 3 PRs to minimise the change in the interface, as you suggested. It did work out a bit less messy than I anticipated, so let me know what you think.
jackc: @nicois 

Thanks for making all these changes. I still think this configuration should not live on the pool. But now actually seeing it on the connection and especially seeing how it complicates #2048 (`ConnectConfigReusingTypeMap` is a rather inelegant necessity caused by this change), I'm not sure `AutoLoadTypes` on the `pgx.ConnConfig` is right either.

Here's what I think we should do. Let's go back to some of your original design.

* Back out this last change of `AutoLoadTypes` on `pgx.ConnConfig`.
* Make `LoadTypes` a method of `*pgx.Conn` again. Given that it now really is the collection version of `LoadType` it should live next to it. If I could do it all over again I might not make `LoadType` a `Conn` method, but at this point consistency is probably the priority.

I'll merge just that core functionality. Then we can evaluate what is the most convenient interface for exposing the functionality.

---

With regard to that convenient interface, the pgxpool interface and possibly configuration on `Conn`, I would like us to evaluate using https://pkg.go.dev/sync#OnceValue to load the types. This, possibly with a wrapping helper function, might be a better abstraction than the config options.
nicois: I've pushed the earlier version back, with the duplicate creation of types for the namespaced version of each type's name. You can more easily see this here: https://github.com/jackc/pgx/compare/4660f1d7546adb2f6091625ed9fe42f393daf522..dcb32f94021386bf4f450b858281687faa4a0641
nicois: I'll address your feedback above, then let you know. We can follow up further if I need be.
nicois: ok, this should be good to merge now.

Patch:
diff --git a/conn.go b/conn.go
index 31172145..979095af 100644
--- a/conn.go
+++ b/conn.go
@@ -107,8 +107,10 @@ var (
 	ErrTooManyRows = errors.New("too many rows in result set")
 )
 
-var errDisabledStatementCache = fmt.Errorf("cannot use QueryExecModeCacheStatement with disabled statement cache")
-var errDisabledDescriptionCache = fmt.Errorf("cannot use QueryExecModeCacheDescribe with disabled description cache")
+var (
+	errDisabledStatementCache   = fmt.Errorf("cannot use QueryExecModeCacheStatement with disabled statement cache")
+	errDisabledDescriptionCache = fmt.Errorf("cannot use QueryExecModeCacheDescribe with disabled description cache")
+)
 
 // Connect establishes a connection with a PostgreSQL server with a connection string. See
 // pgconn.Connect for details.
@@ -843,7 +845,6 @@ func (c *Conn) getStatementDescription(
 	mode QueryExecMode,
 	sql string,
 ) (sd *pgconn.StatementDescription, err error) {
-
 	switch mode {
 	case QueryExecModeCacheStatement:
 		if c.statementCache == nil {
diff --git a/derived_types.go b/derived_types.go
new file mode 100644
index 00000000..3df45e48
--- /dev/null
+++ b/derived_types.go
@@ -0,0 +1,260 @@
+package pgx
+
+import (
+	"context"
+	"fmt"
+	"regexp"
+	"strconv"
+	"strings"
+
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+/*
+buildLoadDerivedTypesSQL generates the correct query for retrieving type information.
+
+	pgVersion: the major version of the PostgreSQL server
+	typeNames: the names of the types to load. If nil, load all types.
+*/
+func buildLoadDerivedTypesSQL(pgVersion int64, typeNames []string) string {
+	supportsMultirange := (pgVersion >= 14)
+	var typeNamesClause string
+
+	if typeNames == nil {
+		// This should not occur; this will not return any types
+		typeNamesClause = "= ''"
+	} else {
+		typeNamesClause = "= ANY($1)"
+	}
+	parts := make([]string, 0, 10)
+
+	// Each of the type names provided might be found in pg_class or pg_type.
+	// Additionally, it may or may not include a schema portion.
+	parts = append(parts, `
+WITH RECURSIVE
+-- find the OIDs in pg_class which match one of the provided type names
+selected_classes(oid,reltype) AS (
+    -- this query uses the namespace search path, so will match type names without a schema prefix
+    SELECT pg_class.oid, pg_class.reltype
+    FROM pg_catalog.pg_class
+        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
+    WHERE pg_catalog.pg_table_is_visible(pg_class.oid)
+      AND relname `, typeNamesClause, `
+UNION ALL
+    -- this query will only match type names which include the schema prefix
+    SELECT pg_class.oid, pg_class.reltype
+    FROM pg_class
+    INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
+    WHERE nspname || '.' || relname `, typeNamesClause, `
+),
+selected_types(oid) AS (
+    -- collect the OIDs from pg_types which correspond to the selected classes
+    SELECT reltype AS oid
+    FROM selected_classes
+UNION ALL
+    -- as well as any other type names which match our criteria
+    SELECT pg_type.oid
+    FROM pg_type
+    LEFT OUTER JOIN pg_namespace ON (pg_type.typnamespace = pg_namespace.oid)
+    WHERE typname `, typeNamesClause, `
+        OR nspname || '.' || typname `, typeNamesClause, `
+),
+-- this builds a parent/child mapping of objects, allowing us to know
+-- all the child (ie: dependent) types that a parent (type) requires
+-- As can be seen, there are 3 ways this can occur (the last of which
+-- is due to being a composite class, where the composite fields are children)
+pc(parent, child) AS (
+    SELECT parent.oid, parent.typelem
+    FROM pg_type parent
+    WHERE parent.typtype = 'b' AND parent.typelem != 0
+UNION ALL
+    SELECT parent.oid, parent.typbasetype
+    FROM pg_type parent
+    WHERE parent.typtypmod = -1 AND parent.typbasetype != 0
+UNION ALL
+    SELECT pg_type.oid, atttypid
+    FROM pg_attribute
+    INNER JOIN pg_class ON (pg_class.oid = pg_attribute.attrelid)
+    INNER JOIN pg_type ON (pg_type.oid = pg_class.reltype)
+    WHERE NOT attisdropped
+      AND attnum > 0
+),
+-- Now construct a recursive query which includes a 'depth' element.
+-- This is used to ensure that the "youngest" children are registered before
+-- their parents.
+relationships(parent, child, depth) AS (
+    SELECT DISTINCT 0::OID, selected_types.oid, 0
+    FROM selected_types
+UNION ALL
+    SELECT pg_type.oid AS parent, pg_attribute.atttypid AS child, 1
+    FROM selected_classes c
+    inner join pg_type ON (c.reltype = pg_type.oid)
+    inner join pg_attribute on (c.oid = pg_attribute.attrelid)
+UNION ALL
+    SELECT pc.parent, pc.child, relationships.depth + 1
+    FROM pc
+    INNER JOIN relationships ON (pc.parent = relationships.child)
+),
+-- composite fields need to be encapsulated as a couple of arrays to provide the required information for registration
+composite AS (
+    SELECT pg_type.oid, ARRAY_AGG(attname ORDER BY attnum) AS attnames, ARRAY_AGG(atttypid ORDER BY ATTNUM) AS atttypids
+    FROM pg_attribute
+    INNER JOIN pg_class ON (pg_class.oid = pg_attribute.attrelid)
+    INNER JOIN pg_type ON (pg_type.oid = pg_class.reltype)
+    WHERE NOT attisdropped
+      AND attnum > 0
+    GROUP BY pg_type.oid
+)
+-- Bring together this information, showing all the information which might possibly be required
+-- to complete the registration, applying filters to only show the items which relate to the selected
+-- types/classes.
+SELECT typname,
+       pg_namespace.nspname,
+       typtype,
+       typbasetype,
+       typelem,
+       pg_type.oid,`)
+	if supportsMultirange {
+		parts = append(parts, `
+       COALESCE(multirange.rngtypid, 0) AS rngtypid,`)
+	} else {
+		parts = append(parts, `
+       0 AS rngtypid,`)
+	}
+	parts = append(parts, `
+       COALESCE(pg_range.rngsubtype, 0) AS rngsubtype,
+       attnames, atttypids
+    FROM relationships
+    INNER JOIN pg_type ON (pg_type.oid = relationships.child)
+    LEFT OUTER JOIN pg_range ON (pg_type.oid = pg_range.rngtypid)`)
+	if supportsMultirange {
+		parts = append(parts, `
+    LEFT OUTER JOIN pg_range multirange ON (pg_type.oid = multirange.rngmultitypid)`)
+	}
+
+	parts = append(parts, `
+    LEFT OUTER JOIN composite USING (oid)
+    LEFT OUTER JOIN pg_namespace ON (pg_type.typnamespace = pg_namespace.oid)
+    WHERE NOT (typtype = 'b' AND typelem = 0)`)
+	parts = append(parts, `
+    GROUP BY typname, pg_namespace.nspname, typtype, typbasetype, typelem, pg_type.oid, pg_range.rngsubtype,`)
+	if supportsMultirange {
+		parts = append(parts, `
+        multirange.rngtypid,`)
+	}
+	parts = append(parts, `
+        attnames, atttypids
+    ORDER BY MAX(depth) desc, typname;`)
+	return strings.Join(parts, "")
+}
+
+type derivedTypeInfo struct {
+	Oid, Typbasetype, Typelem, Rngsubtype, Rngtypid uint32
+	TypeName, Typtype, NspName                      string
+	Attnames                                        []string
+	Atttypids                                       []uint32
+}
+
+// LoadTypes performs a single (complex) query, returning all the required
+// information to register the named types, as well as any other types directly
+// or indirectly required to complete the registration.
+// The result of this call can be passed into RegisterTypes to complete the process.
+func (c *Conn) LoadTypes(ctx context.Context, typeNames []string) ([]*pgtype.Type, error) {
+	m := c.TypeMap()
+	if typeNames == nil || len(typeNames) == 0 {
+		return nil, fmt.Errorf("No type names were supplied.")
+	}
+
+	// Disregard server version errors. This will result in
+	// the SQL not support recent structures such as multirange
+	serverVersion, _ := serverVersion(c)
+	sql := buildLoadDerivedTypesSQL(serverVersion, typeNames)
+	var rows Rows
+	var err error
+	if typeNames == nil {
+		rows, err = c.Query(ctx, sql, QueryExecModeSimpleProtocol)
+	} else {
+		rows, err = c.Query(ctx, sql, QueryExecModeSimpleProtocol, typeNames)
+	}
+	if err != nil {
+		return nil, fmt.Errorf("While generating load types query: %w", err)
+	}
+	defer rows.Close()
+	result := make([]*pgtype.Type, 0, 100)
+	for rows.Next() {
+		ti := derivedTypeInfo{}
+		err = rows.Scan(&ti.TypeName, &ti.NspName, &ti.Typtype, &ti.Typbasetype, &ti.Typelem, &ti.Oid, &ti.Rngtypid, &ti.Rngsubtype, &ti.Attnames, &ti.Atttypids)
+		if err != nil {
+			return nil, fmt.Errorf("While scanning type information: %w", err)
+		}
+		var type_ *pgtype.Type
+		switch ti.Typtype {
+		case "b": // array
+			dt, ok := m.TypeForOID(ti.Typelem)
+			if !ok {
+				return nil, fmt.Errorf("Array element OID %v not registered while loading pgtype %q", ti.Typelem, ti.TypeName)
+			}
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: &pgtype.ArrayCodec{ElementType: dt}}
+		case "c": // composite
+			var fields []pgtype.CompositeCodecField
+			for i, fieldName := range ti.Attnames {
+				dt, ok := m.TypeForOID(ti.Atttypids[i])
+				if !ok {
+					return nil, fmt.Errorf("Unknown field for composite type %q:  field %q (OID %v) is not already registered.", ti.TypeName, fieldName, ti.Atttypids[i])
+				}
+				fields = append(fields, pgtype.CompositeCodecField{Name: fieldName, Type: dt})
+			}
+
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: &pgtype.CompositeCodec{Fields: fields}}
+		case "d": // domain
+			dt, ok := m.TypeForOID(ti.Typbasetype)
+			if !ok {
+				return nil, fmt.Errorf("Domain base type OID %v was not already registered, needed for %q", ti.Typbasetype, ti.TypeName)
+			}
+
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: dt.Codec}
+		case "e": // enum
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: &pgtype.EnumCodec{}}
+		case "r": // range
+			dt, ok := m.TypeForOID(ti.Rngsubtype)
+			if !ok {
+				return nil, fmt.Errorf("Range element OID %v was not already registered, needed for %q", ti.Rngsubtype, ti.TypeName)
+			}
+
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: &pgtype.RangeCodec{ElementType: dt}}
+		case "m": // multirange
+			dt, ok := m.TypeForOID(ti.Rngtypid)
+			if !ok {
+				return nil, fmt.Errorf("Multirange element OID %v was not already registered, needed for %q", ti.Rngtypid, ti.TypeName)
+			}
+
+			type_ = &pgtype.Type{Name: ti.TypeName, OID: ti.Oid, Codec: &pgtype.MultirangeCodec{ElementType: dt}}
+		default:
+			return nil, fmt.Errorf("Unknown typtype %q was found while registering %q", ti.Typtype, ti.TypeName)
+		}
+		if type_ != nil {
+			m.RegisterType(type_)
+			if ti.NspName != "" {
+				m.RegisterType(&pgtype.Type{Name: ti.NspName + "." + type_.Name, OID: type_.OID, Codec: type_.Codec})
+			}
+			result = append(result, type_)
+		}
+	}
+	return result, nil
+}
+
+// serverVersion returns the postgresql server version.
+func serverVersion(c *Conn) (int64, error) {
+	serverVersionStr := c.PgConn().ParameterStatus("server_version")
+	serverVersionStr = regexp.MustCompile(`^[0-9]+`).FindString(serverVersionStr)
+	// if not PostgreSQL do nothing
+	if serverVersionStr == "" {
+		return 0, fmt.Errorf("Cannot identify server version in %q", serverVersionStr)
+	}
+
+	version, err := strconv.ParseInt(serverVersionStr, 10, 64)
+	if err != nil {
+		return 0, fmt.Errorf("postgres version parsing failed: %w", err)
+	}
+	return version, nil
+}
diff --git a/go.sum b/go.sum
index 4b02a036..29fe452b 100644
--- a/go.sum
+++ b/go.sum
@@ -4,10 +4,6 @@ github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c
 github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
 github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
-github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk=
-github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
-github.com/jackc/pgservicefile v0.0.0-20231201235250-de7065d80cb9 h1:L0QtFUgDarD7Fpv9jeVMgy/+Ec0mtnmYuImjTz6dtDA=
-github.com/jackc/pgservicefile v0.0.0-20231201235250-de7065d80cb9/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
 github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo=
 github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
 github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
diff --git a/pgconn/config.go b/pgconn/config.go
index f07e0885..598917f5 100644
--- a/pgconn/config.go
+++ b/pgconn/config.go
@@ -654,36 +654,6 @@ func configTLS(settings map[string]string, thisHost string, parseConfigOptions P
 
 	tlsConfig := &tls.Config{}
 
-	if sslrootcert != "" {
-		var caCertPool *x509.CertPool
-
-		if sslrootcert == "system" {
-			var err error
-
-			caCertPool, err = x509.SystemCertPool()
-			if err != nil {
-				return nil, fmt.Errorf("unable to load system certificate pool: %w", err)
-			}
-
-			sslmode = "verify-full"
-		} else {
-			caCertPool = x509.NewCertPool()
-
-			caPath := sslrootcert
-			caCert, err := os.ReadFile(caPath)
-			if err != nil {
-				return nil, fmt.Errorf("unable to read CA file: %w", err)
-			}
-
-			if !caCertPool.AppendCertsFromPEM(caCert) {
-				return nil, errors.New("unable to add CA to cert pool")
-			}
-		}
-
-		tlsConfig.RootCAs = caCertPool
-		tlsConfig.ClientCAs = caCertPool
-	}
-
 	switch sslmode {
 	case "disable":
 		return []*tls.Config{nil}, nil
@@ -741,6 +711,23 @@ func configTLS(settings map[string]string, thisHost string, parseConfigOptions P
 		return nil, errors.New("sslmode is invalid")
 	}
 
+	if sslrootcert != "" {
+		caCertPool := x509.NewCertPool()
+
+		caPath := sslrootcert
+		caCert, err := os.ReadFile(caPath)
+		if err != nil {
+			return nil, fmt.Errorf("unable to read CA file: %w", err)
+		}
+
+		if !caCertPool.AppendCertsFromPEM(caCert) {
+			return nil, errors.New("unable to add CA to cert pool")
+		}
+
+		tlsConfig.RootCAs = caCertPool
+		tlsConfig.ClientCAs = caCertPool
+	}
+
 	if (sslcert != "" && sslkey == "") || (sslcert == "" && sslkey != "") {
 		return nil, errors.New(`both "sslcert" and "sslkey" are required`)
 	}
diff --git a/pgtype/pgtype.go b/pgtype/pgtype.go
index 40829568..32d68f40 100644
--- a/pgtype/pgtype.go
+++ b/pgtype/pgtype.go
@@ -214,6 +214,15 @@ type Map struct {
 	TryWrapScanPlanFuncs []TryWrapScanPlanFunc
 }
 
+// Copy returns a new Map containing the same registered types.
+func (m *Map) Copy() *Map {
+	newMap := NewMap()
+	for _, type_ := range m.oidToType {
+		newMap.RegisterType(type_)
+	}
+	return newMap
+}
+
 func NewMap() *Map {
 	defaultMapInitOnce.Do(initDefaultMap)
 
@@ -248,6 +257,13 @@ func NewMap() *Map {
 	}
 }
 
+// RegisterTypes registers multiple data types in the sequence they are provided.
+func (m *Map) RegisterTypes(types []*Type) {
+	for _, t := range types {
+		m.RegisterType(t)
+	}
+}
+
 // RegisterType registers a data type with the Map. t must not be mutated after it is registered.
 func (m *Map) RegisterType(t *Type) {
 	m.oidToType[t.OID] = t


Test patch:
diff --git a/derived_types_test.go b/derived_types_test.go
new file mode 100644
index 00000000..e6c1c869
--- /dev/null
+++ b/derived_types_test.go
@@ -0,0 +1,37 @@
+package pgx_test
+
+import (
+	"context"
+	"testing"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/stretchr/testify/require"
+)
+
+func TestCompositeCodecTranscodeWithLoadTypes(t *testing.T) {
+	skipCockroachDB(t, "Server does not support composite types (see https://github.com/cockroachdb/cockroach/issues/27792)")
+
+	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
+		_, err := conn.Exec(ctx, `
+drop type if exists dtype_test;
+drop domain if exists anotheruint64;
+
+create domain anotheruint64 as numeric(20,0);
+create type dtype_test as (
+  a text,
+  b int4,
+  c anotheruint64,
+  d anotheruint64[]
+);`)
+		require.NoError(t, err)
+		defer conn.Exec(ctx, "drop type dtype_test")
+		defer conn.Exec(ctx, "drop domain anotheruint64")
+
+		types, err := conn.LoadTypes(ctx, []string{"dtype_test"})
+		require.NoError(t, err)
+		require.Len(t, types, 3)
+		require.Equal(t, types[0].Name, "anotheruint64")
+		require.Equal(t, types[1].Name, "_anotheruint64")
+		require.Equal(t, types[2].Name, "dtype_test")
+	})
+}
diff --git a/pgtype/derived_types_test.go b/pgtype/derived_types_test.go
new file mode 100644
index 00000000..4f716ea9
--- /dev/null
+++ b/pgtype/derived_types_test.go
@@ -0,0 +1,58 @@
+package pgtype_test
+
+import (
+	"context"
+	"testing"
+
+	pgx "github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgtype"
+	"github.com/stretchr/testify/require"
+)
+
+func TestDerivedTypes(t *testing.T) {
+	skipCockroachDB(t, "Server does not support composite types (see https://github.com/cockroachdb/cockroach/issues/27792)")
+
+	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
+		_, err := conn.Exec(ctx, `
+drop type if exists dt_test;
+drop domain if exists dt_uint64;
+
+create domain dt_uint64 as numeric(20,0);
+create type dt_test as (
+	a text,
+    b dt_uint64,
+    c dt_uint64[]
+);`)
+		require.NoError(t, err)
+		defer conn.Exec(ctx, "drop domain dt_uint64")
+		defer conn.Exec(ctx, "drop type dt_test")
+
+		dtypes, err := conn.LoadTypes(ctx, []string{"dt_test"})
+		require.Len(t, dtypes, 3)
+		require.Equal(t, dtypes[0].Name, "dt_uint64")
+		require.Equal(t, dtypes[1].Name, "_dt_uint64")
+		require.Equal(t, dtypes[2].Name, "dt_test")
+		require.NoError(t, err)
+		conn.TypeMap().RegisterTypes(dtypes)
+
+		formats := []struct {
+			name string
+			code int16
+		}{
+			{name: "TextFormat", code: pgx.TextFormatCode},
+			{name: "BinaryFormat", code: pgx.BinaryFormatCode},
+		}
+
+		for _, format := range formats {
+			var a string
+			var b uint64
+			var c *[]uint64
+
+			row := conn.QueryRow(ctx, "select $1::dt_test", pgx.QueryResultFormats{format.code}, pgtype.CompositeFields{"hi", uint64(42), []uint64{10, 20, 30}})
+			err := row.Scan(pgtype.CompositeFields{&a, &b, &c})
+			require.NoError(t, err)
+			require.EqualValuesf(t, "hi", a, "%v", format.name)
+			require.EqualValuesf(t, 42, b, "%v", format.name)
+		}
+	})
+}
diff --git a/pgx_test.go b/pgx_test.go
new file mode 100644
index 00000000..51b4bbc4
--- /dev/null
+++ b/pgx_test.go
@@ -0,0 +1,22 @@
+package pgx_test
+
+import (
+	"context"
+	"os"
+	"testing"
+
+	"github.com/jackc/pgx/v5"
+	_ "github.com/jackc/pgx/v5/stdlib"
+)
+
+func skipCockroachDB(t testing.TB, msg string) {
+	conn, err := pgx.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer conn.Close(context.Background())
+
+	if conn.PgConn().ParameterStatus("crdb_version") != "" {
+		t.Skip(msg)
+	}
+}