Thread: Bug in getIndexInfo() with 9.0 JDBC driver
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 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023) This is the code that I'm running (error checking removed) Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "postgres"); ResultSet rs = con.getMetaData().getIndexInfo(null, "public", "mytable", false, false); while (rs.next()) { System.out.println(rs.getString("INDEX_NAME")); } Thanks Thomas
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
Thomas Kellerer <spam_eater@gmx.net> writes: > Thomas Kellerer wrote on 25.09.2010 11:38: >> 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: Actually, I think we'd better fix this on the backend side, because it's going to break for all branches not just 9.0 when the next set of minor releases come out. I think we can make the code that's checking the argument of pg_get_expr() recurse into sub-selects to verify validity. regards, tom lane
Tom Lane wrote on 25.09.2010 21:10: >>> 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() into the >> derived table to avoid the error: > > Actually, I think we'd better fix this on the backend side, because > it's going to break for all branches not just 9.0 when the next set > of minor releases come out. I think we can make the code that's > checking the argument of pg_get_expr() recurse into sub-selects to > verify validity. > I assume putting out a new driver build is a lot quicker than releasing 9.0.1 and as this problem affects any JDBC basedquery tool (as they usually display table & index information), it's probably worthwhile fixing it in the driver first. What I find a bit confusing is that the JUnit test works fine and it *is* testing getIndexInfo(). So there must be a bitmore than what I can reproduce. Regards Thomas