Re: Psql patch to show access methods info

Thanks for review.

>> With + it shows description:
>> # \dA+
>> List of access methods
>> Name |
>> Type | Handler | Description
>> --------+-------+----------------------+-------------------------------
>> ---------
>> brin | index | brinhandler | block range index (BRIN)
>> access method
>> btree | index | bthandler | b-tree index access method
>> gin | index | ginhandler | GIN index access method
>> gist | index | gisthandler | GiST index access method
>> hash | index | hashhandler | hash index access method
>> heap | table | heap_tableam_handler | heap table access method
>> spgist | index | spghandler | SP-GiST index access method
>> (7 rows)
>
> Looks nice, but this fails for 9.4 or 9.5 server. I'm not sure
> how far back versions we should support, though.

The command \dA initially displayed an error message when working
on a server version below 9.6, and I did not change this logic.
I'm not sure, but it probably makes sense for versions 9.4 and 9.5
to output something like this query does:
SELECT
a.amname AS "AM",
d.description AS "Description"
FROM pg_am a
JOIN pg_description d ON a.oid = d.objoid
ORDER BY 1;

#\dA
AM | Description
--------+-----------------------------
btree | b-tree index access method
gin | GIN index access method
gist | GiST index access method
hash | hash index access method
spgist | SP-GiST index access method

SELECT
a.amname AS "AM",
CASE WHEN a.amcanorder THEN 'yes' ELSE 'no' END AS "Ordering",
CASE WHEN a.amcanunique THEN 'yes' ELSE 'no' END AS "Unique
indexes",
CASE WHEN a.amcanmulticol THEN 'yes' ELSE 'no' END AS "Multicol
indexes",
CASE WHEN a.amsearchnulls THEN 'yes' ELSE 'no' END AS "Searching
NULLs",
CASE WHEN a.amclusterable THEN 'yes' ELSE 'no' END AS "Clusterale"
FROM pg_am a
JOIN pg_description d ON a.oid = d.objoid
ORDER BY 1;

#dA NAME
AM | Ordering | Unique indexes | Multicol indexes | Searching
NULLs
| Clusterale
--------+----------+----------------+------------------+-----------------+------------
btree | yes | yes | yes | yes
| yes
gin | no | no | yes | no
| no
gist | no | no | yes | yes
| yes
hash | no | no | no | no
| no
spgist | no | no | no | yes
| no
(5 rows)

>
>> The functionality of the \dAp command has been moved to \dA NAME.
>> Now the user can query the properties of a particular AM (or several,
>> using the search pattern) as follows:
>>
>> # \dA h*
>> Index access
>> method properties
>> AM | Can order | Support unique indexes | Support indexes with
>> multiple columns | Support exclusion constraints | Can include non-key
>> columns
>> ------+-----------+------------------------+---------------------------
>> ------------+-------------------------------+------------------------
>> -----
>> hash | no | no |
>> no | yes
>> |
>> no
>> (1 row)
>
> In the earlier patches they were "Can order", "Can unique", "Can
> multi col", "Can exclude" and they indeed look
> too-short. Nevertheless the current column names occupies the top
> four places on the podium by their length. "Foreign-data wrapeer"
> is on the fifth place. Most of them are just one noun. Some of
> them are two-or-three-word nouns. Some of them are single-word
> adjective followed by '?'. \dicp uses single-word adverbs or
> a-few-words nouns without trailing '?'. How about the following?
>
> 8 Ordering yes/no
> 14 Unique indexes yes/no
> 16 Multicol indexes yes/no
> 21 Exclusion constraints yes/no
> 23 Include non-key columns yes/no
> =====
> 20 Foreign-data wrapper
>
>
> Does anyone have better wordings? Or, are the current wordings OK?

I like this version.

>> # \dAo gin jsonb_ops
>> List operators of family related to access method
>> AM | Opfamily Schema | Opfamily Name | Operator
>> -----+-----------------+---------------+--------------------
>> gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb)
>> gin | pg_catalog | jsonb_ops | ? (jsonb, text)
>> gin | pg_catalog | jsonb_ops | ?| (jsonb, text[])
>> gin | pg_catalog | jsonb_ops | ?& (jsonb, text[])
>> (4 rows)
>
> I'm not sure but couldn't we show the opfamily name in full
> qualified? The schema is not a property of the AM.
Now Opfamily Schema is shown if opfamily name is not visible in the
current
schema search path (check by pg_opfamily_is_visible().

>
>> # \dAo+ gist circle_ops
>> List operators of family related to access
>> method
>> AM | Opfamily Schema | Opfamily Name | Operator |
>> Strategy | Purpose | Sort family
>> ------+-----------------+---------------+----------------------+-------
>> ---+----------+-------------
>> gist | pg_catalog | circle_ops | << (circle,
>> circle) | 1 | search |
>> ...
>> gist | pg_catalog | circle_ops | <-> (circle,
>> point) | 15 | ordering | float_ops
>
> "Sort family" doesn't make sense. "Sort opfamily" or "Sort
> operator family"?

Renamed.

>> The \dAop command has been renamed to \dAp.
>> It displays list of support procedures associated with access method
>> operator families.
>> # \dAp hash array_ops
>> List of operator family procedures
>> AM | Family schema | Family name | Left | Right | Number
>> ------+---------------+-------------+----------+----------+--------
>> hash | pg_catalog | array_ops | anyarray | anyarray | 1
>> hash | pg_catalog | array_ops | anyarray | anyarray | 2
>> (2 rows)
>>
>> # \dAp+ hash array_ops
>> List of operator family procedures
>> AM | Family schema | Family name | Left | Right | Number
>> | Proc name
>> ------+---------------+-------------+----------+----------+--------+---
>> ------------------
>> hash | pg_catalog | array_ops | anyarray | anyarray | 1 |
>> hash_array
>> hash | pg_catalog | array_ops | anyarray | anyarray | 2 |
>> hash_array_extended
>> (2 rows)
>>
>> It may be easier for the user to navigate in this list if the defining
>> feature in addition to the number is also the procedure name.
>> Even if it does not carry important information, it improves the
>> readability of the list. Maybe it makes sense to return field "Proc
>> name" to the main output?
>
> "Number", "Proc name" doens't seem descriptive enough. It is
> mentioned as support function number in the documentation. The
> "Left" and "Right" are not necessarily parameter types of "Proc
> name". But I don't come up with better namings. It is a bit
> different thing, but "Left/Right arg type" is used elsewhere as
> parameter types.
>
> How about "AM", "Operator family", "Left arg type", "Right arg
> type" and "Support function number", "Support function"? The
> second from the last is 23 characters long. It could be "Support
> number" instead.

I have no better idea how to improve naming so I used the names you
suggested.

>
>> 0002-psql_add_index_info-v5.patch
>>
> I'm fine with this, but fails for 9.4 and 9.5. Also \dicp fails.

Maybe I missed something, but it works well on 9.4 and 9.5 for me.

Regards,
Sergey Cherkashin.