Repository: pgjdbc/pgjdbc
Pull request #1007 (2017-11-02 17:01:31)

test: querymode extendedCacheEverything
Add test for extendedCacheEverything to track issue #955.

The replication is tested on other jobs, so is safe to remove it for 9.4 and use that to test extendedCacheEverything.

davecramer: seems reasonable to me


Patch:
diff --git a/.travis.yml b/.travis.yml
index 7a3d80c9..0b11bc35 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -62,6 +62,11 @@ cache:
 
 matrix:
   fast_finish: true
+  allow_failures:
+    - env: # REMOVE FAILURE extendedCacheEverything
+        - PG_VERSION=9.4
+        - QUERY_MODE=extendedCacheEverything
+        - COVERAGE=Y
   include:
     - jdk: oraclejdk8
       env: RUN_CHECKSTYLE=true
@@ -110,13 +115,11 @@ matrix:
         - XA=true
         - REPLICATION=Y
     - jdk: oraclejdk8
-      sudo: required
       addons:
         postgresql: "9.4"
       env:
         - PG_VERSION=9.4
-        - XA=true
-        - REPLICATION=Y
+        - QUERY_MODE=extendedCacheEverything
         - COVERAGE=Y
     - jdk: openjdk7
       sudo: required
diff --git a/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java b/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
index 41b01eca..2716aac5 100644
--- a/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
+++ b/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
@@ -1075,6 +1075,15 @@ public abstract class BaseDataSource implements CommonDataSource, Referenceable
     return url.toString();
   }
 
+  /**
+   * Generates a {@link DriverManager} URL from the other properties supplied.
+   *
+   * @return {@link DriverManager} URL from the other properties supplied
+   */
+  public String getURL() {
+    return getUrl();
+  }
+
   /**
    * Sets properties from a {@link DriverManager} URL.
    *
@@ -1089,6 +1098,16 @@ public abstract class BaseDataSource implements CommonDataSource, Referenceable
     }
   }
 
+  /**
+   * Sets properties from a {@link DriverManager} URL.
+   * Added to follow convention used in other DBMS.
+   *
+   * @param url properties to set
+   */
+  public void setURL(String url) {
+    setUrl(url);
+  }
+
   public String getProperty(String name) throws SQLException {
     PGProperty pgProperty = PGProperty.forName(name);
     if (pgProperty != null) {
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java b/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
index e7b5c447..f35fea27 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
@@ -2541,7 +2541,41 @@ public class PgDatabaseMetaData implements DatabaseMetaData {
 
   public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern)
       throws SQLException {
-    return getProcedures(catalog, schemaPattern, functionNamePattern);
+
+    // The pg_get_function_result only exists 8.4 or later
+    boolean pgFuncResultExists = connection.haveMinimumServerVersion(ServerVersion.v8_4);
+
+    // Use query that support pg_get_function_result to get function result, else unknown is defaulted
+    String funcTypeSql = DatabaseMetaData.functionResultUnknown + " ";
+    if (pgFuncResultExists) {
+      funcTypeSql = " CASE "
+              + "   WHEN (format_type(p.prorettype, null) = 'unknown') THEN " + DatabaseMetaData.functionResultUnknown
+              + "   WHEN "
+              + "     (substring(pg_get_function_result(p.oid) from 0 for 6) = 'TABLE') OR "
+              + "     (substring(pg_get_function_result(p.oid) from 0 for 6) = 'SETOF') THEN " + DatabaseMetaData.functionReturnsTable
+              + "   ELSE " + DatabaseMetaData.functionNoTable
+              + " END ";
+    }
+
+    // Build query and result
+    String sql;
+    sql = "SELECT current_database() AS FUNCTION_CAT, n.nspname AS FUNCTION_SCHEM, p.proname AS FUNCTION_NAME, "
+        + " d.description AS REMARKS, "
+        + funcTypeSql + " AS FUNCTION_TYPE, "
+        + " p.proname || '_' || p.oid AS SPECIFIC_NAME "
+        + "FROM pg_catalog.pg_proc p "
+        + "INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace=n.oid "
+        + "LEFT JOIN pg_catalog.pg_description d ON p.oid=d.objoid "
+        + "WHERE pg_function_is_visible(p.oid) ";
+    if (schemaPattern != null && !schemaPattern.isEmpty()) {
+      sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
+    }
+    if (functionNamePattern != null && !functionNamePattern.isEmpty()) {
+      sql += " AND p.proname LIKE " + escapeQuotes(functionNamePattern);
+    }
+    sql += " ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, p.oid::text ";
+
+    return createMetaDataStatement().executeQuery(sql);
   }
 
   public ResultSet getFunctionColumns(String catalog, String schemaPattern,
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java b/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
index d075b236..cdf25f40 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
@@ -8,6 +8,7 @@ package org.postgresql.jdbc;
 import org.postgresql.PGResultSetMetaData;
 import org.postgresql.core.BaseConnection;
 import org.postgresql.core.Field;
+import org.postgresql.core.ServerVersion;
 import org.postgresql.util.GT;
 import org.postgresql.util.JdbcBlackHole;
 import org.postgresql.util.LruCache;
@@ -233,9 +234,14 @@ public class PgResultSetMetaData implements ResultSetMetaData, PGResultSetMetaDa
 
     StringBuilder sql = new StringBuilder(
         "SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, "
-            + "a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), "
-            + "pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' "
-            + "FROM pg_catalog.pg_class c "
+            + "a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), ");
+
+    if ( connection.haveMinimumServerVersion(ServerVersion.v10)) {
+      sql.append("a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' ");
+    } else {
+      sql.append("pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' ");
+    }
+    sql.append( "FROM pg_catalog.pg_class c "
             + "JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) "
             + "JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) "
             + "JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) "


Test patch:
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java
index 39649b43..98f9b78c 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java
@@ -10,9 +10,11 @@ import static org.junit.Assert.assertNull;
 import static org.junit.Assert.assertTrue;
 
 import org.postgresql.PGResultSetMetaData;
+import org.postgresql.core.ServerVersion;
 import org.postgresql.jdbc.PreferQueryMode;
 import org.postgresql.test.TestUtil;
 
+import org.junit.Assert;
 import org.junit.Assume;
 import org.junit.Test;
 
@@ -38,6 +40,11 @@ public class ResultSetMetaDataTest extends BaseTest4 {
 
     TestUtil.dropSequence(conn, "serialtest_a_seq");
     TestUtil.dropSequence(conn, "serialtest_b_seq");
+
+    if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v10)) {
+      TestUtil.createTable(conn, "identitytest", "id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY");
+    }
+
     TestUtil.createTable(conn, "serialtest", "a serial, b bigserial, c int");
     TestUtil.createTable(conn, "alltypes",
         "bool boolean, i2 int2, i4 int4, i8 int8, num numeric(10,2), re real, fl float, ch char(3), vc varchar(3), tx text, d date, t time without time zone, tz time with time zone, ts timestamp without time zone, tsz timestamp with time zone, bt bytea");
@@ -52,6 +59,9 @@ public class ResultSetMetaDataTest extends BaseTest4 {
     TestUtil.dropTable(conn, "rsmd1");
     TestUtil.dropTable(conn, "timetest");
     TestUtil.dropTable(conn, "serialtest");
+    if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v10)) {
+      TestUtil.dropTable(conn, "identitytest");
+    }
     TestUtil.dropTable(conn, "alltypes");
     TestUtil.dropTable(conn, "sizetest");
     TestUtil.dropSequence(conn, "serialtest_a_seq");
@@ -248,6 +258,16 @@ public class ResultSetMetaDataTest extends BaseTest4 {
     assertEquals("rsmd1", rsmd.getColumnTypeName(1));
   }
 
+  @Test
+  public void testIdentityColumn() throws Exception {
+    assumeMinimumServerVersion(ServerVersion.v10);
+    assumePreparedStatementMetadataSupported();
+    PreparedStatement pstmt = conn.prepareStatement("SELECT id FROM identitytest");
+    ResultSet rs = pstmt.executeQuery();
+    ResultSetMetaData rsmd = pstmt.getMetaData();
+    Assert.assertTrue(rsmd.isAutoIncrement(1));
+  }
+
   private void assumePreparedStatementMetadataSupported() {
     Assume.assumeTrue("prepared statement metadata is not supported for simple protocol",
         preferQueryMode.compareTo(PreferQueryMode.EXTENDED_FOR_PREPARED) >= 0);
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/OptionalTestSuite.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/OptionalTestSuite.java
index fe59de08..00c5d1a0 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/OptionalTestSuite.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/OptionalTestSuite.java
@@ -17,6 +17,7 @@ import org.junit.runners.Suite;
 @RunWith(Suite.class)
 @Suite.SuiteClasses({SimpleDataSourceTest.class,
         SimpleDataSourceWithUrlTest.class,
+        SimpleDataSourceWithSetURLTest.class,
         ConnectionPoolTest.class,
         PoolingDataSourceTest.class,
         CaseOptimiserDataSourceTest.class})
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/SimpleDataSourceWithSetURLTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/SimpleDataSourceWithSetURLTest.java
new file mode 100644
index 00000000..f9bbd5a9
--- /dev/null
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/optional/SimpleDataSourceWithSetURLTest.java
@@ -0,0 +1,62 @@
+/*
+ * Copyright (c) 2017, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.test.jdbc2.optional;
+
+import static org.junit.Assert.assertEquals;
+import static org.postgresql.Driver.parseURL;
+
+import org.postgresql.PGProperty;
+import org.postgresql.jdbc2.optional.SimpleDataSource;
+import org.postgresql.test.TestUtil;
+
+import org.junit.Test;
+
+import java.util.Properties;
+
+/**
+ * Performs the basic tests defined in the superclass. Just adds the configuration logic.
+ */
+public class SimpleDataSourceWithSetURLTest extends BaseDataSourceTest {
+  /**
+   * Creates and configures a new SimpleDataSource using setURL method.
+   */
+  @Override
+  protected void initializeDataSource() {
+    if (bds == null) {
+      bds = new SimpleDataSource();
+      bds.setURL(String.format("jdbc:postgresql://%s:%d/%s?prepareThreshold=%d&loggerLevel=%s", TestUtil.getServer(), TestUtil.getPort(), TestUtil.getDatabase(), TestUtil.getPrepareThreshold(),
+              TestUtil.getLogLevel()));
+      bds.setUser(TestUtil.getUser());
+      bds.setPassword(TestUtil.getPassword());
+      bds.setProtocolVersion(TestUtil.getProtocolVersion());
+    }
+  }
+
+  @Test
+  public void testGetURL() throws Exception {
+    con = getDataSourceConnection();
+
+    String url = bds.getURL();
+    Properties properties = parseURL(url, null);
+
+    assertEquals(TestUtil.getServer(), properties.getProperty(PGProperty.PG_HOST.getName()));
+    assertEquals(Integer.toString(TestUtil.getPort()), properties.getProperty(PGProperty.PG_PORT.getName()));
+    assertEquals(TestUtil.getDatabase(), properties.getProperty(PGProperty.PG_DBNAME.getName()));
+    assertEquals(Integer.toString(TestUtil.getPrepareThreshold()), properties.getProperty(PGProperty.PREPARE_THRESHOLD.getName()));
+    assertEquals(TestUtil.getLogLevel(), properties.getProperty(PGProperty.LOGGER_LEVEL.getName()));
+  }
+
+  @Test
+  public void testSetURL() throws Exception {
+    initializeDataSource();
+
+    assertEquals(TestUtil.getServer(), bds.getServerName());
+    assertEquals(TestUtil.getPort(), bds.getPortNumber());
+    assertEquals(TestUtil.getDatabase(), bds.getDatabaseName());
+    assertEquals(TestUtil.getPrepareThreshold(), bds.getPrepareThreshold());
+    assertEquals(TestUtil.getLogLevel(), bds.getLoggerLevel());
+  }
+}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
index 734ace44..1a03543c 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
@@ -5,8 +5,11 @@
 
 package org.postgresql.test.jdbc4;
 
+import static org.hamcrest.CoreMatchers.is;
+import static org.hamcrest.CoreMatchers.notNullValue;
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertThat;
 import static org.junit.Assert.assertTrue;
 
 import org.postgresql.core.ServerVersion;
@@ -20,6 +23,10 @@ import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
 
 public class DatabaseMetaDataTest {
 
@@ -80,4 +87,149 @@ public class DatabaseMetaDataTest {
     assertNull(rs.getString("TABLE_CATALOG"));
     assertTrue(!rs.next());
   }
+
+  @Test
+  public void testGetFunctionsWithBlankPatterns() throws SQLException {
+    int minFuncCount = 1000;
+    DatabaseMetaData dbmd = _conn.getMetaData();
+    ResultSet rs = dbmd.getFunctions("", "", "");
+    int count = assertGetFunctionRS(rs);
+    assertThat(count > minFuncCount, is(true));
+
+    // Should be same as blank pattern
+    ResultSet rs2 = dbmd.getFunctions(null, null, null);
+    int count2 = assertGetFunctionRS(rs2);
+    assertThat(count2 > minFuncCount, is(true));
+    assertThat(count2, is(count));
+
+    // Catalog parameter has no affect on our getFunctions filtering
+    ResultSet rs3 = dbmd.getFunctions("ANYTHING_WILL_WORK", null, null);
+    int count3 = assertGetFunctionRS(rs3);
+    assertThat(count3 > minFuncCount, is(true));
+    assertThat(count3, is(count));
+
+    // Filter by schema
+    ResultSet rs4 = dbmd.getFunctions("", "pg_catalog", null);
+    int count4 = assertGetFunctionRS(rs4);
+    assertThat(count4 > minFuncCount, is(true));
+
+    // Filter by schema and function name
+    ResultSet rs5 = dbmd.getFunctions("", "pg_catalog", "abs");
+    int count5 = assertGetFunctionRS(rs5);
+    assertThat(count5 >= 1, is(true));
+
+    // Filter by function name only
+    rs5 = dbmd.getFunctions("", "", "abs");
+    count5 = assertGetFunctionRS(rs5);
+    assertThat(count5 >= 1, is(true));
+
+    rs.close();
+    rs2.close();
+    rs3.close();
+    rs4.close();
+    rs5.close();
+  }
+
+  /** Assert some basic result from ResultSet of a GetFunctions method. Return the total row count. */
+  private int assertGetFunctionRS(ResultSet rs) throws SQLException {
+    // There should be at least one row
+    assertThat(rs.next(), is(true));
+    assertThat(rs.getString("FUNCTION_CAT"), is(System.getProperty("database")));
+    assertThat(rs.getString("FUNCTION_SCHEM"), notNullValue());
+    assertThat(rs.getString("FUNCTION_NAME"), notNullValue());
+    assertThat(rs.getShort("FUNCTION_TYPE") >= 0, is(true));
+    assertThat(rs.getString("SPECIFIC_NAME"), notNullValue());
+
+    // Ensure there is enough column and column value retrieve by index should be same as column name (ordered)
+    assertThat(rs.getMetaData().getColumnCount(), is(6));
+    assertThat(rs.getString(1), is(rs.getString("FUNCTION_CAT")));
+    assertThat(rs.getString(2), is(rs.getString("FUNCTION_SCHEM")));
+    assertThat(rs.getString(3), is(rs.getString("FUNCTION_NAME")));
+    assertThat(rs.getString(4), is(rs.getString("REMARKS")));
+    assertThat(rs.getShort(5), is(rs.getShort("FUNCTION_TYPE")));
+    assertThat(rs.getString(6), is(rs.getString("SPECIFIC_NAME")));
+
+    // Get all result and assert they are ordered per javadoc spec:
+    //   FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_NAME
+    List<String> result = new ArrayList<String>();
+    do {
+      result.add(rs.getString("FUNCTION_CAT")
+              + " "
+              + rs.getString("FUNCTION_SCHEM")
+              + " "
+              + rs.getString("FUNCTION_NAME")
+              + " "
+              + rs.getString("SPECIFIC_NAME"));
+    } while (rs.next());
+
+    List<String> orderedResult = new ArrayList<String>(result);
+    Collections.sort(orderedResult);
+    assertThat(result, is(orderedResult));
+
+    return result.size();
+  }
+
+  @Test
+  public void testGetFunctionsWithSpecificTypes() throws SQLException {
+    // These function creation are borrow from jdbc2/DatabaseMetaDataTest
+    // We modify to ensure new function created are returned by getFunctions()
+
+    DatabaseMetaData dbmd = _conn.getMetaData();
+    if (TestUtil.haveMinimumServerVersion(_conn, ServerVersion.v8_4)) {
+      Statement stmt = _conn.createStatement();
+      stmt.execute(
+              "CREATE OR REPLACE FUNCTION getfunc_f1(int, varchar) RETURNS int AS 'SELECT 1;' LANGUAGE SQL");
+      ResultSet rs = dbmd.getFunctions("", "", "getfunc_f1");
+      assertThat(rs.next(), is(true));
+      assertThat(rs.getString("FUNCTION_NAME"), is("getfunc_f1"));
+      assertThat(rs.getShort("FUNCTION_TYPE"), is((short)DatabaseMetaData.functionNoTable));
+      assertThat(rs.next(), is(false));
+      rs.close();
+      stmt.execute("DROP FUNCTION getfunc_f1(int, varchar)");
+
+      stmt.execute(
+              "CREATE OR REPLACE FUNCTION getfunc_f3(IN a int, INOUT b varchar, OUT c timestamptz) AS $f$ BEGIN b := 'a'; c := now(); return; END; $f$ LANGUAGE plpgsql");
+      rs = dbmd.getFunctions("", "", "getfunc_f3");
+      assertThat(rs.next(), is(true));
+      assertThat(rs.getString("FUNCTION_NAME"), is("getfunc_f3"));
+      assertThat(rs.getShort("FUNCTION_TYPE"), is((short)DatabaseMetaData.functionNoTable));
+      assertThat(rs.next(), is(false));
+      rs.close();
+      stmt.execute("DROP FUNCTION getfunc_f3(int, varchar)");
+
+      // RETURNS TABLE requires PostgreSQL 8.4+
+      stmt.execute(
+              "CREATE OR REPLACE FUNCTION getfunc_f5() RETURNS TABLE (i int) LANGUAGE sql AS 'SELECT 1'");
+
+      rs = dbmd.getFunctions("", "", "getfunc_f5");
+      assertThat(rs.next(), is(true));
+      assertThat(rs.getString("FUNCTION_NAME"), is("getfunc_f5"));
+      assertThat(rs.getShort("FUNCTION_TYPE"), is((short)DatabaseMetaData.functionReturnsTable));
+      assertThat(rs.next(), is(false));
+      rs.close();
+      stmt.execute("DROP FUNCTION getfunc_f5()");
+    } else {
+      // For PG 8.3 or 8.2 it will resulted in unknown function type
+      Statement stmt = _conn.createStatement();
+      stmt.execute(
+              "CREATE OR REPLACE FUNCTION getfunc_f1(int, varchar) RETURNS int AS 'SELECT 1;' LANGUAGE SQL");
+      ResultSet rs = dbmd.getFunctions("", "", "getfunc_f1");
+      assertThat(rs.next(), is(true));
+      assertThat(rs.getString("FUNCTION_NAME"), is("getfunc_f1"));
+      assertThat(rs.getShort("FUNCTION_TYPE"), is((short)DatabaseMetaData.functionResultUnknown));
+      assertThat(rs.next(), is(false));
+      rs.close();
+      stmt.execute("DROP FUNCTION getfunc_f1(int, varchar)");
+
+      stmt.execute(
+              "CREATE OR REPLACE FUNCTION getfunc_f3(IN a int, INOUT b varchar, OUT c timestamptz) AS $f$ BEGIN b := 'a'; c := now(); return; END; $f$ LANGUAGE plpgsql");
+      rs = dbmd.getFunctions("", "", "getfunc_f3");
+      assertThat(rs.next(), is(true));
+      assertThat(rs.getString("FUNCTION_NAME"), is("getfunc_f3"));
+      assertThat(rs.getShort("FUNCTION_TYPE"), is((short)DatabaseMetaData.functionResultUnknown));
+      assertThat(rs.next(), is(false));
+      rs.close();
+      stmt.execute("DROP FUNCTION getfunc_f3(int, varchar)");
+    }
+  }
 }