search

pgjdbc / r2dbc-postgresql

Postgresql R2DBC Driver
https://r2dbc.io
Apache License 2.0
1.01k stars 177 forks source link

Postgres 11 compatibility #657

Open AntonioHReyes opened 1 month ago

AntonioHReyes commented 1 month ago

Bug Report

Versions

Current Behavior

I'm trying to connect with r2dbc in spring to a Postgres database with version "PostgreSQL 11.22 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit" but I'm getting the following error:

"Column name 'oid' does not exist in column names [typname, typnamespace, typowner, typlen, typbyval, typtype, typcategory, typispreferred, typisdefined, typdelim, typrelid, typelem, typarray, typinput, typoutput, typreceive, typsend, typmodin, typmodout, typanalyze, typalign, typstorage, typnotnull, typbasetype, typtypmod, typndims, typcollation, typdefaultbin, typdefault, typacl]"

Searching a little, it's because, behind the scenes, this query is trying to be executed

SELECT * FROM pg_catalog.pg_type WHERE typname IN ('hstore','geometry','vector')

However, in postgress 11 the oid field is hidden and has to be specified in the query.

Is there any solution to allow the connection with postgress 11?

Stack trace ``` org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection at _COROUTINE._BOUNDARY._(CoroutineDebugging.kt:42) at com.motumweb.platform.neuro.neurodial.ManualConnectionTest$testManualConnection$1.invokeSuspend(ManualConnectionTest.kt:18) Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection at org.springframework.r2dbc.connection.ConnectionFactoryUtils.lambda$getConnection$0(ConnectionFactoryUtils.java:100) at reactor.core.publisher.Mono.lambda$onErrorMap$28(Mono.java:3854) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) at reactor.core.publisher.FluxRetry$RetrySubscriber.onError(FluxRetry.java:96) at reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:180) at reactor.pool.AbstractPool$Borrower.fail(AbstractPool.java:480) at reactor.pool.SimpleDequePool.lambda$drainLoop$9(SimpleDequePool.java:436) at reactor.core.publisher.FluxDoOnEach$DoOnEachSubscriber.onError(FluxDoOnEach.java:186) at reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.onError(MonoSubscribeOn.java:152) at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onError(FluxMapFuseable.java:142) at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onError(FluxContextWrite.java:121) at reactor.core.publisher.MonoSingle$SingleSubscriber.onError(MonoSingle.java:150) at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onError(FluxDiscardOnCancel.java:97) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) at reactor.core.publisher.Operators.error(Operators.java:198) at reactor.core.publisher.MonoError.subscribe(MonoError.java:53) at reactor.core.publisher.Mono.subscribe(Mono.java:4568) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103) at reactor.core.publisher.MonoFlatMap$FlatMapMain.secondError(MonoFlatMap.java:241) at reactor.core.publisher.MonoFlatMap$FlatMapInner.onError(MonoFlatMap.java:315) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:280) at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:232) at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:204) at reactor.core.publisher.Operators.complete(Operators.java:137) at reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:46) at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:53) at reactor.core.publisher.Mono.subscribe(Mono.java:4568) at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:265) at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) at reactor.core.publisher.Mono.subscribe(Mono.java:4568) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103) at reactor.core.publisher.MonoDelayUntil$DelayUntilTrigger.onError(MonoDelayUntil.java:516) at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onError(MonoIgnoreElements.java:84) at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onError(Operators.java:2236) at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onError(MonoIgnoreElements.java:84) at reactor.core.publisher.FluxFlatMap$FlatMapMain.checkTerminated(FluxFlatMap.java:846) at reactor.core.publisher.FluxFlatMap$FlatMapMain.drainLoop(FluxFlatMap.java:612) at reactor.core.publisher.FluxFlatMap$FlatMapMain.drain(FluxFlatMap.java:592) at reactor.core.publisher.FluxFlatMap$FlatMapMain.innerError(FluxFlatMap.java:867) at reactor.core.publisher.FluxFlatMap$FlatMapInner.onError(FluxFlatMap.java:994) at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onError(FluxHandleFuseable.java:229) at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:184) at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:670) at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drain(FluxWindowPredicate.java:748) at reactor.core.publisher.FluxWindowPredicate$WindowFlux.onNext(FluxWindowPredicate.java:790) at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onNext(FluxWindowPredicate.java:268) at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:91) at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107) at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:880) at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:805) at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:163) at io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:684) at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:936) at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:810) at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:716) at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:129) at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854) at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224) at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224) at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:294) at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:403) at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:426) at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:115) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346) at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333) at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:455) at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412) at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1407) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420) at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:918) at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:799) at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:501) at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:399) at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:994) 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:840) Caused by: io.r2dbc.postgresql.PostgresqlConnectionFactory$PostgresConnectionException: [08003] Cannot connect to localhost/:6432 at io.r2dbc.postgresql.PostgresqlConnectionFactory.cannotConnect(PostgresqlConnectionFactory.java:188) at io.r2dbc.postgresql.PostgresqlConnectionFactory.lambda$doCreateConnection$7(PostgresqlConnectionFactory.java:153) at reactor.core.publisher.Mono.lambda$onErrorMap$28(Mono.java:3854) at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94) ... 67 more Caused by: java.util.NoSuchElementException: Column name 'oid' does not exist in column names [typname, typnamespace, typowner, typlen, typbyval, typtype, typcategory, typispreferred, typisdefined, typdelim, typrelid, typelem, typarray, typinput, typoutput, typreceive, typsend, typmodin, typmodout, typanalyze, typalign, typstorage, typnotnull, typbasetype, typtypmod, typndims, typcollation, typdefaultbin, typdefault, typacl] at io.r2dbc.postgresql.PostgresqlRow.getColumn(PostgresqlRow.java:193) at io.r2dbc.postgresql.PostgresqlRow.get(PostgresqlRow.java:96) at io.r2dbc.postgresql.codec.BuiltinDynamicCodecs.lambda$null$0(BuiltinDynamicCodecs.java:107) at io.r2dbc.postgresql.PostgresqlResult.lambda$map$2(PostgresqlResult.java:129) at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:179) ... 42 more ```

Table schema

Input Code ```sql -- your SQL here; ```

Steps to reproduce

Input Code ```java // your code here; ```

Expected behavior/code

Possible Solution

Additional context

mp911de commented 1 month ago

There's a bit back and forth regarding OID handling in the built-in codec support.

Do you think running a query such as SELECT oid, * would address your issue?

AntonioHReyes commented 1 month ago

Yes, if we specify the oid field, the query works correctly. However, this happens under the library's hood, so I don't have a way to specify the oid in that query.

For now, I have left r2dbc and am using JPA, but I would really appreciate having this support in the future.