Back pressure question

[hughpearse]

Given this code

Flux<Map<String, Object>> resultFlux = databaseClient
.sql("SELECT TOP 101 * FROM ExampleTable") // Still fetch 101 from DB
.fetch()
.all()
.take(100); // Reactor stops after 100 items

resultFlux.subscribe(
row -> System.out.println("Row: " + row),
error -> System.err.println("Error: " + error),
() -> System.out.println("Query complete.")
);

Does this driver use back pressure to ensure the query is cancelled on the DBMS once the first 100 rows are received?

[Michael-A-McMahon]

Oracle R2DBC will close the cursor for the SQL command. This is necessary to free up memory on the database server.

[hughpearse]

Hi, thank you so much for confirming.

1. My service account has the grant for killing a query on oracle. We are using exadata. I did look on V$SESSION but didn't find anything useful really. All I could see was some queries were in waiting status, it didn't explicitly say they were killed. Maybe I need to check GV$SESSION ? How to verify query killing on the oracle side.

2. Maybe the oracle driver jar has a break point I can set? How to verify query killing on the client side?

3. Is there any TCP packet message for oracle wire protocolI can find in wire shark to verify the kill query message is being sent? How to verify query killing in the network traffic?

I'm unsure how to verify the back pressure is working.

[Michael-A-McMahon]

In this case, there's no cancellation packet sent to the database. That is, there is no packet sent to the database telling it to interrupt an ongoing operation, such as fetching rows.

Here's what will happen:

1. The Statement is configured to fetch rows in batches of size N.
2. The request for N rows is sent to the database.
3. N rows are received from the database
4. N rows are emitted downstream to a subscriber, in response to Subscription.request(long) signals.
5. Repeat steps 2-4 until: All rows are fetched (onComplete), the subscriber cancels (Subscription.cancel()), or the database responds with an error when requesting the next N rows (onError)

Let's say N=10, in the example you've posted. We'll cycle through steps 2-4 10 times, for a total of 100 rows emitted downstream. Oracle R2DBC is now waiting for the next Subscription.request signal before it will request the next 10 rows at step 2. Our take(100) operator means that a Subscription.cancel signal occurs, and we exit via step 5. In this case, there was no active request happening in the database, so there is no reason to send a cancellation packet.

Let's instead say N=200. At step 2, we request 200 rows from database, get back 101, emit the first 100 rows downstream, and then receive cancel. Here again, there's no active request to be cancelled.

[hughpearse]

Thank you very much. I'll try queries which return thousands of rows and use a mono to read only the first row. I'll set a break point on subscription cancel and trace the call

I will investigate this and get back to you on my progress