gdbReadWriteSplittingPlugin does not support Global Write Forwarding

[ivankhoosty]

Describe the bug

with restrictWriterToHomeRegion=true the plugin throws writerNotInHomeRegion exception and blocks connect attempt
with restrictWriterToHomeRegion=false it attempts to connect to the writer in another region and times out, which is not possible in our setup (regions are isolated, and GWF is enabled)

Expected Behavior

plugin should support GWF and allow connections to go to the reader nodes in passive region

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

gdbReadWriteSplittingPlugin,gdbFailover,iam,efm2,auroraConnectionTracekr,initialConnection

Current Behavior

see description

Reproduction Steps

enable GWF and disable cross regional traffic

Possible Solution

additional property, perhaps allowGlobalWriteForwarding=true which tells the plugin to connect to local readers if the region is passive

Additional Information/Context

No response

The AWS Advanced JDBC Wrapper version used

3.2.0

JDK version used

21

Operating System and version

unix

[sergiyvamz]

Hi @ivankhoosty

Thank you for raising this issue.

Just wanted to confirm my understanding of the use case. I assume your application always connects to a particular region and performs (mostly?) read operations. For write DML, you'd prefer to keep the connection in the same region and use Global Write Forwarding.

Would the following driver configuration be a good fit for this case? The initialConnection plugin can effectively replace the cluster reader endpoint with an instance endpoint in the same region, helping to load balance traffic. Write DML would then be intercepted by the connected instance itself and forwarded to the current writer. I may be missing some edge cases — please help me fill in the gaps.

Connection host: regional cluster reader endpoint
plugins: gdbFailover,iam,efm2,auroraConnectionTracker,initialConnection

[ivankhoosty]

Hi @sergiyvamz

let me give some more background in addition to #1527 (comment)

CURRENTLY: with 2.6.8 (or 3.2.0 with aurora-pg dialect and wrapperPlugins workaround):

us-east-1 : global rds aurora postgres with 1 writer + 3 reader nodes, "replacable datasource" wraps AwsWrapperDatasource with the aws driver configured to use initialConnection,auroraConnectionTracker,iam,failover2,readWriteSplitting,efm2 - this is considered active region, and NLB routes all customer traffic to this region. we actually have two datasources: read/write, which uses cluster endpoint, and read-only, which uses cluster (-ro) endpoint. thanks to readWriteSplitting round-robin we fully utilise all reader nodes.

how do we decide if the region is "active" or "passive"? we do the same that you do with topology queries against db, but use rds api to query the state of rds and determine which region has the writer, roughly every 30 seconds. the key is that the same app/image is deployed across all regions, and it dynamically reacts to regional failover/switchovers, without any pod restarts.

therefore, for example, in us-west-2: global rds aurora postgress with 4 reader nodes, "replacement datasource" wraps HikariDatasource with the aws driver configured to only use iam plugin. this is the passive region state, NLB does not route any customer traffic here, but thanks to GFW we are able to run write/update commands for some monitoring/status processes. we still have two datasources here for code/config simplicity, but because cluster and cluster-ro endpoints resolve to a single (random) reader nodes, we don't have the same utilisation of reader nodes as we have in active region with readWriteSplitting - it's ok, it's a passive region, no customer traffic , thus acceptable "waste". This region is basically hot-standby.

when regional switchover/failover is triggered, we detect this via rds api and :
in us-east-1, "replacable datasource" dumps AwsWrapperDatasource and creates a HikariDatasource with just iam, NLB stops sending traffic
in us-west-2, "replacable datasource" dumps HikariDatasource and creates AwsWrapperDatasource with all the plugins and NLB sends traffic here.

reminder - we do not have any cross regional communication, the EKS in us-west-2 cannot connect to rds nodes in us-east-1.

IDEAL setup, utilising global db awareness and gdb plugins you introduced in 3.2.0:
we remove "replacable datasource" completely and just have AwsWrapperDatasource configured with gdbReadWriteSplittingPlugin,gdbFailover,iam,efm2,auroraConnectionTracker,initialConnection (in the right order, just copy/pasting), ALL gdb plugins configured to "home" region only, and the driver detects regional swtichover via its (new global) topology queries and rebuilds internal hikari pools etc... gdbReadWriteSplittingPlugin not being able to support GWF is what's stopping this.

what you suggested is removing gdbReadWriteSplittingPlugin from plugin list, but this is not possible, as the same app/image is deployed across all regions, and plugins list is set on startup, so the only way to modify them is to have a "replacable datasourse" wrapper again, which defeats the purpose.

conceptually the change would look something like this:

@OverRide
protected void initializeWriterConnection() throws SQLException {
if (this.restrictWriterToHomeRegion) {
HostSpec writerHost = getWriter();

if (writerHost != null
    && !this.homeRegion.equalsIgnoreCase(writerHost.getRegion())) {
  if ("global-write-forwarding".equals(this.writeForwardingMode)) { <--- new property
    // Stay on current connection — GWF will handle writes
    LOGGER.fine("Writer is in region " + writerHost.getRegion()
        + "; using current connection with Global Write Forwarding");
    return;  // <- No exception, no cross-regional connection attempt
  }

  throw new ReadWriteSplittingSQLException(...);  // strict mode (default)
}

}

super.initializeWriterConnection();
}

does that help?

side note: i mentioned the driver "rebuilding the hikari pools". we have an implementation of HikariPoolMapping something along these lines:

return (HostSpec hostSpec, Properties targetDatasourceProperties) ->
targetDatasourceProperties.getProperty(PropertyDefinition.USER.name) + "-" datasourceName + "-" + hostSpec.getRole().name()

which means when a host flips from reader to writer or vice versa, the driver creates a new pool with the obvious name in the logs so it's very easy to track what's happening during regional failover.