Is jOOQ's fetchLazy() truly lazy?

Question

I'm using jOOQ 3.4.2 to query a PostgreSQL database using Scala. I want to fetch small chunks of the result set at a time without loading the entire result set into memory, as the query could return many rows. I have the following code:

val query = context.selectFrom(table)
                   .where(conditions)
                   .orderBy(orderField)
                   .fetchSize(1)
val cursor = query.fetchLazy()
// Iterate through cursor using cursor.fetchOne()

It appears that jOOQ will load the entire result set (or at least a huge chunk of the result set) in memory when I call fetchLazy(), before I even fetch the first row from the cursor (judging by the large number of recvfrom() syscalls I'm seeing while fetchLazy() is being called). Am I doing something wrong?

Answer 1

Am I doing something wrong?

Not from the jOOQ side. This is exactly how you ensure that both jOOQ (via ResultQuery.fetchLazy() and Cursor.fetchOne()) and the JDBC driver (via ResultQuery.fetchSize(), which translates to PreparedStatement.setFetchSize()) will fetch exactly one row at a time.

You may, however, have run into this PostgreSQL JDBC driver caveat, saying that for fetchSize to work, you have to run the query in an explicit transaction.

Answer 2

If somebody has the same problem with MySQL:

MySQL requires fetch size to be set to Integer.MIN_VALUE to indicate to the driver that the result should be fetched row-by-row: https://www.jooq.org/javadoc/3.9.1/org/jooq/ResultQuery.html#fetchSize

Doing this will make jOOQ's fetchLazy() work without loading the whole result set into memory.