Обсуждение: getTables() not working for information_schema or pg_catalog
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
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 >
[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
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
Kris Jurka wrote on 25.11.2005 23:42: >> 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. > 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
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