Re: Psql patch to show access methods info

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)

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)

                 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.

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)

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

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?


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)

# \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 =)

Sincerely
Sergey Cherkashin.