Re: Psql patch to show access methods info

On 22.06.2018 16:48, Sergey Cherkashin wrote:

> Hello!
>
> There are command in psql to list access methods, but there are no fast
> way to look detailed info about them. So here a patch with new
> commands:

Hi!

I've done a preliminary in-company review of this patch several times.
Here is my review of its first published version.

> \dAp     [PATTERN]           list access methods with properties (Table
> pg_am)

* Should we rename it to \dAip and include "index" word into the table header?
As you know, we are going to support table AMs in the future.

> \dAf[+]  [AMPTRN [OPFPTRN]]  list operator families of access method. +
> prints owner of operator family. (Table pg_opfamily)

> \dAfp    [AMPTRN [OPFPTRN]]  list procedures of operator family related
> to access method (Table pg_amproc)

* Reorder "Left"/"Right" and "Strategy"/"Proc name" columns.
* Include "Left"/"Right" columns into ORDER BY clause.
* Show procedure's argument types, because procedure's name does not completely
identify procedure (for example, in_range() is used in several opclasses with
different signatures). Or maybe show arguments only if procedure name is not
unique?

> \dAfo    [AMPTRN [OPFPTRN]]  list operators of family related to access
> method (Table pg_amop)

* Reorder "Left"/"Right" and "Strategy"/"Operator" columns.
* Include "Left"/"Right" columns into ORDER BY clause.
* Operator's schema is shown only if operator is invisible for the current
user -- I'm not sure if this is correct.

> \dAoc[+] [AMPTRN [OPCPTRN]]  list operator classes of index access
> methods. + prints owner of operator class. (Table pg_opclass)

* Maybe it would be better to show stored type only if it differs from the
indexed type?

> \dip[S]  [PATTERN]           list indexes with properties (Table
> pg_class)

> \dicp[S] [IDXNAME [COLNAME]] show index column properties (Table
> pg_class)

* Fix duplicate rows that appear in the table for composite indices.
* Include "Column #" into ORDER BY clause.
* Rename column "Null first" to "Nulls First" or "NULLS LAST".
* Maybe it is not necessary to show "Access method" column here?
* ASC, NULLS are shown as TRUE/FALSE only if the index is orderable, and as
NULL if unorderable -- I'm not sure if this is correct. Maybe we should
simply show these properties in the literal form, not as booleans
(as strings 'ASC'/'DESC', 'NULLS FIRST'/'NULLS LAST')?
* I think we should show column's properties in the separate table for each
index, because it is not so easy to understand the combined table.
The same, perhaps, can be applied to \dAfp and \dAfo commands.

> I also have a question about testing commands \dAf+ and \dAoc+: is it
> good idea to test them by changing an owner of one operator family or
> class to created new one, checking the output, and restoring the owner
> back? Or we should create a new opclass or opfamily with proper owner.
> Or maybe it is not necesary to test these commands?
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company