The fetchSize setting is not being honored when using JDBC with a PreparedStatement

CrateDB SQL
1

We are using the CrateDB JDBC driver version 2.7.0 Below is the dependency

<dependency>
    <groupId>io.crate</groupId>
    <artifactId>crate-jdbc</artifactId>
    <version>2.7.0</version>
</dependency>

And attempting to use the fetchSize parameter as mentioned in the official documentation:
CrateDB PostgreSQL Interface – fetchSize

Unfortunately, it is not working as expected.

We have also followed all the prerequisites mentioned in the following PostgreSQL JDBC documentation:
PostgreSQL JDBC – Using Cursors

However, even when setting the fetchSize to 1000, if the query returns 10,000 records, the entire result set is still fetched at once, instead of being fetched in batches as expected.

Any help or guidance on this would be greatly appreciated.

1 Like
2

Did you disable auto commits? For example connection.setAutoCommit(false);

1 Like
3

Yes, I have already followed all of the points mentioned below:

  • The connection to the server is using the V3 protocol (default for server versions 7.4 and later).
  • The connection is not in autocommit mode, since cursors are closed at the end of transactions in autocommit mode.
  • The Statement is created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY (which is the default), so no changes were needed in that regard. I understand this also means the ResultSet cannot be scrolled or randomly accessed.
  • The query being executed is a single statement (not multiple statements separated by semicolons).

Despite meeting all these conditions, the fetchSize is still not being honored — the entire result set is returned at once instead of being fetched in smaller batches.

Any help or insights would be appreciated.

1 Like
4

Hi @atish11pune, I’m not sure I understand second point correctly. Do you mean you explicitly disabled autocommit (as adviced by @kneth) or you are implying that having cursors open == having autocommit disabled?

Explicitly disabling should work, see test methods in the autocommit and fetch size by chaudum · Pull Request #204 · crate/crate-jdbc · GitHub

We can’t imply autocommit setting from a “cursor state at the end of transaction” because CrateDB doesn’t support transactions, so there is no such thing as end of transaction in CrateDB.

See also SQL compatibility - CrateDB: Reference

1 Like