Re: JDBC problem in 10.3 / 10.4 - Mailing list pgsql-bugs

I am using Connection.getMetaData().getTables(...) to auto generate
SQL statements (INSERT, UPDATE and DELETE). When upgrading
underlying databases to 10.3 and 10.4 on different servers, I started
getting problems. After some debugging I found that the meta
data returned by the same JDBC driver for the different server
versions are different.

If you debug the code below (I am using Eclipse) and
step through the code, the "originalQuery" property of the resultset
(which you can see in "rs" variable properties) differs. I have tested
the postgresql-42.2.2.jre7.jar and postgresql-9.4.1212.jar against
both servers and got the same problem.

Test Code: (Replace createConnection() to whatever you need)

try (Connectgion con = createConnection();
     ResultSet rs = con.getMetaData().getTables(null, null, null, new String[] { "TABLE" })) {
  while (rs.next()) {
      String catalogue = rs.getString("TABLE_CAT");
      String schema = rs.getString("TABLE_SCHEM");
      String name = rs.getString("TABLE_NAME");
      String type = rs.getString("TABLE_TYPE");
      System.out.println(catalogue + " / " + schema + " / " + name + " / " + type);
  }
}

"originalQuery" used when connecting to 9.5 server:

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  -- CASE STATEMENTS --
FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE c.relnamespace = n.oid  AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )
ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

"originalQuery" used when connecting to 10.4 server:

SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME,  -- CASE STATEMENTS --
FROM pg_class c 
WHERE true  AND (false  OR ( c.relkind = 'r' AND c.relname !~ '^pg_' ) )
ORDER BY TABLE_TYPE,TABLE_NAME

This shows clearly that LEFT JOINs are left out for 10.4 and NULL is simply returned for some columns. The full
statement actually works for 10.5, so there must be some other condition which determines the SQL used.