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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> > On 7/25/16 3:26 PM, Andrew Gierth wrote:
> >> The issue I ran into was the exact same one as in the JDBC thread I
> >> linked to earlier: correctly interpreting pg_index.indoption (to get the
> >> ASC / DESC and NULLS FIRST/LAST settings), which requires knowing
> >> whether amcanorder is true to determine whether to look at the bits at
> >> all.
>
> > Maybe we should provide a facility to decode those bits then?
>
> Yeah. I'm not very impressed by the underlying assumption that it's
> okay for client-side code to hard-wire knowledge about what indoption
> bits mean, but not okay for it to hard-wire knowledge about which index
> AMs use which indoption bits. There's something fundamentally wrong
> in that. We don't let psql or pg_dump look directly at indoption, so
> why would we think that third-party client-side code should do so?
>
> Andrew complained upthread that pg_get_indexdef() was too heavyweight
> for his purposes, but it's not clear to me what he wants instead.

I guess I'm missing something because it seems quite clear to me. He
wants to know if the index was built with ASC or DESC, and if it was
built with NULLS FIRST or NULLS LAST, just like the JDBC driver.

pg_get_indexdef() will return that information, but as an SQL statement
with a lot of other information that isn't relevant and is difficult to
deal with when all you're trying to do is write an SQL query (no, I
don't believe the solution here is to use pg_get_indexef() ~ 'DESC').

For my 2c, I'd like to see pg_dump able to use the catalog tables to
derive the index definition, just as they manage to figure out table
definitions without (for the most part) using functions. More
generally, I believe we should be working to reach a point where we can
reconstruct all objects in the database using just the catalog, without
any SQL bits being provided from special functions which access
information that isn't available at the SQL level.

I don't see any problem with what Andrew has proposed as the information
returned informs the creation of the DDL statement, but does not provide
a textual "drop-in"/black-box component to include in the statement to
recreate the object, the way pg_get_indexdef() does.

Thanks!

Stephen