Please add a caveat for using JdbcCursorItemReader in the docs

[Sax388]

Hi there,

I spent a little (too much) time figuring out why my batch job was running into out-of-memory (OOM) errors until finally figuring out that with postgresql you have to disable auto-commit in your datasource (to prevent materializing the full result set in memory). In my case hikariDataSource.setAutoCommit(false) did the trick:

@Bean
@ConfigurationProperties("spring.datasource.revenue-message.configuration")
public HikariDataSource revenueMessagesDataSource(
  @Qualifier("revenueMessagesDataSourceProperties")
      DataSourceProperties revenueMessagesDataSourceProperties) {
  var hikariDataSource = revenueMessagesDataSourceProperties
          .initializeDataSourceBuilder()
          .type(HikariDataSource.class)
          .build();

  // To make use of JdbcCursorItemReader from Spring Batch, see https://jdbc.postgresql.org/documentation/query/#getting-results-based-on-a-cursor
  hikariDataSource.setAutoCommit(false);

  return hikariDataSource;
}

I looked at the docs here https://docs.spring.io/spring-batch/reference/readers-and-writers/database.html and I would have loved a small admonition box in which this oddity was mentioned <3.

Thanks in advance 🙂

[fmbenhassine]

I agree and I can relate, because I also spent as much time and energy (and lost some hair during debugging sessions) before discovering that MySQL only streams data when the fetch size is set to Integer.MIN_VALUE.. see "ResultSet" section here: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html.

Another similar caveat with PostgreSQL as well is when the item writer get stuck when trying to update rows selected by the reader using SELECT ... FOR UPDATE SKIP LOCKED, see https://stackoverflow.com/questions/77358701/how-to-enable-itemwriter-to-write-items-to-database-when-rows-are-locked-by-item/77373190#77373190.

Even though these are DB specific issues, it would be great if Spring Batch warns about them in the docs. I will turn this into a documentation enhancement and move it to the issue tracker.

[fmbenhassine]

Moved to the issue tracker as #5040