Re: JDBC problem in 10.3 / 10.4

Dave Cramer

On 16 May 2018 at 09:02, John Bester <john(at)softco(dot)co(dot)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

>