Re: Regarding inclusion of indexes as tables

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