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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> 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?

> 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.

No, I reject that entirely. It would be insane for example to expect that
random client-side code should be able to interpret the node trees stored
in places like pg_index.indexprs. It's barely possible that we could
maintain such logic in pg_dump, though having to maintain a different
version for each supported server branch would be a giant PITA. But do
you also want to maintain translated-into-Java copies of each of those
libraries for the benefit of JDBC? Or any other language that client
code might be written in?

Now, obviously knowing which bit in pg_index.indoption does what would be
a few orders of magnitude less of a maintenance hazard than knowing what
expression node trees contain. But that doesn't make it a good
future-proof thing for clients to be doing. If the answer to the question
"why do you need access to pg_am.amcanorder?" is "so I can interpret the
bits in pg_index.indoption", I think it's clear that we've got an
abstraction failure that is not going to be fixed by just exposing
something equivalent to the old pg_am definition.

Building on the has-property approach Andrew suggested, I wonder if
we need something like pg_index_column_has_property(indexoid, colno,
propertyname) with properties like "sortable", "desc", "nulls first".

regards, tom lane