Add the ability to register EnumCodec when enum data type is not in the Database yet.

[man85]

DDL and DML migrations fails with enum data types. The application executes migrations that adding enum data type into Database, but Enum codecs tried to register during connection creation before this moment and they haven't been registered, because there weren't enum types in the Database yet. Than some code try to save or retrieve Enum data from the Database and will fail, because there is no Enum codec registered for connections. As workarkaround I tried to register fake codec. When app try to save or retrieve data some of methods canDecode, canEncode, canEncodeNull, getDataTypes of this fake codec will be invoked. After that the fake codec checks whether enum type exists in the Database or not and if it does the fake codec tries to register enum codec with

EnumCodec.builder()
                    .withEnum(databaseTypeName, kotlinClazz.java)
                    .withRegistrationPriority(EnumCodec.Builder.RegistrationPriority.LAST)
                    .build()
                    .register(connection, allocator, registry)

, but codec won't be found found in current canEncode method. canEncode' https://github.com/pgjdbc/r2dbc-postgresql/blob/main/src/main/java/io/r2dbc/postgresql/codec/CachedCodecLookup.java#L115 invocation occurs after cache lookup https://github.com/pgjdbc/r2dbc-postgresql/blob/main/src/main/java/io/r2dbc/postgresql/codec/CachedCodecLookup.java#L151. So if launch registiring new codec it will appear in codec cache, but it won't be found beacuse there will no cache lookup in the current canEncode' already. Possible solutions:

1. Add the feature for registering new codec on the fly for connections that already created.
2. Add the ability to close all connections (or does it already exist?), so new connections will be created with Enum codec successfully registered.
3. Add the ability to register EnumCodec when enum data type is not in the Database yet.

[mp911de]

EnumCodec requires an OID. We cannot perform OID lookups during encoding or decoding values. You would need to run your migration scripts first and then obtain a new connection that is associated with the EnumCodec.

[man85]

Why EnumCodec can't load OID lazyly? When I created codecs for user-defined types I did this trick

[mp911de]

Because R2DBC's bind-methods and the codec API uses synchronous methods. Lazym OID retrieval requires I/O that we cannot do in a synchronous flow.

[man85]

What about adding codecs to CodecLookup on the fly?

[man85]

You would need to run your migration scripts first and then obtain a new connection that is associated with the EnumCodec.

There is no control over all connections that have been created before, just over ConnectionFactory

[mp911de]

There is currently no way to re-register codecs on an active connection. Even if there would be, you would be required to interact with a connection pool and obtain all idle connections.

The easier approach is to set min-idle connections on the pool to zero, ensure that the pool can be empty and rely on auto-registration of the enum codec upon new connection creation.

Alternatively, use two pools/connectionfactories.