Add support for reading arbitrary enum values as `String`

[lukaseder]

Feature Request

Is your feature request related to a problem? Please describe

It's currently not possible (I think?) to read enum types with r2dbc-postgresql. Create a type like this:

create type e as enum('a','b');

And try reading it like this:

System.out.println(
    Flux.from(cf.create())
        .flatMap(c -> c.createStatement("select 'a'::e union select null::e").execute())
        .flatMap(it -> it.map((r, m) -> Optional.ofNullable(r.get(0))))
        .collectList()
        .block()
    );
;

There's an exception:

java.lang.IllegalArgumentException: Cannot decode value of type java.lang.Object with OID 681868
	at io.r2dbc.postgresql.codec.DefaultCodecs.decode(DefaultCodecs.java:162)
	at io.r2dbc.postgresql.PostgresqlRow.decode(PostgresqlRow.java:94)
	at io.r2dbc.postgresql.PostgresqlRow.get(PostgresqlRow.java:70)
	at io.r2dbc.spi.Row.get(Row.java:51)
	at org.jooq.testscripts.R2DBC.lambda$2(R2DBC.java:43)
	at io.r2dbc.postgresql.PostgresqlResult.lambda$map$1(PostgresqlResult.java:110)
	at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:169)
	at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:667)
	at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drain(FluxWindowPredicate.java:745)
	at reactor.core.publisher.FluxWindowPredicate$WindowFlux.onNext(FluxWindowPredicate.java:787)
	at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onNext(FluxWindowPredicate.java:265)
	at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:793)
	at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:718)
	at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:154)
	at io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:717)
	at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:968)
	at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:842)
	at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:749)
	at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:118)
	at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
	at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:220)
	at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:220)
	at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:265)
	at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:371)
	at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:358)
	at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:96)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
	at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
	at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:311)
	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:432)
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
	at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
	at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:655)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:581)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)
	Suppressed: java.lang.Exception: #block terminated with an error
		at reactor.core.publisher.BlockingSingleSubscriber.blockingGet(BlockingSingleSubscriber.java:99)
		at reactor.core.publisher.Mono.block(Mono.java:1703)
		at org.jooq.testscripts.R2DBC.main(R2DBC.java:45)

Describe the solution you'd like

There should be codecs to read enum types and map them to strings, which is a reasonable default serialisation for enum types

[lukaseder]

On a related note: Given that PostgreSQL has a very easily extendable type system, I think it should be possible to get a String serialisation for all the not-yet-supported types, especially the user-defined ones. Just like with pgjdbc, where almost every type can be read in the form of a PGobject type containing a string.

[mp911de]

Have you seen https://github.com/pgjdbc/r2dbc-postgresql#postgres-enum-types to consume enums as Java enums?

I actually like the idea of having a generic representation of a value. With R2DBC 0.9, we introduced Parameter that can hold a value and refer to a database type. The driver could provide something similar that holds the representation of the value (textual/binary) and provide a toString() implementation.

[lukaseder]

Have you seen https://github.com/pgjdbc/r2dbc-postgresql#postgres-enum-types to consume enums as Java enums?

I have not. That works for end users that use R2DBC directly, but it's not too practical for a client library like jOOQ:

• Which doesn't know the user type representation of the enum, and as such, can only internally work with something like a String
• Which doesn't / shouldn't tamper with a user's R2DBC Connection configuration, including its codec registries

However, assuming the library knows the user-type, it would be useful to think about some type registry API that can be used in an ad-hoc fashion, rather than globally. For example, JDBC has ResultSet.getObject(int, Map<String, Class<?>>) for that purpose. It allows for providing an ad-hoc mapping of type names -> SQLData classes when reading UDTs. It was never really adopted outside of ojdbc, but I still think it's a useful mechanism for this purpose.

[lukaseder]

EnumCodec requires the Postgres OID

That's another thing I'd like to avoid. The OID of a user-defined type is unstable and can change depending on the system, so it would have to be looked up all the time. Caching is possible, but in principle, between two usages, the OID could change if the user drops / re-creates the type. The name is the only reliable way to identify a type.

Make sure to use different Java enum types otherwise the driver is not able to distinguish between Postgres OIDs.

That's another limitation that I wouldn't want to support :) Again, the identifier of an enum type should be the fully qualified type name, not the OID or any other handle, such as a client representation.

[mp911de]

If you accept a provided ConnectionFactory it doesn't make sense to tamper with user-space configuration. While we can certainly do something for the reading side, how would you want to provide the writing value when inserting/updating/querying data (WHERE clause)? We should find something that is symmetric for both cases.

[lukaseder]

We should find something that is symmetric for both cases.

Sure. I'd expect to be able to do the same as with JDBC:

where enum_column = cast(? as schema.enum_type)

And then bind a string value. In my case, I always know the enum type's qualified identifier. If users work with enums and R2DBC directly, they'll also do that, they're used to doing it with JDBC, too. It works for many types this way, in PostgreSQL

[mp911de]

As a workaround, you could cast the enum to varchar when selecting data.

[lukaseder]

As a workaround, you could cast the enum to varchar when selecting data.

Users could easily, but for me, it's a bit more tricky:

• Only top level enum projections can be cast to varchar. Nested select projections must be left alone (that's doable in jOOQ, but there are a few edge cases, e.g. when there are unions, etc.)
• I might have to remember the original projection expression in case there is an ORDER BY done on the enum expression (enums order by ordinal, not by literal, in PG)

So, an ideal solution wouldn't require tampering with the SQL for the read case. The write case is different, because it's only a bind variable, nothing complex.

[lukaseder]

... I mean, it's definitely doable for jOOQ, but if there's going to be an out-of-the-box solution in the next versions of r2dbc-postgresql, then I prefer not to work around this.

[mp911de]

I'm trying to estimate where this is going. The driver doesn't maintain a type cache nor does it look up types during result processing as that would require another query while consuming a result and that doesn't work with the streaming approach of result consumption. Let me come up with a draft for something.

[jbellassai]

Any idea yet on how to move this forward? I'm working on a project trying to use jOOQ with r2dbc-postgresql and it's stuck on this.

[mp911de]

As a first step we could generally apply a toString codec that attempts to decode the value first (when a codec exists) and then call toString() on the value. If no codec was found (typically for enum types), we can attempt a VARCHAR decode and see whether that helps.

In any case, pull requests are welcome.