Repository: googleapis/java-spanner-jdbc
Pull request #1441 (2023-12-07 08:17:27)

deps: update dependency org.postgresql:postgresql to v42.7.1
[![Mend Renovate](https://app.renovatebot.com/images/banner.svg)](https://renovatebot.com)

This PR contains the following updates:

| Package | Change | Age | Adoption | Passing | Confidence |
|---|---|---|---|---|---|
| [org.postgresql:postgresql](https://jdbc.postgresql.org) ([source](https://togithub.com/pgjdbc/pgjdbc)) | `42.7.0` -> `42.7.1` | [![age](https://developer.mend.io/api/mc/badges/age/maven/org.postgresql:postgresql/42.7.1?slim=true)](https://docs.renovatebot.com/merge-confidence/) | [![adoption](https://developer.mend.io/api/mc/badges/adoption/maven/org.postgresql:postgresql/42.7.1?slim=true)](https://docs.renovatebot.com/merge-confidence/) | [![passing](https://developer.mend.io/api/mc/badges/compatibility/maven/org.postgresql:postgresql/42.7.0/42.7.1?slim=true)](https://docs.renovatebot.com/merge-confidence/) | [![confidence](https://developer.mend.io/api/mc/badges/confidence/maven/org.postgresql:postgresql/42.7.0/42.7.1?slim=true)](https://docs.renovatebot.com/merge-confidence/) |

---

### Release Notes

<details>
<summary>pgjdbc/pgjdbc (org.postgresql:postgresql)</summary>

### [`v42.7.1`](https://togithub.com/pgjdbc/pgjdbc/blob/HEAD/CHANGELOG.md#4271-2023-12-06-083400--0500)

##### Changed

-   perf: improve performance of PreparedStatement.setBlob, BlobInputStream, and BlobOutputStream with dynamic buffer sizing [PR #&#8203;3044](https://togithub.com/pgjdbc/pgjdbc/pull/3044)

##### Fixed

-   fix: Apply connectTimeout before SSLSocket.startHandshake to avoid infinite wait in case the connection is broken [PR #&#8203;3040](https://togithub.com/pgjdbc/pgjdbc/pull/3040)
-   fix: support waffle-jna 2.x and 3.x by using reflective approach for ManagedSecBufferDesc [PR #&#8203;2720](https://togithub.com/pgjdbc/pgjdbc/pull/2720) Fixes [Issue #&#8203;2690](https://togithub.com/pgjdbc/pgjdbc/issues/2720).
-   fix: NoSuchMethodError on ByteBuffer#position When Running on Java 8  when accessing arrays, fixes [Issue #&#8203;3014](https://togithub.com/pgjdbc/pgjdbc/issues/3014)
-   Revert "[PR #&#8203;2925](https://togithub.com/pgjdbc/pgjdbc/pull/2925) Use canonical DateStyle name" [PR #&#8203;3035](https://togithub.com/pgjdbc/pgjdbc/pull/3035)
    Fixes  [Issue #&#8203;3008](https://togithub.com/pgjdbc/pgjdbc/issues/3008)
-   Revert "[PR ##&#8203;2973](https://togithub.com/pgjdbc/pgjdbc/pull/2973) feat: support SET statements combining with other queries with semicolon in PreparedStatement" [PR #&#8203;3010](https://togithub.com/pgjdbc/pgjdbc/pull/3010)
    Fixes [Issue #&#8203;3007](https://togithub.com/pgjdbc/pgjdbc/issues/3007)
-   fix: avoid timezone conversions when sending LocalDateTime to the database [#&#8203;2852](https://togithub.com/pgjdbc/pgjdbc/pull/3010)  Fixes [Issue #&#8203;1390](https://togithub.com/pgjdbc/pgjdbc/issues/1390)
    ,[Issue #&#8203;2850](https://togithub.com/pgjdbc/pgjdbc/issues/2850)
    Closes \[Issue [#&#8203;1391](https://togithub.com/pgjdbc/pgjdbc/issues/1391)([https://github.com/pgjdbc/pgjdbc/issues/1391](https://togithub.com/pgjdbc/pgjdbc/issues/1391))

</details>

---

### Configuration

📅 **Schedule**: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).

🚦 **Automerge**: Disabled by config. Please merge this manually once you are satisfied.

♻ **Rebasing**: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.

🔕 **Ignore**: Close this PR and you won't be reminded about this update again.

---

 - [ ] <!-- rebase-check -->If you want to rebase/retry this PR, check this box

---

This PR has been generated by [Mend Renovate](https://www.mend.io/free-developer-tools/renovate/). View repository job log [here](https://developer.mend.io/github/googleapis/java-spanner-jdbc).
<!--renovate-debug:eyJjcmVhdGVkSW5WZXIiOiIzNy44Ny4yIiwidXBkYXRlZEluVmVyIjoiMzcuODcuMiIsInRhcmdldEJyYW5jaCI6Im1haW4ifQ==-->



Patch:
diff --git a/pom.xml b/pom.xml
index 90ac8a5..1f01bee 100644
--- a/pom.xml
+++ b/pom.xml
@@ -14,7 +14,7 @@
   <parent>
     <groupId>com.google.cloud</groupId>
     <artifactId>google-cloud-shared-config</artifactId>
-    <version>1.6.1</version>
+    <version>1.7.1</version>
   </parent>
   <developers>
     <developer>
diff --git a/samples/spring-data-jdbc/pom.xml b/samples/spring-data-jdbc/pom.xml
index d1dd574..1a11307 100644
--- a/samples/spring-data-jdbc/pom.xml
+++ b/samples/spring-data-jdbc/pom.xml
@@ -52,7 +52,7 @@
     <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
-      <version>42.7.0</version>
+      <version>42.7.1</version>
     </dependency>
 
     <dependency>
diff --git a/samples/spring-data-mybatis/pom.xml b/samples/spring-data-mybatis/pom.xml
index 4dbd092..03b3918 100644
--- a/samples/spring-data-mybatis/pom.xml
+++ b/samples/spring-data-mybatis/pom.xml
@@ -62,7 +62,7 @@
     <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
-      <version>42.7.0</version>
+      <version>42.7.1</version>
     </dependency>
 
     <dependency>
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/JdbcConnection.java b/src/main/java/com/google/cloud/spanner/jdbc/JdbcConnection.java
index 9248dac..4cef6b3 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcConnection.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcConnection.java
@@ -29,6 +29,7 @@ import com.google.cloud.spanner.connection.Connection;
 import com.google.cloud.spanner.connection.ConnectionOptions;
 import com.google.cloud.spanner.connection.SavepointSupport;
 import com.google.cloud.spanner.connection.TransactionMode;
+import com.google.common.base.Strings;
 import com.google.common.collect.ImmutableList;
 import com.google.common.collect.Iterators;
 import java.sql.Array;
@@ -57,14 +58,42 @@ class JdbcConnection extends AbstractJdbcConnection {
       "Only result sets with concurrency CONCUR_READ_ONLY are supported";
   private static final String ONLY_CLOSE_CURSORS_AT_COMMIT =
       "Only result sets with holdability CLOSE_CURSORS_AT_COMMIT are supported";
-  static final String IS_VALID_QUERY = "SELECT 1";
+
+  /**
+   * This query is used to check the aliveness of the connection if legacy alive check has been
+   * enabled. As Cloud Spanner JDBC connections do not maintain a physical or logical connection to
+   * Cloud Spanner, there is also no point in repeatedly executing a simple query to check whether a
+   * connection is alive. Instead, we rely on the result from the initial query to Spanner that
+   * determines the dialect to determine whether the connection is alive or not. This result is
+   * cached for all JDBC connections using the same {@link com.google.cloud.spanner.Spanner}
+   * instance.
+   *
+   * <p>The legacy {@link #isValid(int)} check using a SELECT 1 statement can be enabled by setting
+   * the System property spanner.jdbc.use_legacy_is_valid_check to true or setting the environment
+   * variable SPANNER_JDBC_USE_LEGACY_IS_VALID_CHECK to true.
+   */
+  static final String LEGACY_IS_VALID_QUERY = "SELECT 1";
 
   static final ImmutableList<String> NO_GENERATED_KEY_COLUMNS = ImmutableList.of();
 
   private Map<String, Class<?>> typeMap = new HashMap<>();
 
+  private final boolean useLegacyIsValidCheck;
+
   JdbcConnection(String connectionUrl, ConnectionOptions options) throws SQLException {
     super(connectionUrl, options);
+    this.useLegacyIsValidCheck = useLegacyValidCheck();
+  }
+
+  static boolean useLegacyValidCheck() {
+    String value = System.getProperty("spanner.jdbc.use_legacy_is_valid_check");
+    if (Strings.isNullOrEmpty(value)) {
+      value = System.getenv("SPANNER_JDBC_USE_LEGACY_IS_VALID_CHECK");
+    }
+    if (!Strings.isNullOrEmpty(value)) {
+      return Boolean.parseBoolean(value);
+    }
+    return false;
   }
 
   @Override
@@ -347,23 +376,38 @@ class JdbcConnection extends AbstractJdbcConnection {
     this.typeMap = new HashMap<>(map);
   }
 
+  boolean isUseLegacyIsValidCheck() {
+    return useLegacyIsValidCheck;
+  }
+
   @Override
   public boolean isValid(int timeout) throws SQLException {
     JdbcPreconditions.checkArgument(timeout >= 0, "timeout must be >= 0");
     if (!isClosed()) {
+      if (isUseLegacyIsValidCheck()) {
+        return legacyIsValid(timeout);
+      }
       try {
-        Statement statement = createStatement();
-        statement.setQueryTimeout(timeout);
-        try (ResultSet rs = statement.executeQuery(IS_VALID_QUERY)) {
-          if (rs.next()) {
-            if (rs.getLong(1) == 1L) {
-              return true;
-            }
+        return getDialect() != null;
+      } catch (Exception ignore) {
+        // ignore and fall through.
+      }
+    }
+    return false;
+  }
+
+  private boolean legacyIsValid(int timeout) throws SQLException {
+    try (Statement statement = createStatement()) {
+      statement.setQueryTimeout(timeout);
+      try (ResultSet rs = statement.executeQuery(LEGACY_IS_VALID_QUERY)) {
+        if (rs.next()) {
+          if (rs.getLong(1) == 1L) {
+            return true;
           }
         }
-      } catch (SQLException e) {
-        // ignore
       }
+    } catch (SQLException e) {
+      // ignore and fall through.
     }
     return false;
   }


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcConnectionTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcConnectionTest.java
index ff811f3..8d26bb3 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcConnectionTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcConnectionTest.java
@@ -502,7 +502,7 @@ public class JdbcConnectionTest {
         mock(com.google.cloud.spanner.connection.Connection.class);
     when(spannerConnection.getDialect()).thenReturn(dialect);
     when(options.getConnection()).thenReturn(spannerConnection);
-    Statement statement = Statement.of(JdbcConnection.IS_VALID_QUERY);
+    Statement statement = Statement.of(JdbcConnection.LEGACY_IS_VALID_QUERY);
 
     // Verify that an opened connection that returns a result set is valid.
     try (JdbcConnection connection = new JdbcConnection("url", options)) {
@@ -517,7 +517,7 @@ public class JdbcConnectionTest {
       }
 
       // Now let the query return an error. isValid should now return false.
-      when(spannerConnection.executeQuery(statement))
+      when(spannerConnection.getDialect())
           .thenThrow(
               SpannerExceptionFactory.newSpannerException(
                   ErrorCode.ABORTED, "the current transaction has been aborted"));