Thread: Regarding inclusion of indexes as tables

Deepak Beehyv wrote:
> Hi all,
> We are trying to use the getColumns() and getTables() functions from
> DatabaseMetaData, but we are getting indexes as well in getTables() and
> indexColumns from getColumns() call. We are able to filter out indexes from
> getTables using tableType column available but were unable to do that for
> columns as tableType is not captured.
> I would like to know if getting indexes in getTables() and indexColumns in
> getColumns is warranted or a bug. If such a behaviour is warranted, is there any
> alternate solution.
>
> --
> regards
> N.Deepak Ram
>
>

Hello N.Deepak,

1. DatabaseMetaData.getTables()

Each databases seems to decide what table types are available for
this function call.

PostgreSQL

Table Types: FOREIGN TABLE
Table Types: INDEX
Table Types: MATERIALIZED VIEW
Table Types: SEQUENCE
Table Types: SYSTEM INDEX
Table Types: SYSTEM TABLE
Table Types: SYSTEM TOAST INDEX
Table Types: SYSTEM TOAST TABLE
Table Types: SYSTEM VIEW
Table Types: TABLE
Table Types: TEMPORARY INDEX
Table Types: TEMPORARY SEQUENCE
Table Types: TEMPORARY TABLE
Table Types: TEMPORARY VIEW
Table Types: TYPE
Table Types: VIEW

While, MariaDB

Table Types: BASE TABLE
Table Types: SYSTEM VIEW
Table Types: VIEW

If the database decides at its core to have index tables then
they are going to be returned unless filtered via getTables().

So this would seem warranted and not a bug.

2. DatabaseMetaData.getColumns()

According to the Java API:

Retrieves a description of table columns available in the specified catalog.

So it retrieves all columns in a db specified for every table unfiltered
by the arguments. If the table has indexes they will be retrieved.

So again this would seem correct and not a bug.

Perhaps a more specific description of what you are trying to accomplish
could help to more fully answer the question of an alternative solution?

danap

On 8-5-2015 18:45, dmp wrote:

> 1. DatabaseMetaData.getTables()
>
> Each databases seems to decide what table types are available for
> this function call.
>
> PostgreSQL
>
> Table Types: FOREIGN TABLE
> Table Types: INDEX
> Table Types: MATERIALIZED VIEW
> Table Types: SEQUENCE
> Table Types: SYSTEM INDEX
> Table Types: SYSTEM TABLE
> Table Types: SYSTEM TOAST INDEX
> Table Types: SYSTEM TOAST TABLE
> Table Types: SYSTEM VIEW
> Table Types: TABLE
> Table Types: TEMPORARY INDEX
> Table Types: TEMPORARY SEQUENCE
> Table Types: TEMPORARY TABLE
> Table Types: TEMPORARY VIEW
> Table Types: TYPE
> Table Types: VIEW
>
> While, MariaDB
>
> Table Types: BASE TABLE
> Table Types: SYSTEM VIEW
> Table Types: VIEW
>
> If the database decides at its core to have index tables then
> they are going to be returned unless filtered via getTables().
>
> So this would seem warranted and not a bug.
>
> 2. DatabaseMetaData.getColumns()
>
> According to the Java API:
>
> Retrieves a description of table columns available in the specified
> catalog.
>
> So it retrieves all columns in a db specified for every table unfiltered
> by the arguments. If the table has indexes they will be retrieved.
>
> So again this would seem correct and not a bug.
>
> Perhaps a more specific description of what you are trying to accomplish
> could help to more fully answer the question of an alternative solution?

For background: I don't regularly use PostgreSQL so I don't know all its
ins and outs. I develop Jaybird, the Firebird JDBC driver, and I
recently joined this mailinglist to see and follow what users/developers
of other JDBC drivers do and discuss (and the Firebird-java mailinglist
is rather silent...).

With that out of the way: as an outside observer having getTables and
getColumns return index information sounds a bit curious: that is what
getIndexInfo is for.
Is an index in PostgreSQL selectable (as if it is a table) or can the
index itself be referenced as a column (eg select index from table)? If
not, I'd suggest that the information should not be part of getTables
nor of getColumns.

Mark
--
Mark Rotteveel

Mark Rotteveel <mark@lawinegevaar.nl> wrote:

> [...] having getTables and getColumns return index information
> sounds a bit curious: that is what getIndexInfo is for.
> Is an index in PostgreSQL selectable (as if it is a table) or can
> the index itself be referenced as a column (eg select index from
> table)? If not, I'd suggest that the information should not be
> part of getTables nor of getColumns.

It's somewhat understandable how the driver developers got to this
point, since in the server indexes are considered *relations*, as
are tables, views, materialized views, system catalogs, foreign
tables, etc.  On the other hand, unlike other relations they cannot
generally be referenced in SELECT queries or DML statements.  A
query can sometimes return data directly from the columns in an
index, but it is the job of the optimizer to recognize when a query
referencing a table can be optimized in this way.

If I were designing this in a green field, I would probably limit
return of indexes to the getIndexInfo() method and filter them out
of scans of relations returned by the getTables() method.  That
said, doing so now could break working code for existing users, and
the getTables() method does allow you to specify which table types
you want returned; so I (and probably most of the pg community)
would be reluctant to support filtering them out by default at this
point.  We tend to be reluctant to make changes that break working
code for existing users, and want to make a lot of noise about any
such change so that people have a chance to fix it before putting
it into production.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company