JdbcStepExecutionDao.getLastStepExecution nests a query inside an open row-limited ResultSet, breaking every restart on CockroachDB

[sean-huni]

Summary

JdbcStepExecutionDao.getLastStepExecution issues a nested query from inside an open, row-limited ResultSet. On PostgreSQL this is fine. On CockroachDB it is rejected outright, so any step that already has a prior StepExecution under the same JobInstance fails, and the connection is discarded as broken. In practice that means restarts.

This is a portability defect rather than a performance one, and it is not caught by the existing test suite because it only manifests on a database that refuses multiple simultaneously-open portals.

Affected versions

Present in 6.0.4 (current release) and on main as of today.

The code

spring-batch-core/src/main/java/org/springframework/batch/core/repository/dao/jdbc/JdbcStepExecutionDao.java, in getLastStepExecution:

return getJdbcTemplate().query(getQuery(GET_LAST_STEP_EXECUTION), (PreparedStatementCallback<StepExecution>) statement -> {
    statement.setMaxRows(1);                       // <-- opens a row-limited portal
    ...
    try (ResultSet rs = statement.executeQuery()) {
        if (rs.next()) {
            Long jobExecutionId = rs.getLong(19);
            JobExecution jobExecution = new JobExecution(jobExecutionId, jobInstance,
                    jobExecutionDao.getJobParameters(jobExecutionId));   // <-- nested query, cursor still open
            ...

JdbcJobExecutionDao.getJobParameters prepares and executes a second statement on the same connection while the first cursor is still open.

Why setMaxRows(1) matters

The nesting alone is not sufficient. The distinction that matters is suspended and still executable versus completed, not simply open versus closed.

setMaxRows(1) makes pgjdbc send Execute with a protocol row count of 1. Per the protocol, if a non-zero row count stops execution before the portal completes, the backend replies PortalSuspended rather than CommandComplete. pgjdbc has already sent Sync, but because the connection is inside an explicit transaction that Sync yields ReadyForQuery(T) without ending the transaction or disposing the suspended portal. The nested query then attempts its protocol preparation while that portal is still actively suspended, and CockroachDB rejects it.

By contrast LIMIT 1 in the SQL, with no JDBC max rows, produces Execute(portal, 0), which fetches to completion and yields CommandComplete. That is why the two are not interchangeable here.

Relevant detail: on this setMaxRows / fetchSize == 0 path pgjdbc uses the unnamed portal and returns no named ResultCursor, so its explicit portal-cleanup machinery does not apply. Closing the Java ResultSet therefore does not necessarily emit a wire-level Close for the suspended unnamed portal.

Verified empirically in an equivalent implementation: LIMIT 1 works, switching to statement.setMaxRows(1) reintroduces the failure, removing it resolves it.

Qualifications, so this is not overstated:

• setMaxRows(1) does not guarantee PortalSuspended. A zero-row result reaches CommandComplete.
• A positive fetchSize can create the same condition via a named fetch portal, so setMaxRows is not the only trigger.
• The explanation does not apply in simple-query mode.
• Prepared-statement caching and prepareThreshold can affect reproduction, since a subsequent unnamed Bind normally replaces the previous unnamed portal.
• The wire state above is inferred from behaviour, not captured. A pgjdbc FINEST trace or pgwire capture showing Execute(portal=null, limit=1) then PortalSuspended then Sync then ReadyForQuery(T) then Parse then ErrorResponse would confirm it. I have not captured one.

Observed failure

org.postgresql.util.PSQLException: ERROR: unimplemented: multiple active portals is in preview,
please set session variable multiple_active_portals_enabled to true to enable them
  Detail: cannot perform operation sql.PrepareStmt while a different portal is open
  Hint: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/40195/v26.2
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2904)
	...
	at org.springframework.batch.core.repository.dao.jdbc.JdbcJobExecutionDao.getJobParameters(JdbcJobExecutionDao.java:450)
	at org.springframework.batch.core.repository.dao.jdbc.JdbcStepExecutionDao.lambda$getLastStepExecution$0(JdbcStepExecutionDao.java:341)
	at org.springframework.batch.core.repository.dao.jdbc.JdbcStepExecutionDao.getLastStepExecution(JdbcStepExecutionDao.java:332)
	at org.springframework.batch.core.repository.explore.support.SimpleJobExplorer.getLastStepExecution(SimpleJobExplorer.java:267)
	at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:95)

HikariCP then marks the connection broken (SQLSTATE 0A000), which surfaces downstream as Connection is closed and JDBC rollback failed, so the original cause is easy to misdiagnose.

Impact and why it looks intermittent

To be precise about the trigger, since "restart" is not quite the right framing: SimpleStepHandler.handleStep always runs with a JobInstance present and calls getLastStepExecution on ordinary first attempts too, simply getting null back.

The dangerous condition is narrower: a prior StepExecution exists for the same JobInstance and the same step name, so rs.next() is true and the nested hydration query actually runs. A first execution with a fresh step name is safe because the outer query returns zero rows, the Execute completes rather than suspending, and rs.next() is false.

In our case this presented as one service failing every attempt while others were unaffected, which sent us looking at the service rather than the framework. SimpleStepExecutionSplitter calls getLastStepExecution once per partition, so partitioned steps multiply the exposure, but partitioning is a multiplier rather than the cause.

Reproduction

Against cockroachdb/cockroach:v26.2.3 (Testcontainers), with a JDBC JobRepository and no CockroachDB session flags set:

1. Run any job to completion or failure so a JobInstance exists.
2. Relaunch the same job instance so Spring Batch takes the restart path.
3. getLastStepExecution throws the error above.

On PR #5133

#5133 refactors this method to reuse JdbcJobExecutionDao::getJobExecution, and at first glance looks like it would resolve this. It does not: the replacement call is still made from inside the open ResultSet.

+   return new StepExecutionRowMapper(
+           jobExecutionDao.getJobExecution(rs.getLong("JOB_EXECUTION_ID")))
+       .mapRow(rs, 0);

Since getJobExecution issues more statements than the getJobParameters call it replaces, the nesting would arguably widen. Worth noting in case that PR is considered a fix for this.

Suggested direction

Hoist the dependent read out of the cursor rather than changing which call is nested: read the row (or just its id) first, let that statement reach CommandComplete, then resolve the JobExecution. getStepExecution(long) already performs the sequential equivalent and could be delegated to.

Important subtlety: closing the Java ResultSet is not sufficient on its own. The first query must actually complete, which means limiting via SQL LIMIT 1 rather than JDBC setMaxRows(1). Otherwise the id query leaves its own suspended portal and the problem simply moves.

Happy to open a PR if the maintainers would like it in that shape.

Workaround for anyone hitting this

Either enable multiple_active_portals_enabled on the CockroachDB connection the JobRepository uses (it is a preview feature), or override JdbcStepExecutionDao.getLastStepExecution via a JdbcJobRepositoryFactoryBean subclass so it reads the id first and then delegates to getStepExecution(long). We took the latter route to avoid the preview flag.

---

Wire-protocol account in this report was reviewed and corrected after an independent technical review; an earlier revision attributed the behaviour to a deferred Close at the next Sync, which is wrong on two counts (pgjdbc sends Sync immediately, including inside explicit transactions, and Sync is not a deferred Close). The observable behaviour and the fix are unchanged.

[quaff]

Spring Batch uses standard JDBC features, I'm not sure it should be compromised for a non-mainstream database.

[sean-huni]

Correcting my own report: the wire-protocol explanation in the original revision was wrong, and I have edited the body. Flagging it here so anyone who already read it sees what changed. The observable behaviour, the reproduction and the suggested fix are unchanged.

What was wrong. I wrote that "pgjdbc defers the portal Close message to the next Sync". That is incorrect on two counts. pgjdbc sends Sync immediately after the extended-query messages, including inside explicit transactions; and Sync is not a deferred Close, they are distinct messages.

Corrected account. setMaxRows(1) makes pgjdbc send Execute with a protocol row count of 1. If that count stops execution before the portal completes, the backend replies PortalSuspended rather than CommandComplete. Sync has already been sent, but inside an explicit transaction it yields ReadyForQuery(T) without ending the transaction or disposing the suspended portal, so the nested query prepares while that portal is still actively suspended. LIMIT 1 with no JDBC max rows instead produces Execute(portal, 0), which fetches to completion and yields CommandComplete, which is why the two are not interchangeable.

Also: the distinction is suspended and still executable versus completed, not "open versus closed" as I originally put it.

Two other corrections.

I claimed getLastStepExecution "is only reached when a job instance already exists". That is not right, since SimpleStepHandler.handleStep always has a JobInstance and calls it on first attempts too, simply receiving null. The actual trigger is narrower: a prior StepExecution for the same JobInstance and step name, so rs.next() is true and the nested query runs. Partitioning multiplies the exposure rather than causing it.

And on the suggested fix: closing the Java ResultSet is not sufficient by itself. The first query has to reach CommandComplete, which means LIMIT 1 in SQL rather than setMaxRows(1), otherwise the id query leaves its own suspended portal and the problem just moves.

Caveat I should have stated originally. The wire state is inferred from behaviour, not captured. A pgjdbc FINEST trace or pgwire capture showing Execute(portal=null, limit=1), PortalSuspended, Sync, ReadyForQuery(T), Parse, ErrorResponse would settle it. I have not captured one, and I would not want the report read as if I had.

[quaff]

which means limiting via SQL LIMIT 1 rather than JDBC setMaxRows(1)

LIMIT 1 is not supported by all databases.