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

From Dave Cramer
Subject Re: JDBC problem in 10.3 / 10.4
Date
Msg-id CADK3HH+9Awq+ATsXvkrUCrVYG5+kX1qaP60_GxTq-u=oMYBbmg@mail.gmail.com
Whole thread Raw
List pgsql-bugs


Dave Cramer

On 16 May 2018 at 09:02, John Bester <john@softco.co.za> wrote:
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.

I just looked at the code https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1298 and there is no version specific logic in there. I'm at a loss...

Dave



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #15203: trigger does not recognize schema changes when passing on data
Next
From: Tom Lane
Date:
Subject: Re: BUG #15080: ecpg on windows doesn't define HAVE_LONG_LONG_INT