Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[JDBC] Bug in getIndexInfo() with 9.0 JDBC driver

10 views
Skip to first unread message

Thomas Kellerer

unread,
Sep 25, 2010, 5:38:04 AM9/25/10
to
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


--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Thomas Kellerer

unread,
Sep 25, 2010, 6:01:29 AM9/25/10
to
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() into the 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

Tom Lane

unread,
Sep 25, 2010, 3:10:49 PM9/25/10
to
Thomas Kellerer <spam_...@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() 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.

regards, tom lane

Thomas Kellerer

unread,
Sep 25, 2010, 4:46:44 PM9/25/10
to
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 based query 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 bit more than what I can reproduce.

Regards
Thomas

0 new messages