jdbc array resultsets cause issues with read-write-splitting plugin with spring boot

[treetip]

Describe the bug

In spring boot 3.4.x java.sql.Array#getResultSet causes a "underlying connection has changed" exception when the surrounding transaction/connection is read only.

Expected Behavior

no exception thrown

What plugins are used? What other connection properties were set?

?wrapperDialect=aurora-pg&wrapperProfileName=F0 with postgres 17.4 aurora cluster of 1rw, 1ro node

Current Behavior

Reading a jdbc array via its java.sql.ResultSet throws exception about the read only connection changing.

Reproduction Steps

@Component
@AllArgsConstructor
public FancyDao {

    private final NamedParameterJdbcTemplate jdbc;

    @Transactional(readOnly = true)
    public List<String> getStuffs() {
        var sql = """
                with stuff(text_col) as (
                  values ('a'),('b'),('c'),('d'),('a')
                )
                select array(select distinct text_col from stuff) as text_values""";
        var result = new ArrayList<String>();
        jdbc.query(sql, rs -> {
            result.addAll(collect(rs.getArray("text_values"), ResultSet::getString, Collectors.toList()));
        });
        return result;
    }

    static <T, A, R> R collect(Array array, RowMapper<T> mapper, Collector<T, A, R> collector) throws SQLException {
        var accumulator = collector.accumulator();
        var container = collector.supplier().get();
        if (array != null) {
            try (var rs = array.getResultSet()) {
                while (rs.next()) {
                    accumulator.accept(container, mapper.mapRow(rs, 2));
                }
            }
        }
        return collector.finisher().apply(container);
    }
}

Possible Solution

Not familiar with the JDBC spec, but the Array data should already be client side at this point, making checking the underlying connection a false positive.

Can also use java.sql.Array#getArray to go around the issue, but it's a bit of a trap.

Additional Information/Context

No response

The AWS Advanced JDBC Driver version used

2.5.6

JDK version used

temurin 21

Operating System and version

fedora 42

[treetip]

actual exception, up to internal code which starts at the above examples array.getResultSet()

 java.sql.SQLException: The internal connection has changed since 'org.postgresql.jdbc.PgResultSet@77b31135' was created. This is likely due to failover or read-write splitting functionality. To ensure you are using the updated connection, please re-create Statement and ResultSet objects after failover and/or calling setReadOnly.
      at software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:341)
      at software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:244)
      at software.amazon.jdbc.wrapper.ResultSetWrapper.next(ResultSetWrapper.java:1202)

[sergiyvamz]

Hi @treetip

Thank you for reaching out with this issue.

The exception in your code indicates a change in the connection context (database session context). At the same time, the connection reference held by your final ResultSet has become stale and differs from the current connection. It's quite challenging to verify if the ResultSet has already fetched the data array at the time of the exception. To prevent data loss or corruption, the AWS Wrapper consistently checks the current connection against the connection in bound objects (Statement, ResultSet). If a mismatch is detected, an exception is thrown as a safeguard. It is suggested that an application to be designed to handle this error gracefully and implement logic to re-run the failed transaction when this exception occurs.

We suggest you to examine your application logs and the AWS JDBC Wrapper Driver logs and identify the root cause of the connection context change. For detailed information on configuring driver logging, refer to the AWS JDBC Wrapper documentation:
https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/UsingTheJdbcDriver.md#logging

The connection issue you're experiencing could stem from several potential root causes:

• Network Instability: Intermittent network problems may be disrupting the connection.
• Cluster Failover handling: The system might be attempting to manage a cluster failover scenario.
• ReadWriteSplitting Plugin activity: The behaviour of this plugin could be affecting the connection context.

Your application is utilizing Configuration Profile F0, which incorporates the following plugins:

• ReadWriteSplitting Plugin
• Failover Plugin
• HostMonitoring Plugin

It's important to note that each of these plugins has the capability to alter the connection context. To identify the precise root cause, we strongly recommend examining the detailed logs generated by the driver. These logs should provide valuable insights into the specific actions taken by each plugin and any changes in the connection context.

If you need assistance interpreting the logs or require further guidance, please don't hesitate to reach out with more details or specific log excerpts.

[treetip]

Did some debugging of this, the cause seems to be:

1. code calls resultSet.getArray("text_values") -> returns wrapped array
2. code calls Array#getResultset on wrapped array
3. code calls ResultSet#next on wrapped resultset
4. in ConnectionPluginManager#execute#335 methodInvokeOn target is unwrapped PGResultSet which returns unwrapped PGConnection
5. conn != this.pluginService.getCurrentConnection is true -> exception raised

If you look at this debugger view, you can see that the wrapped and unwrapped connection are actually the same, so the comparison just doesn't work for Array#getResultSet -> ResultSet#next as the arrays resultset has a reference to the internal PGConnection

[aaron-congo]

Hi @treetip,

Thank you for reporting this issue. I have taken a look and have been able to reproduce the problem. To give you some context, because the driver switches underlying connections when setReadOnly is called, we perform a comparison check between the connection associated with the JDBC object in use and the current underlying connection. This exception is thrown when the check detects a mismatch in underlying connections, to protect against unintentional usage of JDBC objects associated with old connections. It seems you have uncovered an edge case where this comparison check is not working correctly. We will have to investigate a proper fix.

In the meantime, as a workaround, does it work for you to cast the SQL Array Object to a String[] like they do in this article instead of using Array#getResultSet? If you do that, you will not need to call your collect() method, you will only need to convert the String[] to List