Repository: GoogleCloudPlatform/pgadapter
Pull request #1266 (2023-12-14 18:51:49)

feat: translate integrity constraint error codes
Translate integrity constraint errors and return the correct PostgreSQL error code.

Cloud Spanner returns its own error codes for PostgreSQL-dialect databases. These are by default returned by PGAdapter to the client as a generic PostgreSQL `P0001` (`raise_exception`) error. This means that PostgreSQL clients that have built-in logic that depend on specific PostgreSQL error codes will not work correctly with PGAdapter.

PGAdapter does translate a small set of specific Cloud Spanner errors to PostgreSQL error codes. Cloud Spanner `Aborted` errors are for example translated to PostgreSQL's `40001` (`serialization_failure`) error codes. This is the error code that PostgreSQL returns when isolation level `Serializable` is used, and the transaction failed due to a lock conflict.

This pull request adds translations for a couple of additional error codes that are known to be used by some Node.js frameworks to determine the type of error that is returned to the user. The translations that are added in this pull request are:
- Primary key violations: Translated to PostgreSQL `23505`
- Unique key violations: Translated to PostgreSQL ` 23505`
- Foreign key violations: Translated to PostgreSQL `23503`

The translations are done based on the error code and the error message that is returned by Cloud Spanner. Cloud Spanner does not return enough error details to do this translation without also matching on the error message.

We also need separate matches for Cloud Spanner and the Emulator, as these return different error messages.

The full list of error codes that is used by PostgreSQL can be found here: https://www.postgresql.org/docs/current/errcodes-appendix.html

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/1266?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Comparison is base [(`b55f924`)](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/commit/b55f92439036d319b35972ff668a954a8fc4a335?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) 89.99% compared to head [(`04e6fc3`)](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/1266?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) 89.93%.


<details><summary>Additional details and impacted files</summary>


```diff
@@                   Coverage Diff                    @@
##             postgresql-dialect    #1266      +/-   ##
========================================================
- Coverage                 89.99%   89.93%   -0.06%     
+ Complexity                 2593     2591       -2     
========================================================
  Files                       141      141              
  Lines                      8889     8896       +7     
  Branches                   1290     1290              
========================================================
+ Hits                       8000     8001       +1     
- Misses                      623      627       +4     
- Partials                    266      268       +2     
```

| [Flag](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/1266/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | Coverage Δ | |
|---|---|---|
| [all_tests](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/1266/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | `89.93% <100.00%> (-0.06%)` | :arrow_down: |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#carryforward-flags-in-the-pull-request-comment) to find out more.


</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/1266?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform).


Patch:
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactory.java b/src/main/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactory.java
index 347011c6..af8dd5dc 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactory.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactory.java
@@ -27,10 +27,26 @@ import java.util.regex.Pattern;
 public class PGExceptionFactory {
   private static final Pattern RELATION_NOT_FOUND_PATTERN =
       Pattern.compile("relation .+ does not exist");
+  private static final Pattern COLUMN_NOT_FOUND_PATTERN =
+      Pattern.compile("column .+ of relation .+ does not exist");
   private static final Pattern CANNOT_DROP_TABLE_WITH_INDICES_PATTERN =
-      Pattern.compile("Cannot drop table test with indices");
+      Pattern.compile("Cannot drop table .+ with indices");
   private static final Pattern ONLY_RESTRICT_BEHAVIOR =
       Pattern.compile("Only <RESTRICT> behavior is supported by <DROP> statement\\.");
+  private static final Pattern PK_VIOLATION_PATTERN =
+      Pattern.compile("Row .+ in table .+ already exists");
+  private static final Pattern PK_VIOLATION_PATTERN_EMULATOR =
+      Pattern.compile("Failed to insert row with primary key .+ due to previously existing row");
+  private static final Pattern UNIQUE_INDEX_VIOLATION_PATTERN =
+      Pattern.compile("Unique index violation on index .+ at index key .+");
+  private static final Pattern UNIQUE_INDEX_VIOLATION_PATTERN_EMULATOR =
+      Pattern.compile("UNIQUE violation on index .+ duplicate key: .+");
+  private static final Pattern FOREIGN_KEY_VIOLATION_PATTERN =
+      Pattern.compile(
+          "Foreign key constraint .+ is violated on table .+\\. Cannot find referenced values in .+");
+  private static final Pattern FOREIGN_KEY_VIOLATION_PATTERN_EMULATOR =
+      Pattern.compile(
+          "Foreign key .+ constraint violation on table .+\\. Cannot find referenced key .+ in table .+");
 
   private PGExceptionFactory() {}
 
@@ -85,12 +101,34 @@ public class PGExceptionFactory {
           .setSQLState(SQLState.SerializationFailure)
           .build();
     }
-    if (spannerException.getErrorCode() == ErrorCode.NOT_FOUND
-        || spannerException.getErrorCode() == ErrorCode.INVALID_ARGUMENT
-            && RELATION_NOT_FOUND_PATTERN.matcher(spannerException.getMessage()).find()) {
+    if ((spannerException.getErrorCode() == ErrorCode.NOT_FOUND
+            || spannerException.getErrorCode() == ErrorCode.INVALID_ARGUMENT)
+        && COLUMN_NOT_FOUND_PATTERN.matcher(spannerException.getMessage()).find()) {
+      return PGException.newBuilder(extractMessage(spannerException))
+          .setSQLState(SQLState.UndefinedColumn)
+          .build();
+    } else if ((spannerException.getErrorCode() == ErrorCode.NOT_FOUND
+            || spannerException.getErrorCode() == ErrorCode.INVALID_ARGUMENT)
+        && RELATION_NOT_FOUND_PATTERN.matcher(spannerException.getMessage()).find()) {
       return PGException.newBuilder(extractMessage(spannerException))
           .setSQLState(SQLState.UndefinedTable)
           .build();
+    } else if (spannerException.getErrorCode() == ErrorCode.ALREADY_EXISTS
+        && (PK_VIOLATION_PATTERN.matcher(spannerException.getMessage()).find()
+            || PK_VIOLATION_PATTERN_EMULATOR.matcher(spannerException.getMessage()).find()
+            || UNIQUE_INDEX_VIOLATION_PATTERN.matcher(spannerException.getMessage()).find()
+            || UNIQUE_INDEX_VIOLATION_PATTERN_EMULATOR
+                .matcher(spannerException.getMessage())
+                .find())) {
+      return PGException.newBuilder(extractMessage(spannerException))
+          .setSQLState(SQLState.UniqueViolation)
+          .build();
+    } else if (spannerException.getErrorCode() == ErrorCode.FAILED_PRECONDITION
+        && (FOREIGN_KEY_VIOLATION_PATTERN_EMULATOR.matcher(spannerException.getMessage()).find()
+            || FOREIGN_KEY_VIOLATION_PATTERN.matcher(spannerException.getMessage()).find())) {
+      return PGException.newBuilder(extractMessage(spannerException))
+          .setSQLState(SQLState.ForeignKeyViolation)
+          .build();
     } else if ((spannerException.getErrorCode() == ErrorCode.FAILED_PRECONDITION
             || spannerException.getErrorCode() == ErrorCode.INVALID_ARGUMENT)
         && CANNOT_DROP_TABLE_WITH_INDICES_PATTERN.matcher(spannerException.getMessage()).find()) {
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/error/SQLState.java b/src/main/java/com/google/cloud/spanner/pgadapter/error/SQLState.java
index a019918e..724cd1fb 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/error/SQLState.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/error/SQLState.java
@@ -135,6 +135,15 @@ public enum SQLState {
   SqlJsonScalarRequired("2203F"),
   SqlJsonItemCannotBeCastToTargetType("2203G"),
 
+  // Class 23 - Integrity Constraint Violation
+  IntegrityConstraintViolation("23000"),
+  RestrictViolation("23001"),
+  NotNullViolation("23502"),
+  ForeignKeyViolation("23503"),
+  UniqueViolation("23505"),
+  CheckViolation("23514"),
+  ExclusionViolation("23P01"),
+
   // Class 25 - Invalid Transaction State
   InvalidTransactionState("25000"),
   ActiveSqlTransaction("25001"),


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/ITErrorsTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/ITErrorsTest.java
new file mode 100644
index 00000000..bbf381ae
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/ITErrorsTest.java
@@ -0,0 +1,131 @@
+// Copyright 2023 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertThrows;
+
+import com.google.cloud.spanner.Database;
+import com.google.cloud.spanner.pgadapter.error.SQLState;
+import com.google.common.collect.ImmutableList;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.Collections;
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+import org.postgresql.util.PSQLException;
+
+@Category(IntegrationTest.class)
+@RunWith(JUnit4.class)
+public class ITErrorsTest implements IntegrationTest {
+  private static final PgAdapterTestEnv testEnv = new PgAdapterTestEnv();
+  private static Database database;
+
+  @BeforeClass
+  public static void setup() throws Exception {
+    // Make sure the PG JDBC driver is loaded.
+    Class.forName("org.postgresql.Driver");
+
+    testEnv.setUp();
+    database =
+        testEnv.createDatabase(
+            ImmutableList.of(
+                "create table my_table (id bigint primary key, value varchar)",
+                "create unique index my_index on my_table (value)",
+                "create table my_other_table (id bigint primary key, my_table_id bigint, constraint fk_my_table foreign key (my_table_id) references my_table (id))"));
+    testEnv.startPGAdapterServer(Collections.emptyList());
+  }
+
+  @AfterClass
+  public static void teardown() {
+    testEnv.stopPGAdapterServer();
+    testEnv.cleanUp();
+  }
+
+  @After
+  public void clearTestData() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(getConnectionUrl())) {
+      connection.createStatement().execute("delete from my_other_table");
+      connection.createStatement().execute("delete from my_table");
+    }
+  }
+
+  private String getConnectionUrl() {
+    return String.format(
+        "jdbc:postgresql://%s/%s",
+        testEnv.getPGAdapterHostAndPort(), database.getId().getDatabase());
+  }
+
+  @Test
+  public void testTableNotFound() throws SQLException {
+    verifyExpectedSQLState(
+        "insert into my_non_existing_table (id, ref) values (1, 1)", SQLState.UndefinedTable);
+  }
+
+  @Test
+  public void testColumnNotFound() throws SQLException {
+    verifyExpectedSQLState(
+        "insert into my_other_table (id, ref) values (1, 1)", SQLState.UndefinedColumn);
+  }
+
+  @Test
+  public void testPrimaryKeyConstraintViolation() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(getConnectionUrl())) {
+      assertEquals(
+          1,
+          connection
+              .createStatement()
+              .executeUpdate("insert into my_table (id, value) values (1, 'One')"));
+    }
+    verifyExpectedSQLState(
+        "insert into my_table (id, value) values (1, 'Other one')", SQLState.UniqueViolation);
+  }
+
+  @Test
+  public void testUniqueIndexViolation() throws SQLException {
+    try (Connection connection = DriverManager.getConnection(getConnectionUrl())) {
+      assertEquals(
+          1,
+          connection
+              .createStatement()
+              .executeUpdate("insert into my_table (id, value) values (1, 'One')"));
+    }
+    verifyExpectedSQLState(
+        "insert into my_table (id, value) values (2, 'One')", SQLState.UniqueViolation);
+  }
+
+  @Test
+  public void testForeignKeyConstraintViolation() throws SQLException {
+    verifyExpectedSQLState(
+        "insert into my_other_table (id, my_table_id) values (1, 1)", SQLState.ForeignKeyViolation);
+  }
+
+  private void verifyExpectedSQLState(String sql, SQLState expectedSQLState) throws SQLException {
+    try (Connection connection = DriverManager.getConnection(getConnectionUrl())) {
+      PSQLException exception =
+          assertThrows(PSQLException.class, () -> connection.createStatement().executeUpdate(sql));
+      assertNotNull(exception.getServerErrorMessage());
+      assertEquals(expectedSQLState.toString(), exception.getServerErrorMessage().getSQLState());
+      assertEquals(expectedSQLState.toString(), exception.getSQLState());
+    }
+  }
+}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactoryTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactoryTest.java
index 68c5556e..0a22b18c 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactoryTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/error/PGExceptionFactoryTest.java
@@ -58,6 +58,47 @@ public class PGExceptionFactoryTest {
                 SpannerExceptionFactory.newSpannerException(
                     ErrorCode.ABORTED, "The transaction was aborted"))
             .getSQLState());
+    assertEquals(
+        SQLState.UniqueViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.ALREADY_EXISTS, "Row [1] in table \"test\" already exists."))
+            .getSQLState());
+    assertEquals(
+        SQLState.UniqueViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.ALREADY_EXISTS,
+                    "Failed to insert row with primary key [1] due to previously existing row"))
+            .getSQLState());
+    assertEquals(
+        SQLState.UniqueViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.ALREADY_EXISTS,
+                    "Unique index violation on index my_unique_index at index key [1]"))
+            .getSQLState());
+    assertEquals(
+        SQLState.UniqueViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.ALREADY_EXISTS,
+                    "UNIQUE violation on index my_unique_index duplicate key: [1]"))
+            .getSQLState());
+    assertEquals(
+        SQLState.ForeignKeyViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.FAILED_PRECONDITION,
+                    "Foreign key constraint my_foreign_key is violated on table \"test\". Cannot find referenced values in \"referenced_table\""))
+            .getSQLState());
+    assertEquals(
+        SQLState.ForeignKeyViolation,
+        toPGException(
+                SpannerExceptionFactory.newSpannerException(
+                    ErrorCode.FAILED_PRECONDITION,
+                    "Foreign key my_foreign_key constraint violation on table \"test\". Cannot find referenced key [1] in table \"referenced_table\""))
+            .getSQLState());
   }
 
   @Test