Postgresql – the new JDBC 4.3 Connection Request feature with `beginRequest` & `endRequest` methods

javajdbcpostgresqlsharding

The JDBC 4.3 update arrived with Java 9 2017-10. Amongst its few changes is the new feature on Connection where the beginRequest and endRequest methods signals…

Hints to the driver that a request, an independent unit of work, is beginning on this connection. Each request is independent of all other requests with regard to state local to the connection either on the client or the server. Work done between beginRequest, endRequest pairs does not depend on any other work done on the connection either as part of another request or outside of any request. A request may include multiple transactions. There may be dependencies on committed database state as that is not local to the connection.

…and…

… is optional, vendor specific and should largely be transparent.

➠ Please explain the purpose of this feature.

It seems to be about something larger than transactions yet is not about two-phase commits. Is it related to sharding, the major new feature in JDBC 4.3?

Is it some kind of scope that wraps around multiple transactions?
What databases make use of this feature?
- In particular, does Postgres, H2, or or Microsoft SQL Server have such a feature?

I am not asking about whether these databases have JDBC drivers updated yet for JDBC 4.3 yet. I am asking about the bigger picture, what is the meaning and purpose of a "request" in the context of a database connection across connections.

Oddly, the JDBC 4.3 specification makes only this one mention, with no explanation:

JDBC API changes

The following changes were made to existing JDBC interfaces..

java.sql.Connection

Added the methods beginRequest,endRequest, setShardingKey,
setShardingKeyIfValid.

Best Answer

These new JDBC4.3 APIs beginRequest and endRequest have to do with connection management. It's really a hint provided by the connection pool to the driver that a connection has been checked out of the pool (beginRequest) or checked back into the pool (endRequest). Without these APIs the driver has really no clue about request boundaries. The driver can then use these request demarcations to do dirty laundry like pinging the database, replacing the connection without another one for load balancing, clearing out dirty states, etc.

These APIs were defined as Oracle specific APIs in the Oracle JDBC thin driver before JDBC4.3. Oracle's UCP (Universal Connection Pool) already calls these APIs. Now that they are part of the standard, other connection pools can start using them (and other drivers will implement them).

Related Solutions

The JDBC URL for Oracle’s Data Guard Fast-Start Failover

You don't setup Fast-Start Failover in the URL. It's something that you setup on the data source using the setFastConnectionFailoverEnabled(true) function call.

Per here

An application enables Fast Connection Failover by calling setFastConnectionFailoverEnabled(true) on a DataSource instance, before retrieving any connections from that instance.

You cannot enable Fast Connection Failover when reinitializing a connection cache. You must enable it before using the OracleDataSource instance.

For example

    ods = new OracleDataSource();
    ...
    ods.setFastConnectionFailoverEnabled(true);

Also, it mentions:

FAST CONNECTION FAILOVER PREREQUISITES

1.) The implicit connection cache is enabled. Fast Connection Failover works in conjunction with the JDBC connection caching mechanism. This helps applications manage connections to ensure high availability.

2.) The application uses service names to connect to the database; the application cannot use service IDs (ORACLE_SID). Use a service name rather than an SID when setting the OracleDataSource url property.

3.) The underlying database has Release 10 (10.1) or Release 10 (10.2) Real Application Clusters (RAC) capability. If failover events are not propagated, connection failover cannot occur.

4.) Oracle Notification Service (ONS) is configured and available on the node where JDBC is running and on the Oracle RAC. JDBC depends on ONS to propagate database events from RAC and notify JDBC of them. If ONS is not correctly set up, then implicit connection cache creation fails and an ONSException is thrown at the first getConnection request.

5.) The JVM in which your JDBC instance is running must have oracle.ons.oraclehome set to point to your ORACLE_HOME. For example:

-Doracle.ons.oraclehome=C:\oracle\product\10.2.0\db_1

6.) Set the FastConnectionFailoverEnabled property before making the first getConnection request to an OracleDataSource. When Fast Connection Failover is enabled, the failover applies to all connections in the connection cache. If your application explicitly creates a connection cache using the Connection Cache Manager, then you must first set FastConnectionFailoverEnabled before retrieving any connections.

7.) The ons.jar must be part of the CLASSPATH for the application. The ons.jar can be found in the Oracle Client installation.

Edit: One last thought, if there ever is a failover, you will need to handle the SQLException:

ORA-17008: Closed Connection

Postgresql – Postgres synchronized backup – what’s with the archive

Wal archive is useful for backup purposes. For example - you make full backup every day, and keep wal segments since last abckup, thanks to this you can always get database to any state that it was during the day (for example - just before someone did run: drop table users cascade;).

Best Answer

Related Solutions

The JDBC URL for Oracle’s Data Guard Fast-Start Failover

Postgresql – Postgres synchronized backup – what’s with the archive

Related Question