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

Tom,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> 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?

Honestly, I anticipated the focus on the pg_get_expr() and should have
explicitly commented on it. I agree that we shouldn't look to have
pg_dump or client utilities be able to understand node trees and that,
instead, we should continue to provide a way for those to be
reconstructed into SQL expressions.

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

I agree- asking clients to interpret the bits in pg_index.indoption
isn't the right answer either.

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

Right, this makes sense to me. The point which I was trying to get at
above is that we should be able to replace most of what is provided in
pg_get_indexdef() by using this function to rebuild the CREATE INDEX
command- again, similar to how we build a CREATE TABLE command rather
than simply provide a 'pg_get_tabledef()'.

Thanks!

Stephen