Connection.close()

[Lukas Eder]

Hey folks,

So, let's finally do this thing! https://github.com/jOOQ/jOOQ/issues/11700 😁

First step was proxing the ConnectionFactory to support a parsing / translating ConnectionFactory that can translate between SQL dialects (use jOOQ's SQL dialect as input to the R2DBC API and run the translated SQL on any backing driver). That was surprisingly easy.

Now, I'm going to tackle the main task: Letting jOOQ API implement Publisher, backed by R2DBC ConnectionFactory and run type safe queries on R2DBC. I'll  be asking my questions here. First one: Connection.close().

I don't fully get who is calling it. From what I understand, a ConnectionFactory is like a JDBC DataSource (e.g. implemented by a ConnectionFactory based connection pool), and Connection.close() will must be called to return it to the pool. 1) is this assumption correct? and 2) Am I also correct that if jOOQ fetches Connections from a ConnectionFactory, it's jOOQ's responsibility to call Connection.close() at the appropriate moment?

Cheers,

Lukas

[Mark Paluch]

Great news.

Both of your assumptions are true. A ConnectionFactory and Connection are the R2DBC equivalent of JDBC's DataSource and Connection.

> call Connection.close() at the appropriate moment?

The appropriate moment can be a tricky one. Although R2DBC is non-blocking, it's not enforcing any sort of action queue. Practically that means any work to be done on a connection must have finished before closing the connection. Closing the connection without awaiting query/command completion can terminate the activity on a connection while the command is being received/processed/…. When using Project Reactor, then Flux/Mono.usingWhen(…) is typically a good choice to implement a properly guarded resource closure.

Cheers,

Mark

[Dávid Karnok]

The Connection.close() issue is a complicated one dataflow-vise. Normally, with 1 Connection -> 1 Statement -> 1 Result -> N Rows flow, when all rows have been consumed, you'd append the close call before completing the end-subscriber:

createConnection()

.flatMap(connection ->

    connection.createStatement("SELECT 1 FROM DUAL")

    .execute()

    .map((row, rm) -> Record...)

    .concatWith(connection.close().cast(Record.class))

)

.subscribe(record -> ...);

However, if we add take(1) before subscribe(record -> ...), that would cancel the chain and connection.close() would not execute. Since there is no confirmation for a Subscription.cancel call, the simplest you can do is fire-and-forget close() upon cancellation:

createConnection()

.flatMap(connection ->

    connection.createStatement("SELECT 1 FROM DUAL")

    .execute()

    .map((row, rm) -> Record...)

    .concatWith(connection.close().cast(Record.class))

    .doOnCancel(() -> connection.close().subscribe()) // <-------------

)

.take(1)

.subscribe(record -> ...);

This is what essentially Reactor's usingWhen does (https://github.com/reactor/reactor-core/blob/79bcd6f37fa8342119170c6decb9fcfb079557a0/reactor-core/src/main/java/reactor/core/publisher/FluxUsingWhen.java#L342).

You could add some action queue indeed to serialize out retrieving rows and closing the connection so that in-flight rows do not get interrupted upon async cancellation. The trouble is, how would a timeout work then; i.e., waiting for a row that would not come in time but then can't cancel/close the connection until the row arrives? 

A similar problem arises with C# IAsyncEnumerable where DisposeAsnc() can't be called until MoveNextAsync returned, hence they needed to inject a CancellationToken to be able to interrupt the whole thing while MoveNextAsync was running (which then throws an exception).

[Lukas Eder]

Thanks folks. I've been progressing quite a bit last week. Luckily, jOOQ always knows what to expect (e.g. exactly N update counts vs exactly 1 result), such that the ideal moment to close the connection is known (in the case of a happy path). I think that getting the happy path right is already a start for the next jOOQ version. My current focus is to support as much as possible from the jOOQ API via R2DBC, including:

- Single result fetches

- Multi result fetches (possibly, they're esoteric enough with JDBC)

- Single update count queries (DML)

- Multi update count queries (all types of batches)

- Single row DML RETURNING statements

- Multi row DML RETURNING statements

- Stored procedures (possibly)

The interesting cases you've mentioned can still be fixed later.

I'm still hoping I can avoid adding a reactor (or whatever) dependency. The mess I have on my own classpath with 6 reactor versions because I added all 6 R2DBC drivers is hint enough that it would be great if jOOQ didn't add yet another reactor dependency.

Cheers,

Lukas

--
You received this message because you are subscribed to a topic in the Google Groups "r2dbc" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/r2dbc/a7CQAU_u_m0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to r2dbc+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/r2dbc/dd282621-0f7f-4ef5-b36a-52772b39fc87n%40googlegroups.com.