Re: No longer possible to query catalogs for index capabilities?

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > On 07/25/2016 12:19 PM, Tom Lane wrote:
> >> Andrew still hasn't shown a concrete
> >> example of what he needs to do and why.
>
> > I think that Andrew and other people who have commented on this thread
> > made it pretty obvious why it is useful.
>
> Both Andrew and Robert have asserted without proof that it'd be useful
> to be able to get at some of that data. Given the lack of any supporting
> evidence, it's impossible to know which data needs to be exposed, and
> that's why I find their statements insufficient. "Emulate 9.5's pg_am
> exactly" is not in the cards, and short of that I'd like to see some
> concrete reasons why we do or do not need to expose particular bits of
> data.

I believe the response to "what" is the patch which Andrew provided, and
the use-case is illustrated by the query which he wrote that used those
columns in much the same way that the JDBC driver used them (and which
was also broken by their removal). This isn't just academic "gee, I
wish we hadn't removed those columns", there are clearly cases where
they were useful and were used.

I do not believe hard-coding the name of index types as a definitive
list of which indexes support what capabilities is an appropriate
approach (as was suggested, and evidently done, for the JDBC driver).

Additional use-cases include query analysis, by which one might want to
see what capabilities an index has to understand why it may or may not
be useful for a given query. I would also suggest that relying on
pg_get_indexdef() is a poor solution and we should be considering how to
expose the necessary information for pg_dump through the catalog instead
of asking users who are interested to use a function that returns the
result as an SQL DDL statement. We don't do that for table definitions
and have argued time and time again why we shouldn't.

Thanks!

Stephen