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

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> - this still has everything in amapi.c rather than creating any new
>> files. Also, the regression tests are in create_index.sql for lack
>> of any obviously better place.

Tom> This more than doubles the size of amapi.c, so it has a definite
Tom> feel of tail-wags-dog for me, even if that seemed like an
Tom> appropriate place otherwise which it doesn't really. I think a
Tom> new .c file under adt/ is the way to go, with extern declarations
Tom> in builtins.h.

Yeah, I'm fine with that. adt/amutils.c unless I see some better
suggestion.

Tom> Maybe we need a new regression test case file too. I don't much
Tom> like adding this to create_index because (a) it doesn't
Tom> particularly seem to match that file's purpose of setting up
Tom> indexes on the standard regression test tables, and (b) that file
Tom> is a bottleneck in parallel regression runs because it can't run
Tom> in parallel with much else.

Good point. I looked around to see if anything was testing
pg_get_indexdef, thinking that that would be a good place, but it seems
that pg_get_indexdef is tested only in incidental ways (in collate and
rules, the latter of which tests it only with invalid input).

I'll do the next version with a new file, unless a better idea shows up.

>> Comments?

Tom> Why did you go with "capability" rather than "property" in the
Tom> exposed function names? The latter seems much closer to le mot
Tom> juste, especially for things like asc/desc.

The first version (which dealt only with AMs) went with "capability"
because it was dealing with what the AM _could_ do rather than what was
defined on any specific index.

The second version added pg_index_column_has_property because that was
the name that had been used in discussion.

Changing them all to "property" would be more consistent I suppose.

Tom> I'd personally cut the list of pg_am replacement properties way
Tom> back, as I believe much of what you've got there is not actually
Tom> of use to applications, and some of it is outright
Tom> counterproductive. An example is exposing amcanreturn as an
Tom> index-AM boolean.

For AM-wide properties, it may be that they have to be considered
"lossy" when tested against the AM oid rather than on an individual
index or column - at the AM level, "false" might mean "this won't work"
while "true" would mean "this might work sometimes, not guaranteed to
work on every index". The documentation should probably indicate this.

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
- if this is false, an admin tool shouldn't try and put ASC or DESC
in a CREATE INDEX

can_unique
- if this is false, an admin tool might, for example, want to not
offer the user the option of CREATE UNIQUE INDEX with this AM

can_multi_col
- if this is false, an admin tool might want to allow the user to
select only one column

can_exclude
- a new property that indicates whether the AM can be used for
exclusion constraints; at present this matches "amgettuple" but
that implementation detail should of course be hidden

(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?)

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

can_backward
clusterable
index_scan
bitmap_scan
optional_key (? maybe)
predicate_locks (? maybe)

And these for individual columns:

can_return
search_array (? maybe)
search_nulls (? maybe)
operator_orderable (or distance_orderable? what's a good name?)
orderable
asc
desc
nulls_first
nulls_last

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?

--
Andrew (irc:RhodiumToad)