Extended flow queries hang with pgpool 4.1

[lyxgy841]

Bug Report

Versions

• Driver: 0.8.5.RELEASE
• Database: postgreSQL cluster with pgpool 4.1 (pgpool 4.0 works)
• Java: jdk8
• OS: win10

Current Behavior

I hava a PostgreSql cluster with Pgpool-II, other services connected to Pgpool using jdbc can run normally, but service using
r2dbc connect to pgpool, queries just hang forever.

[mp911de]

If you would like us to spend some time helping you to diagnose the problem, please spend some time describing it and, ideally, providing a minimal sample that reproduces the problem.

[lyxgy841]

this is demo https://github.com/lyxgy841/r2dbc-demo

[mp911de]

Thanks a lot. Here's an excerpt from the logs:

2020-11-04 14:59:13.678 DEBUG 45353 --- [actor-tcp-nio-9] i.r.p.client.ReactorNettyClient          : [cid: 0x6][pid: 1307] Response: ReadyForQuery{transactionStatus=IDLE}
2020-11-04 14:59:13.743 DEBUG 45353 --- [           main] io.r2dbc.postgresql.PARAM                : [cid: 0x6][pid: 1307] Bind parameter [0] to: Dave
2020-11-04 14:59:13.745 DEBUG 45353 --- [           main] io.r2dbc.postgresql.PARAM                : [cid: 0x6][pid: 1307] Bind parameter [1] to: Matthews
2020-11-04 14:59:13.749 DEBUG 45353 --- [           main] i.r.p.client.ReactorNettyClient          : [cid: 0x6][pid: 1307] Request:  [Parse{name='S_0', parameters=[1043, 1043], query='INSERT INTO customer (firstname, lastname) VALUES ($1, $2) RETURNING *'}, Flush{}]

The conversation stops after the flush request (P/H). Looking at JDBC, the sequence consists of Parse, Bind, Desc, Execute, Sync (P/B/D/E/S). While we do not combine all packets, it looks like we should include Sync. Going to test that.

[mp911de]

Interestingly, this issue happens with PGPool 4.1 but not with PGPool 4.0.x.

[mp911de]

To achieve a similar flow as with PGJDBC, we would need to change our statement caching API in the way that we generate a statement name but do not await ParseComplete before continuing with the flow. Instead, we should get hold of the parse message to append bind, desc etc.. We would have to register the prepared statement only when we receive ParseComplete.

Paging @davecramer and @Squiry to verify my approach.

[m4tt87]

Hi @mp911de,
I found the same problem in the past. To solve it, only as a workaround, I used the following dependencies:

implementation 'org.springframework.boot.experimental:spring-boot-starter-data-r2dbc:0.1.0.M3'.
implementation 'io.r2dbc:r2dbc-pool:0.8.0.RELEASE'.
implementation 'io.r2dbc:r2dbc-postgresql:0.8.0.RELEASE'.
Any subsequent version of the libraries above caused the problem.
PGPool version: 4.1.4

I recently tried to upgrade to the latest available version of spring-boot-starter-data-r2dbc, r2dbc-pool, and r2dbc-postgresql and the problem persists.

I hope that this additional information will help you in solving the problem.

[mp911de]

I had a look what it takes to make this work with pgpool 4.1.

We need to rewrite our query flow and change conceptually how the statement cache works. Basically, the cache needs to supply only details and no more initiate the query parsing on its own.

Beyond that, we're using an optimized flow with cursors that creates a cursor even in auto-commit mode. That is because we use Execute + Flush instead of Execute + Sync. That works pretty well when using a Postgres server directly. Pgpool fails to respond since version 4.1.

That means we require a compatibility mode where we use Execute + Sync. It's a pretty significant change. While the compatibility mode aligns with what PGJDBC does, we should not enable compatibility mode by default.

I have got a working prototype locally, but it would be good to get some reviewers once I can submit a PR.

[Squiry]

it would be good to get some reviewers once I can submit a PR

I beleive I can help you with that. But that sounds like broken fetch size for a pgpool.

[mp911de]

I submitted #373 containing the changes.

[dave-b-uk]

Hello,

Do you know when will this fix be released?

[mp911de]

#373 needs more reviews first before we can go into planning. Other than that, you can find release dates in the milestones.

[dave-b-uk]

Thanks for the heads up @mp911de !

[mp911de]

That's fixed now.