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

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> So these properties (I've changed all the names here, suggestions
> welcome for better ones) I think should be testable on the AM, each with
> an example of why:

> can_order
> can_unique
> can_multi_col
> can_exclude

Check, flags that indicate what you can put in CREATE INDEX obviously
have to be testable on the AM. I wonder though whether this behavior
of can_order should be distinct from the notion of "can produce
ordered scan output"? Maybe that's silly. Or maybe can_order needs
to be something you test at the opclass level not the AM level ---
I can sort of imagine an index type in which some opclasses support
ordering and others don't. Certainly that behavior is possible today
for amcanorderbyop.

> (One possible refinement here could be to invert the sense of all of
> these, making them no_whatever, so that "false" and "null" could be
> treated the same by clients. Or would that be too confusing?)

Hmm? I think true as the "has capability" case is fine from that
perspective, null would be taken as "doesn't work".

> These could be limited to being testable only on a specified index, and
> not AM-wide:

That would require adding a third function, but maybe we should just do
that. In a lot of cases you'd rather not have to worry about which AM
underlies a given index, so a simple index_has_property(regclass, text)
function would be nice. (That means can_order ought to be supported in
the per-index function even if you don't believe that it'd ever be
opclass-specific, or in the per-column function if you do.)

> can_backward

As above, that could conceivably need to be per-column.

> clusterable

Call this can_cluster, maybe? Or just cluster?

> index_scan
> bitmap_scan
> optional_key (? maybe)
> predicate_locks (? maybe)

As noted in my response to Kevin, I don't like the last two. They
are internal properties and it's hard to see them being of much
use to applications even if they weren't subject to change.

> And these for individual columns:

> can_return
> search_array (? maybe)
> search_nulls (? maybe)
> operator_orderable (or distance_orderable? what's a good name?)

distance_orderable seems not bad.

> orderable
> asc
> desc
> nulls_first
> nulls_last

OK

> A question: implementing can_return as a per-column property looks like
> it requires actually opening the index rel, rather than just consulting
> the syscache the way that most pg_get_* functions do. Should it always
> open it, or only for properties that need it?

Probably only if needed, on performance grounds, and because opening
the rel adds to chances of failure.

regards, tom lane