org.postgresql.util.PSQLException Java Examples

private DataAccessException doTranslate(Throwable sourceThrowable, SQLException sqlException) {
  SQLException relevantSqlException;
  if (sqlException instanceof BatchUpdateException) {
    relevantSqlException = sqlException.getNextException();
  } else {
    relevantSqlException = sqlException;
  }

  DataAccessException translatedException;
  if (relevantSqlException instanceof PSQLException) {
    translatedException = doTranslate(sourceThrowable, (PSQLException) relevantSqlException);
  } else {
    translatedException = null;
  }
  return translatedException;
}
 

/** Package private for testability */
ReadonlyValueException translateReadonlyViolation(
    Throwable sourceThrowable, PSQLException pSqlException) {
  Matcher matcher =
      Pattern.compile(
              "Updating read-only column \"?(.*?)\"? of table \"?(.*?)\"? with id \\[(.*?)] is not allowed")
          .matcher(pSqlException.getServerErrorMessage().getMessage());
  boolean matches = matcher.matches();
  if (!matches) {
    LOG.error(ERROR_TRANSLATING_POSTGRES_EXC_MSG, pSqlException);
    throw new RuntimeException(ERROR_TRANSLATING_EXCEPTION_MSG, pSqlException);
  }
  String colName = matcher.group(1);
  String tableName = matcher.group(2);
  String id = matcher.group(3);

  String attributeName = tryGetAttributeName(tableName, colName).orElse(null);
  String entityTypeId = tryGetEntityTypeName(tableName).orElse(null);
  return new ReadonlyValueException(entityTypeId, attributeName, id, sourceThrowable);
}
 

@Test
public void testJPATimeout() {
    doInJPA(entityManager -> {
        try {
            List<Post> posts = entityManager
            .createQuery(
                "select p " +
                "from Post p " +
                "where function('1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(2) ) --',) is ''", Post.class)
            .getResultList();

            fail("Timeout failure expected");
        } catch (Exception e) {
            PSQLException rootCause = ExceptionUtil.rootCause(e);
            assertTrue(rootCause.getMessage().contains("canceling statement due to user request"));            }
    });
}
 

/**
 * work with same profile defined multiple times
 *
 * @throws Exception if test fails to run
 */
@Test(groups = { "profile" })
public void duplicateProfile() throws Exception {

    exTable.setProfile(null);
    exTable.setUserParameters(new String[] {
            "Profile=" + EnumPxfDefaultProfiles.HdfsTextSimple.toString().toUpperCase(),
            "Profile=" + EnumPxfDefaultProfiles.HdfsTextSimple
    });

    try {
        gpdb.createTableAndVerify(exTable);
        Assert.fail("Exception should have been thrown because of duplicate profile");
    } catch (PSQLException e) {
        ExceptionUtils.validate(null, e,
                new PSQLException("ERROR: .?nvalid URI pxf://" + exTable.getPath() +
                        "\\?Profile=HDFSTEXTSIMPLE&Profile=HdfsTextSimple: " +
                        "Duplicate option\\(s\\): PROFILE", null), true);
    }
}
 

@Test
public void testConnPropertiesNegative() throws Exception {
  Map<String, String> connProperties = new HashMap<>();
  connProperties.put("user", "postgresX");
  connProperties.put("password", "");

  String uri = PgSQLTestServer.getInstance().getJdbcUrl().split("\\?")[0];
  Tablespace space = new PgSQLTablespace("t1", URI.create(uri), getJsonTablespace(connProperties));
  try {
    space.init(new TajoConf());
    fail("Must be failed");
  } catch (IOException ioe) {
    assertTrue(ioe.getCause() instanceof PSQLException);
  } finally {
    space.close();
  }
}
 

/**
 * Test unsupported type in writable resolver -- negative
 *
 * @throws Exception if test fails to run
 */
@Test(groups = { "features", "gpdb", "hcfs", "security" })
public void negativeCharType() throws Exception {

    String[] fields = { "a1 INTEGER", "c1 CHAR" };
    String hdfsDir = hdfsWritePath + writableTableName + "char";

    writableExTable.setName("wr_char");
    writableExTable.setFields(fields);
    writableExTable.setPath(hdfsDir);
    writableExTable.setDataSchema(schemaPackage + customSchemaWithCharFileName);
    gpdb.createTableAndVerify(writableExTable);

    Table dataTable = new Table("data", null);
    dataTable.addRow(new String[] { "100", "a" });
    dataTable.addRow(new String[] { "1000", "b" });

    try {
        gpdb.insertData(dataTable, writableExTable);
        Assert.fail("Insert data should fail because of unsupported type");
    } catch (PSQLException e) {
        ExceptionUtils.validate(null, e, new PSQLException("ERROR.*Type char is not supported " +
                "by GPDBWritable.*?", null), true);
    }
}
 

@Test
void translateUniqueKeyViolationCompositeKey() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23505");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getDetail())
      .thenReturn("Key (myIdColumn, myColumn)=(myIdValue, myValue) already exists.");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateUniqueKeyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals(
      "entityTypeId:myEntity attributeName:myAttr entityId:myIdValue value:myValue",
      e.getMessage());
  assertTrue(e instanceof ListValueAlreadyExistsException);
}
 

@Override
@Nullable
@CheckForNull
public DataAccessException doTranslate(TransactionException transactionException) {
  DataAccessException translatedException;

  Throwable cause = transactionException.getCause();
  if (cause instanceof PSQLException) {
    PSQLException psqlException = (PSQLException) cause;
    String task = "commit transaction";
    DataAccessException dataAccessException = super.doTranslate(task, null, psqlException);
    if (dataAccessException != null) {
      translatedException = doTranslate(transactionException, dataAccessException);
    } else {
      translatedException = doTranslate(transactionException, psqlException);
    }
  } else {
    translatedException = null;
  }

  return translatedException;
}
 

@Test
void translateForeignKeyViolationStillReferenced() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23503");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getMessage())
      .thenReturn(
          "update or delete on table \"myDependentTable\" violates foreign key constraint \"myTable_myAttr_fkey\" on table \"myTable\"");
  when(serverErrorMessage.getDetail())
      .thenReturn("Key (myColumn)=(myValue) is still referenced from table \"myTable\"");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateForeignKeyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr value:myValue", e.getMessage());
  assertTrue(e instanceof ValueReferencedException);
}
 

private DataAccessException doTranslate(Throwable sourceThrowable, PSQLException pSqlException) {
  switch (pSqlException.getSQLState()) {
    case "22001":
      return translateValueTooLongViolation(sourceThrowable);
    case "22007": // invalid_datetime_format
    case "22P02": // not an integer exception
      return translateInvalidIntegerException(sourceThrowable, pSqlException);
    case "23502": // not_null_violation
      return translateNotNullViolation(sourceThrowable, pSqlException);
    case "23503": // foreign_key_violation
      return translateForeignKeyViolation(sourceThrowable, pSqlException);
    case "23505": // unique_violation
      return translateUniqueKeyViolation(sourceThrowable, pSqlException);
    case "23514": // check_violation
      return translateCheckConstraintViolation(sourceThrowable, pSqlException);
    case "2BP01":
      return translateDependentObjectsStillExist(sourceThrowable, pSqlException);
    case "42703":
      return translateUndefinedColumnException(pSqlException);
    case PostgreSqlQueryGenerator.ERR_CODE_READONLY_VIOLATION:
      return translateReadonlyViolation(sourceThrowable, pSqlException);
    default:
      return null;
  }
}
 

@Test
public void testExtractPSQLException() {
  PSQLException psqlException = new PSQLException("fake error", PSQLState.UNEXPECTED_ERROR);
  // passing in the exception itself works
  assertEquals(psqlException, Manager.extractPSQLException(psqlException));

  // wrapping the exception in a SQLException (as done by ORMLite) works
  SQLException wrap1 = new SQLException("wrapper", psqlException);
  assertEquals(psqlException, Manager.extractPSQLException(wrap1));

  // ORMLite can also double wrap the exception
  SQLException wrap2 = new SQLException("double", wrap1);
  assertEquals(psqlException, Manager.extractPSQLException(wrap2));

  // SQLException with some other kind of exception: null
  SQLException other = new SQLException("other", new RuntimeException("cause"));
  assertNull(Manager.extractPSQLException(other));
  
  Throwable t = new Throwable("hello", psqlException);
  assertEquals(psqlException, Manager.extractPSQLException(t));
}
 

@Test
public void testErrorRecoveryFromErrorsOutsideSqlOperations() throws Exception {
    try (Connection conn = DriverManager.getConnection(url(RW), properties)) {
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("select cast([10.3, 20.2] as integer) " +
                                                       "from information_schema.tables");
        try {
            stmt.executeQuery();
            fail("Should've raised PSQLException");
        } catch (PSQLException e) {
            assertThat(e.getMessage(), Matchers.containsString("Cannot cast expressions from type `double precision_array` to type `integer`"));
        }

        assertSelectNameFromSysClusterWorks(conn);
    }
}
 

@Test(groups = "features")
public void defaultCredentialsGUCsTransferredAsNull() throws Exception {

    ReadableExternalTable exTable = new ReadableExternalTable("extens", new String[] {
            "num1 integer",
            "t1 text",
            "num2 integer" }, "regression_location", "CUSTOM");

    exTable.setFragmenter(testPackage + "DummyFragmenter");
    exTable.setAccessor(testPackage + "FaultyGUCAccessor");
    exTable.setResolver(testPackage + "DummyResolver");
    exTable.setFormatter("pxfwritable_import");

    exTable.setHost(pxfHost);
    exTable.setPort(pxfPort);

    gpdb.createTableAndVerify(exTable);
    try {
        gpdb.queryResults(exTable, "SELECT num1, t1 FROM " + exTable.getName() + " ORDER BY num1, t1");
    } catch (Exception e) {
        ExceptionUtils.validate(null, e, new PSQLException("FaultyGUCAccessor: login null secret null", null), true);
    }
}
 

@Test(groups = "features")
public void emptyCredentialsGUCsTransferredAsNull() throws Exception {

    ReadableExternalTable exTable = new ReadableExternalTable("extens", new String[] {
            "num1 integer",
            "t1 text",
            "num2 integer" }, "regression_location", "CUSTOM");

    exTable.setFragmenter(testPackage + "DummyFragmenter");
    exTable.setAccessor(testPackage + "FaultyGUCAccessor");
    exTable.setResolver(testPackage + "DummyResolver");
    exTable.setFormatter("pxfwritable_import");

    exTable.setHost(pxfHost);
    exTable.setPort(pxfPort);

    gpdb.runQuery("SET pxf_remote_service_login = ''");
    gpdb.runQuery("SET pxf_remote_service_secret = ''");
    gpdb.createTableAndVerify(exTable);
    try {
        gpdb.queryResults(exTable, "SELECT num1, t1 FROM " + exTable.getName() + " ORDER BY num1, t1");
    } catch (Exception e) {
        ExceptionUtils.validate(null, e, new PSQLException("FaultyGUCAccessor: login null secret null", null), true);
    }
}
 

@Test
void translateNotNullViolationNoDoubleQuotes() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23502");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getMessage())
      .thenReturn("null value in column myColumn violates not-null constraint");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateNotNullViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr", e.getMessage());
  assertTrue(e instanceof ValueRequiredException);
}
 

@Test
void translateForeignKeyViolationBadMessage() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23503");
  when(serverErrorMessage.getTable()).thenReturn("mytable");
  when(serverErrorMessage.getDetail()).thenReturn("xxxyyyyzzzz");
  //noinspection ThrowableResultOfMethodCallIgnored
  assertThrows(
      RuntimeException.class,
      () ->
          postgreSqlExceptionTranslator.translateForeignKeyViolation(
              mock(Throwable.class), new PSQLException(serverErrorMessage)));
}
 

@Test
void translateCheckConstraintViolation() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getConstraint()).thenReturn("myTable_myColumn_chk");
  //noinspection ThrowableResultOfMethodCallIgnored

  UnknownEnumValueException e =
      postgreSqlExceptionTranslator.translateCheckConstraintViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr", e.getMessage());
}
 

@Test(expected = PSQLException.class)
public void whenDeletingATable_thenExceptionIfAccessed() throws SQLException {
    articleRepository.createTable();
    articleRepository.deleteTable();

    StringBuilder sb = new StringBuilder("SELECT * FROM ").append(TABLE_NAME);

    final String query = sb.toString();
    PreparedStatement preparedStatement = con.prepareStatement(query);
    preparedStatement.executeQuery();
}
 

@Test
void translateForeignKeyViolationNotPresent() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23503");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getDetail())
      .thenReturn("Key (myColumn)=(myValue) is not present in table \"myTable\"");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateForeignKeyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr value:myValue", e.getMessage());
  assertTrue(e instanceof UnknownValueReferenceException);
}
 

@Test
void translateUndefinedColumnException() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("42703");
  when(serverErrorMessage.getMessage())
      .thenReturn("Undefined column: 7 ERROR: column \"test\" does not exist");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      PostgreSqlExceptionTranslator.translateUndefinedColumnException(
          new PSQLException(serverErrorMessage));
  assertEquals("Undefined column: 7 ERROR: column \"test\" does not exist", e.getMessage());
}
 

@Test
public void testEmptyStatement() throws Exception {
    try (Connection conn = DriverManager.getConnection(url(RW), properties)) {
        assertThat(conn.createStatement().execute(""), is(false));

        try {
            conn.createStatement().executeQuery("");
            fail("executeQuery with empty query should throw a 'No results were returned by the query' error");
        } catch (PSQLException e) {
            // can't use expectedException.expectMessage because error messages are localized and locale is randomized
            assertThat(e.getSQLState(), is(PSQLState.NO_DATA.getState()));
        }
    }
}
 

@Test
void translateForeignKeyViolation() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23503");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getDetail()).thenReturn("... (myColumn) ... (myValue) ...");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateForeignKeyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr value:myValue", e.getMessage());
  assertTrue(e instanceof UnknownValueReferenceException);
}
 

@Test
void translateUniqueKeyViolationDoubleQuotes() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23505");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getDetail()).thenReturn("Key (\"myColumn\")=(myValue) already exists.");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateUniqueKeyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr value:myValue", e.getMessage());
  assertTrue(e instanceof ValueAlreadyExistsException);
}
 

@Test
void translateNotNullViolationBadMessage() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23502");
  when(serverErrorMessage.getTable()).thenReturn("mytable");
  when(serverErrorMessage.getMessage()).thenReturn("xxxyyyzzzz");
  //noinspection ThrowableResultOfMethodCallIgnored
  assertThrows(
      RuntimeException.class,
      () ->
          postgreSqlExceptionTranslator.translateNotNullViolation(
              mock(Throwable.class), new PSQLException(serverErrorMessage)));
}
 

@Test
void translateNotNullViolation() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("23502");
  when(serverErrorMessage.getTable()).thenReturn("myTable");
  when(serverErrorMessage.getMessage())
      .thenReturn("null value in column \"myColumn\" violates not-null constraint");
  //noinspection ThrowableResultOfMethodCallIgnored

  Exception e =
      postgreSqlExceptionTranslator.translateNotNullViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr", e.getMessage());
  assertTrue(e instanceof ValueRequiredException);
}
 

@Test
void translateDependentObjectsStillExistNoDoubleQuotes() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("2BP01");
  when(serverErrorMessage.getDetail())
      .thenReturn(
          "constraint my_foreign_key_constraint on table myTable depends on table myDependentTable");
  //noinspection ThrowableResultOfMethodCallIgnored

  EntityTypeReferencedException e =
      postgreSqlExceptionTranslator.translateDependentObjectsStillExist(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("dependencies:myRefEntity=myEntity", e.getMessage());
}
 

@Test
void translateDependentObjectsStillExistMultipleDependentTables() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("2BP01");
  when(serverErrorMessage.getDetail())
      .thenReturn(
          "constraint my_foreign_key_constraint on table \"myTable\" depends on table \"myDependentTable\"\nconstraint myOther_foreign_key_constraint on table \"myTable\" depends on table \"myOtherDependentTable\"");
  //noinspection ThrowableResultOfMethodCallIgnored

  EntityTypeReferencedException e =
      postgreSqlExceptionTranslator.translateDependentObjectsStillExist(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("dependencies:myRefEntity=myEntity;myOtherRefEntity=myEntity", e.getMessage());
}
 

@Test
void translateDependentObjectsStillExistOneDependentTableMultipleDependencies() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("2BP01");
  when(serverErrorMessage.getDetail())
      .thenReturn(
          "constraint my_foreign_key_constraint on table \"myTable\" depends on table \"myDependentTable\"\nconstraint myOther_foreign_key_constraint on table \"myTable\" depends on table \"myDependentTable\"");
  //noinspection ThrowableResultOfMethodCallIgnored

  EntityTypeReferencedException e =
      postgreSqlExceptionTranslator.translateDependentObjectsStillExist(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("dependencies:myRefEntity=myEntity", e.getMessage());
}
 

@Test
void translateDependentObjectsStillExistOneDependentTableSingleDependency() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getSQLState()).thenReturn("2BP01");
  when(serverErrorMessage.getDetail())
      .thenReturn(
          "constraint my_foreign_key_constraint on table \"myTable\" depends on table \"myDependentTable\"");
  //noinspection ThrowableResultOfMethodCallIgnored

  EntityTypeReferencedException e =
      postgreSqlExceptionTranslator.translateDependentObjectsStillExist(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("dependencies:myRefEntity=myEntity", e.getMessage());
}
 

@Test
void translateReadonlyViolationNoDoubleQuotes() {
  ServerErrorMessage serverErrorMessage = mock(ServerErrorMessage.class);
  when(serverErrorMessage.getMessage())
      .thenReturn(
          "Updating read-only column myColumn of table myTable with id [abc] is not allowed");
  //noinspection ThrowableResultOfMethodCallIgnored

  ReadonlyValueException e =
      postgreSqlExceptionTranslator.translateReadonlyViolation(
          mock(Throwable.class), new PSQLException(serverErrorMessage));
  assertEquals("entityTypeId:myEntity attributeName:myAttr entityId:abc", e.getMessage());
}