Re: No longer possible to query catalogs for index capabilities? - Mailing list pgsql-hackers

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>> - this still has everything in amapi.c rather than creating any new>> files. Also, the regression tests are in
create_index.sqlfor lack>> of any obviously better place.
 
Tom> This more than doubles the size of amapi.c, so it has a definiteTom> feel of tail-wags-dog for me, even if that
seemedlike anTom> appropriate place otherwise which it doesn't really.  I think aTom> new .c file under adt/ is the way
togo, with extern declarationsTom> 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 muchTom> like adding this to create_index because (a)
itdoesn'tTom> particularly seem to match that file's purpose of setting upTom> indexes on the standard regression test
tables,and (b) that fileTom> is a bottleneck in parallel regression runs because it can't runTom> 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 theTom> exposed function names?  The latter seems much
closerto le motTom> 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 wayTom> back, as I believe much of what you've got
thereis not actuallyTom> of use to applications, and some of it is outrightTom> counterproductive.  An example is
exposingamcanreturn as anTom> 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
UNIQUEINDEX 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
thismatches "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)