Re: Bug in getIndexInfo() with 9.0 JDBC driver

Thomas Kellerer wrote on 25.09.2010 11:38:
> Hello,
>
> in my program I'm using DatabaseMetaData.getIndexInfo().
>
> This is working fine with the 8.4 driver on a 8.4 and 9.0 database.
>
> However when using the 9.0 driver (postgresql-9.0-801.jdbc4.jar) I'm getting the following exception when calling
getIndexInfo():
>
> ERROR: argument to pg_get_expr() must come from system catalogs [SQL State=42501]
> org.postgresql.util.PSQLException: ERROR: argument to pg_get_expr() must come from system catalogs

I had a look at AbstractJdbc2DatabaseMetaData, and I think the solution would be to push down the call pg_get_expr()
intothe derived table to avoid the error: 

So instead of the original statement:

SELECT ...
        pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION
FROM pg_catalog.pg_class ct
   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)
   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,
           i.indisunique, i.indisclustered, i.indpred,
           i.indexprs,
           information_schema._pg_expandarray(i.indkey) AS keys
         FROM pg_catalog.pg_index i) i
     ON (ct.oid = i.indrelid)
   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)
   JOIN pg_catalog.pg_am am ON (ci.relam = am.oid)

using

SELECT ...
        i.filter_condition
FROM pg_catalog.pg_class ct
   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)
   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,
           i.indisunique, i.indisclustered,
           pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS filter_condition,
           i.indexprs,
           information_schema._pg_expandarray(i.indkey) AS keys
         FROM pg_catalog.pg_index i) i
     ON (ct.oid = i.indrelid)
   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)
   JOIN pg_catalog.pg_am am ON (ci.relam = am.oid)

seems to solve the problem.

I haven't checked if that works with 8.4 and 8.3 though.

Regards
Thomas