PostgreSQL JDBC - getTables() not working for information_schema or pg

Author

getTables() not working for information_schema or pg_catalog

Thomas Kellerer

2005-11-25, 11:23 am

Hello,

I just stumbled across something:

When I call

con.getMetaData().getTables(null, "information_schema", "%", null);

(where con is a java.sql.Connection), I would assume to get all tables
that are stored in the information_schema, but the returned ResultSet is
empty (next() immediately returns false).

The same is true when I call it with "pg_catalog"
Passing null for the table name does not make a difference.

getTables(null, "public", "%", null) is working fine.

Am I missing something, or is this broken?

I am using postgresql-8.1-404.jdbc3.jar and PG 8.1 on a Windows 2000 box
(with JDK 1.5)

Regards
Thomas

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer

2005-11-25, 1:24 pm

IIRC these two tables are excluded on purpose to avoid returning
those tables. The purpose of getTables is to get the tables
associated with the connection, not to get system tables.

Dave

On 25-Nov-05, at 11:34 AM, Thomas Kellerer wrote:

> Hello,
>
> I just stumbled across something:
>
> When I call
>
> con.getMetaData().getTables(null, "information_schema", "%", null);
>
> (where con is a java.sql.Connection), I would assume to get all
> tables that are stored in the information_schema, but the returned
> ResultSet is empty (next() immediately returns false).
>
> The same is true when I call it with "pg_catalog"
> Passing null for the table name does not make a difference.
>
> getTables(null, "public", "%", null) is working fine.
>
> Am I missing something, or is this broken?
>
> I am using postgresql-8.1-404.jdbc3.jar and PG 8.1 on a Windows
> 2000 box (with JDK 1.5)
>
>
> Regards
> Thomas
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thomas Kellerer

2005-11-25, 8:23 pm

[getTables() not returning information about pg_catalog and
information_schema]

Dave Cramer wrote on 25.11.2005 18:58:
> IIRC these two tables are excluded on purpose to avoid returning those
> tables. The purpose of getTables is to get the tables associated with
> the connection, not to get system tables.

But then I wouldn't expect them to be included in the list that is
returned from getSchemas() as well. If they should be hidden for JDBC
access (which is a strange decision in my opinion) then they should be
hidden completely.

Regards
Thomas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-11-25, 8:23 pm

On Fri, 25 Nov 2005, Thomas Kellerer wrote:

> con.getMetaData().getTables(null, "information_schema", "%", null);
>
> (where con is a java.sql.Connection), I would assume to get all tables that
> are stored in the information_schema, but the returned ResultSet is empty
> (next() immediately returns false).
>
> The same is true when I call it with "pg_catalog"
> Passing null for the table name does not make a difference.
>

The usage of null as the last parameter for table types makes the driver
return only the default table types, not all of getTableTypes(). Things
like "SYSTEM TABLE" are not included in the default list so this is why
you get nothing from information_schema and pg_catalog. Re-reading the
javadoc, it says "types - a list of table types to include; null returns
all types". So clearly we're in violation of that. Will look at a fix.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Thomas Kellerer

2005-11-25, 8:24 pm

Kris Jurka wrote on 25.11.2005 23:42:
>
> The usage of null as the last parameter for table types makes the driver
> return only the default table types, not all of getTableTypes(). Things
> like "SYSTEM TABLE" are not included in the default list so this is why
> you get nothing from information_schema and pg_catalog. Re-reading the
> javadoc, it says "types - a list of table types to include; null returns
> all types". So clearly we're in violation of that. Will look at a fix.
>

You are right, when I pass all the types from getTableTypes() then I'll
get everything. I can live with that for now, but according to the docs
this indeed should work differently.

Thanks for your help
Thomas

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kris Jurka

2005-11-29, 3:24 am

On Fri, 25 Nov 2005, Kris Jurka wrote:

> The usage of null as the last parameter for table types makes the driver
> return only the default table types, not all of getTableTypes(). Things like
> "SYSTEM TABLE" are not included in the default list so this is why you get
> nothing from information_schema and pg_catalog. Re-reading the javadoc, it
> says "types - a list of table types to include; null returns all types". So
> clearly we're in violation of that. Will look at a fix.
>

Fixed in cvs for 8.0, 8.1, and HEAD.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links