Thread: DatabaseMetaData.getTypeInfo() - returns all tables as well

Hello,

DatabaseMetaData.getTypeInfo() returns not only data types, but also all table types.

Strictly speaking this is correct as the row type for a table is indeed a "data type" in Postgres,
but I think the information returned is way too much from a practical point of view.

This could be reduced at least a bit by not only excluding pg_toast, but also pg_catalog and information_schema types:

   SELECT t.typname,t.oid
   FROM pg_catalog.pg_type t
     JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid)
   WHERE n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema')

(instead of WHERE n.nspname != 'pg_toast' in AbstractJdbc2DatabaseMetaData.java getTypeInfo())


What I find even more confusing (from a JDBC point of view) is that each table type is reported twice:

Once with the table's name and once with an underscore prefixing the table name, e.g. foo and _foo.

These entries map to pg_type rows with typtype = 'c' and 'b' respectively.

I wonder if it would make sense to filter out the "real" tables from this result.
And if it does, if there is an efficient way of doing that and not removing real composite types
from the result at the same time.


Regards
Thomas

Sorry, I am not registered with GitHub (nor using it)

Regards
Thomas

Dave Cramer schrieb am 01.10.2015 um 13:23:
> Hi Thomas,
>
> Any chance you could provide a github pull request ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 30 September 2015 at 15:37, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
>
>     Hello,
>
>     DatabaseMetaData.getTypeInfo() returns not only data types, but also all table types.
>
>     Strictly speaking this is correct as the row type for a table is indeed a "data type" in Postgres,
>     but I think the information returned is way too much from a practical point of view.
>
>     This could be reduced at least a bit by not only excluding pg_toast, but also pg_catalog and information_schema
types:
>
>       SELECT t.typname,t.oid
>       FROM pg_catalog.pg_type t
>         JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid)
>       WHERE n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema')
>
>     (instead of WHERE n.nspname != 'pg_toast' in AbstractJdbc2DatabaseMetaData.java getTypeInfo())
>
>
>     What I find even more confusing (from a JDBC point of view) is that each table type is reported twice:
>
>     Once with the table's name and once with an underscore prefixing the table name, e.g. foo and _foo.
>
>     These entries map to pg_type rows with typtype = 'c' and 'b' respectively.
>
>     I wonder if it would make sense to filter out the "real" tables from this result.
>     And if it does, if there is an efficient way of doing that and not removing real composite types
>     from the result at the same time.
>
>
>     Regards
>     Thomas
>
>
>
>
>     --
>     Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

No particular problem besides performance.

But I'm surprised: why would this break anything?
Why does the driver rely on tables from pg_catalog be returned for type information?

Thomas

Dave Cramer schrieb am 19.10.2015 um 17:33:
> This change breaks more than it fixes!
>
> What is your specific problem with returning tables ?
>
> Dave Cramer
>
> davec@postgresintl.com <mailto:davec@postgresintl.com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 1 October 2015 at 08:51, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
>
>     Sorry, I am not registered with GitHub (nor using it)
>
>     Regards
>     Thomas
>
>     Dave Cramer schrieb am 01.10.2015 um 13:23:
>     > Hi Thomas,
>     >
>     > Any chance you could provide a github pull request ?
>     >
>     > Dave Cramer
>     >
>     > dave.cramer(at)credativ(dot)ca
>     > http://www.credativ.ca
>     >
>     > On 30 September 2015 at 15:37, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>
<mailto:spam_eater@gmx.net<mailto:spam_eater@gmx.net>>> wrote: 
>     >
>     >     Hello,
>     >
>     >     DatabaseMetaData.getTypeInfo() returns not only data types, but also all table types.
>     >
>     >     Strictly speaking this is correct as the row type for a table is indeed a "data type" in Postgres,
>     >     but I think the information returned is way too much from a practical point of view.
>     >
>     >     This could be reduced at least a bit by not only excluding pg_toast, but also pg_catalog and
information_schematypes: 
>     >
>     >       SELECT t.typname,t.oid
>     >       FROM pg_catalog.pg_type t
>     >         JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid)
>     >       WHERE n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema')
>     >
>     >     (instead of WHERE n.nspname != 'pg_toast' in AbstractJdbc2DatabaseMetaData.java getTypeInfo())
>     >
>     >
>     >     What I find even more confusing (from a JDBC point of view) is that each table type is reported twice:
>     >
>     >     Once with the table's name and once with an underscore prefixing the table name, e.g. foo and _foo.
>     >
>     >     These entries map to pg_type rows with typtype = 'c' and 'b' respectively.
>     >
>     >     I wonder if it would make sense to filter out the "real" tables from this result.
>     >     And if it does, if there is an efficient way of doing that and not removing real composite types
>     >     from the result at the same time.
>     >
>     >
>     >     Regards
>     >     Thomas
>     >
>     >
>     >
>     >
>     >     --
>     >     Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>
<mailto:pgsql-jdbc@postgresql.org<mailto:pgsql-jdbc@postgresql.org>>) 
>     >     To make changes to your subscription:
>     >     http://www.postgresql.org/mailpref/pgsql-jdbc
>     >
>     >
>
>
>
>     --
>     Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-jdbc
>
>