Re: Psql patch to show access methods info

Thank you for the new version.

At Fri, 22 Mar 2019 21:29:09 +0300, Sergey Cherkashin <s(dot)cherkashin(at)postgrespro(dot)ru> wrote in <fd9f7eb2ffc800157fbf35fa8aa7733a9cbce7cb(dot)camel(at)postgrespro(dot)ru>
> Taking into account the wishes of all the reviewers, the current
> position of the patch is as follows:
>
> The \dA command displays a list of access methods.
>
> # \dA
> List of access methods
> Name | Type | Handler
> --------+-------+----------------------
> brin | index | brinhandler
> btree | index | bthandler
> gin | index | ginhandler
> gist | index | gisthandler
> hash | index | hashhandler
> heap | table | heap_tableam_handler
> spgist | index | spghandler
> (7 rows)
>
> 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 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?

> Table access method properties
> Name | Type | Handler | Description
> ------+-------+----------------------+--------------------------
> heap | table | heap_tableam_handler | heap table access method
> (1 row)
>
> Note that for heap, as well as for future table AM, a separate table is
> displayed, since it is not clear which properties can be displayed for
> them.

Yeah. I think that's fine.

> The \dAoc command has been renamed to \dAc.
> The command displays information about operator classes. The "Input
> type" field was left, because the user may first be interested in what
> type of data opclass can work with,
> and in the second - how it will keep this type inside. Nikita also
> chose to leave the opfamily field as additional information.
>
> # \dAc btree name
> Index access method operator classes
> AM | Input type | Storage type | Operator class | Default?
> -------+------------+--------------+----------------+----------
> btree | name | cstring | name_ops | yes
> (1 row)
>
> # \dAc+ btree record
> Index access method operator classes
> AM | Input type | Storage type | Operator class | Default? |
> Operator family | Owner
> -------+------------+--------------+------------------+----------+-----
> -------------+-------
> btree | record | | record_image_ops | no |
> record_image_ops | zloj
> btree | record | | record_ops | yes |
> record_ops | zloj
> (2 rows)
>
> The \dAfo command has been renamed to \dAo.
> \dAo displays information about operators as follows:
>
> # \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.

> # \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"?

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

> 0002-psql_add_index_info-v5.patch
>
> The commands \dip and \dicp have so far been left in the current form,
> because although they display properties common to the whole AM,
> as Nikita already wrote, this properties can be redefined.
>
> # \dip pg_am_oid_index
> Index properties
> Schema | Name | Access method | Clusterable | Index
> scan | Bitmap scan | Backward scan
> ------------+-----------------+---------------+-------------+--------
> ----+-------------+---------------
> pg_catalog | pg_am_oid_index | btree | yes |
> yes | yes | yes
> (1 row)

I'm fine with this, but fails for 9.4 and 9.5. Also \dicp fails.

> # \dicp pg_amop_opr_fam_index
> Index
> pg_catalog.pg_amop_opr_fam_index
> Column name | Expr | Opclass | ASC | Nulls first | Orderable |
> Distance orderable | Returnable | Search array | Search nulls
> -------------+-------------+----------+-----+-------------+-----------
> +--------------------+------------+--------------+--------------
> amopopr | amopopr | oid_ops | yes | no | yes |
> no | yes | yes | yes
> amoppurpose | amoppurpose | char_ops | yes | no | yes |
> no | yes | yes | yes
> amopfamily | amopfamily | oid_ops | yes | no | yes |
> no | yes | yes | yes
> Table: pg_amop
> Access method: btree
>
> Also please look through the documentation for these features. I am
> sure that the information specified there can be submitted in a more
> accurate and convenient form.
>
> P.S. Since the formatting of the letter can brake the form of the
> tables, I attach a text file with the same content so that you do not
> have to do too much copy/paste to see original view =)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center