Re: Psql patch to show access methods info

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:

\dAp     [PATTERN]           list access methods with properties (Table
pg_am)
\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)
\dAfo    [AMPTRN [OPFPTRN]]  list operators of family related to access
method (Table pg_amop)
\dAoc[+] [AMPTRN [OPCPTRN]]  list operator classes of index access
methods. + prints owner of operator class. (Table pg_opclass)
\dip[S]  [PATTERN]           list indexes with properties (Table
pg_class)
\dicp[S] [IDXNAME [COLNAME]] show index column properties (Table
pg_class)

You can display information only on the access methods, specified by a
template. You can also filter operator classes, operator families, or
the name of the indexed column.

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?

Best regards,
Sergey Cherkashin
s(dot)cherkashin(at)postgrespro(dot)ru

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

Following issues are solved:

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

>> \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?
> * 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.

Following issues require discussion:
>> \dAp  
> * 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.

>> \dAfo
> * Operator's schema is shown only if operator is invisible for the
> current
> user -- I'm not sure if this is correct.
\dAfo and \dAfp
* Should we put info in separate table for each Operator family?

>> \dicp
> * 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 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?

On Tue, Jul 03, 2018 at 01:25:37PM +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote:
> diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
> index 3ed9021..b699548 100644
> --- a/doc/src/sgml/catalogs.sgml
> +++ b/doc/src/sgml/catalogs.sgml

Please note that the latest patch proposed does not apply anymore. This
has been moved to CF 2018-11 with waiting on author as new status.
--
Michael

On 2018-Oct-01, Michael Paquier wrote:

> On Tue, Jul 03, 2018 at 01:25:37PM +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote:
> > diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
> > index 3ed9021..b699548 100644
> > --- a/doc/src/sgml/catalogs.sgml
> > +++ b/doc/src/sgml/catalogs.sgml
>
> Please note that the latest patch proposed does not apply anymore. This
> has been moved to CF 2018-11 with waiting on author as new status.

Here's a rebased version, fixing the rejects, pgindenting, and fixing
some "git show --check" whitespace issues. Haven't reviewed any further
than that.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote:
> Here's a rebased version, fixing the rejects, pgindenting, and fixing
> some "git show --check" whitespace issues. Haven't reviewed any further
> than that.

Schema qualifications are missing in many places, and they are added
sometimes. The character limit in documentation paragraph could be more
respected as well.

+ opereator families associated with whose name matches the
pattern are shown.
s/opereator/operator/.

+ List procedures (<xref linkend="catalog-pg-amproc-table"/>)
accociated with access method operator families.
s/accociated/associated/.
--
Michael

On 2018-Nov-19, Michael Paquier wrote:

> On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote:
> > Here's a rebased version, fixing the rejects, pgindenting, and fixing
> > some "git show --check" whitespace issues. Haven't reviewed any further
> > than that.
>
> Schema qualifications are missing in many places, and they are added
> sometimes. The character limit in documentation paragraph could be more
> respected as well.

Sergey, are you available to fix these issues? Nikita?

Thanks

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Yes, I am available to finish this patch.
I’m sorry that I hadn’t updated patch for new commitfest and I grateful
to you for doing it and fixing some issues.
I would like to clarify which commands lack the output of the schema
names? Because I tried to display them for all objects that have a
schema.

Best regards,
Sergej Cherkashin.

On 2018-11-19 05:38, Alvaro Herrera wrote:
> On 2018-Nov-19, Michael Paquier wrote:
>
>> On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote:
>> > Here's a rebased version, fixing the rejects, pgindenting, and fixing
>> > some "git show --check" whitespace issues. Haven't reviewed any further
>> > than that.
>>
>> Schema qualifications are missing in many places, and they are added
>> sometimes. The character limit in documentation paragraph could be
>> more
>> respected as well.
>
> Sergey, are you available to fix these issues? Nikita?
>
> Thanks

On 2018-Nov-20, s(dot)cherkashin(at)postgrespro(dot)ru wrote:

> Yes, I am available to finish this patch.
> I’m sorry that I hadn’t updated patch for new commitfest and I grateful to
> you for doing it and fixing some issues.
> I would like to clarify which commands lack the output of the schema names?
> Because I tried to display them for all objects that have a schema.

I think Michael is referring to the queries used to obtain the data.
For example "FROM pg_class c" is bogus -- it must be "FROM
pg_catalog.pg_class c".

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Ok, I fixed this.

On 2018-11-20 13:41, Alvaro Herrera wrote:
> On 2018-Nov-20, s(dot)cherkashin(at)postgrespro(dot)ru wrote:
>
>> Yes, I am available to finish this patch.
>> I’m sorry that I hadn’t updated patch for new commitfest and I
>> grateful to
>> you for doing it and fixing some issues.
>> I would like to clarify which commands lack the output of the schema
>> names?
>> Because I tried to display them for all objects that have a schema.
>
> I think Michael is referring to the queries used to obtain the data.
> For example "FROM pg_class c" is bogus -- it must be "FROM
> pg_catalog.pg_class c".

On 2018-Nov-20, s(dot)cherkashin(at)postgrespro(dot)ru wrote:

> Ok, I fixed this.

Cool. I'm not sure this is a good idea: "c.relname::pg_catalog.regclass"
I would use c.oid::pg_catalog.regclass instead.

But before getting into those details, I think we should discuss the
user interface that this patch is offering:

\dip [am pattern]
lists index properties (according to doc patch)
* OK, but why do we need an AM pattern? ... reads regress output ...
oh, actually it's an index name pattern, not an AM pattern. Please fix docs.

\dicp [idx pattern] [column pattern]
list index column properties
* I think the column pattern part is pointless.

\dA{f,p,fo,fp,oc}
Please explain what these are.

I think this is two patches -- one being the \dip/\dicp part, the other
the \dA additions. Let's deal with them separately?

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hello,

On 20.11.2018 16:08, s(dot)cherkashin(at)postgrespro(dot)ru wrote:
> Ok, I fixed this.

I looked at the patch. It is in good shape. It compiles and tests are
passed.

I have few a questions related with throwing errors. They might be silly :)

\dAp as well as \dA command throw an error if a server's version below 9.6:

"The server (version %s) does not support access methods"

But other \dA commands don't. It seems that there is enough information
in catalog for servers below 9.6. That is there are pg_am, pg_opfamily,
pg_amop and other catalog tables related with access methods.

\dAp calls pg_indexam_has_property() function, which doesn't exist in
servers 9.5 and below. Is this the reason that it throws an error? If so
then describeOneIndexColumnProperties() also should throw an error,
because it calls pg_index_column_has_property() function, which doesn't
exist in servers 9.5 and below.

What do you think?

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

> \dA{f,p,fo,fp,oc}
> Please explain what these are.
We adhere to the following logic
f - families
fo - operators in families
fp - procedures in families
p - access method properties
oc - operator classes

> I think this is two patches -- one being the \dip/\dicp part, the
> other
> the \dA additions. Let's deal with them separately?

The attached patches are applied sequentially: first 0003-
psql_add_am_info.patch, then 0003-psql_add_index_info.patch.

Best regards,
Sergey Cherkashin.

On Fri, Nov 23, 2018 at 05:13:24PM +0300, Sergey Cherkashin wrote:
> The attached patches are applied sequentially: first 0003-
> psql_add_am_info.patch, then 0003-psql_add_index_info.patch.

Thanks for doing a split. I have been looking at add_am to being with,
which is the first one in the set.

+ char *pattern2 =
psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);

The set of meta commands with a one-one mapping with the system catalogs
looks sensible to me, one suggestion I have would be to consider the
verbose option of all commands:
- \dAfp could have the strategy, purpose and sort purpose in its verbose
part.
- \dAfp could move the proc name with its arguments to the verbose
portion. I would imagine that removing the arguments could make sense.
- Is \dAf really useful as \dAfp actually proposes all the information
that really matters? And \dAfp joins with pg_opfamily.
- default and stored type could be moved to the verbose output of
\dAoc.

The columns names from \dAp could be better. What does "Can multi col"
mean? Well that's index support for multiple columns but that's rather
unclear for the user, no?

Wouldn't it be cleaner here to set the second pattern only if the first
pattern is defined?

+-- check printing info about access methods
+\dA
+List of access method
Regression tests are good for psql with deterministic matching patterns,
but I am not much a fan of things which print global results as they
result in more potential failures, and actually noise at the end. All
the tests checking unexisting patterns don't bring much either I think.

+ command name, each operator family is listed with it's owner.
s/it's/its/.

tab-complete.c:463:26: warning: ‘Query_for_list_of_operator_families’
defined but not used [-Wunused-const-variable=]
static const SchemaQuery Query_for_list_of_operator_families = {
Compiler complains.
--
Michael

Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you have
any suggestions on how to improve them, I will be very grateful.

Best regards,
Sergey Cherkashin.

On Mon, Dec 10, 2018 at 07:38:39PM +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote:
> Here are some fixes. But I'm not sure that the renaming of columns for the
> '\dAp' command is sufficiently laconic and informative. If you have any
> suggestions on how to improve them, I will be very grateful.

I have not put much thougts into that to be honest. For now I have
moved the patch to next CF.
--
Michael

Hello.

At Mon, 10 Dec 2018 19:38:39 +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf(at)postgrespro(dot)ru>
> Here are some fixes. But I'm not sure that the renaming of columns for
> the '\dAp' command is sufficiently laconic and informative. If you
> have any suggestions on how to improve them, I will be very grateful.

\dA:

This is showing almost nothing. I think it's better that this
command shows the same content with \dA+. As per Nikita's comment
upthread, "Table" addition to "Index" is needed.

\dAp:

As the result \dAp gets useless. It cannot handle both Index
and Table AMs at once.

So, I propose the following behavior instead. It is similar to
what \d does.

=# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | Index | brinhandler
..
heap | Table | heap_tableam_handler

=# \dA+
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
..
heap | Table | heap_tableam_handler | heap table access method

=# \dA brin
Index access method "brin"
Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
--------+----------+--------+--------------+--------------+---------------------
brin | No | Yes | No | No | No

\dA heap
Table access method "heap"
(I don't have an idea what to show here..)

\dAfo: I don't get the point of the command.

\dAoc: This seems more useful than \dAfo but the information that
the command shows seems a bit pointless. We sometimes want to
know the name of operator class usable in a CREATE INDEX. So I
suppose that something like the following might be useful
instead.

SELECT DISTINCT a.amname AS "Acess method",
(case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
n.nspname || '.' || o.opcname AS "Operator class",
(case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
FROM pg_catalog.pg_opclass o
JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
ORDER BY 1, 2, 4 desc, 3;

\dAoc
List of operator classes for access methods
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
brin | bytea | pg_catalog.bytea_minmax_ops | Yes
brin | "char" | pg_catalog.char_minmax_ops | Yes
brin | name | pg_catalog.name_minmax_ops | Yes
brin | bigint | pg_catalog.int8_minmax_ops | Yes
..

\dAoc btree
List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
btree | boolean | pg_catalog.bool_ops | Yes
...
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No

\dAoc btree text
List of operator classes for access method 'btree', type 'text'

List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+--------------------------------+------------------
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No
btree | text | pg_catalog.varchar_pattern_ops | No

I'm not sure it's useful, but \dAoc+ may print owner.

0002 no longer applies.

\dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.

\dip shows the following rseult.

Index properties
Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
ackward scan
--------+-----------+---------------+-------------+------------+-------------+--
-------------
public | x_a_idx | btree | t | t | t | t
public | tt_a_idx | brin | f | f | t | f
public | tt_a_idx1 | brin | f | f | t | f

The colums arfter "Access method" don't seem informatitve for
users since they are fixed properties of an access method, and
they doesn't make difference in what users can do. "Clusterable"
seems useful in certain extent, but it doesn't fit here. Instaed
\d <table> seems to me to be the place. (It could be shown also
in \di+, but that looks a bit odd to me.)

\d+ <table> is already showing (ASC)/DESC, and (NULLS
FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
section.

\d+ x
Table "public.x"
> Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
> ription
> --------+------+-----------+----------+---------+----------+--------------+-----
> --------
> a | text | | | | extended | |
> Indexes:
> "x_a_idx" btree (a varchar_ops)
- "x_a_idx1" btree (a DESC NULLS LAST)
+ "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble
> Access method: heap

# I'm not sure "clusterable" makes sense..

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi Sergey,

On 3/8/19 8:52 AM, Kyotaro HORIGUCHI wrote:
>
> At Mon, 10 Dec 2018 19:38:39 +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf(at)postgrespro(dot)ru>
>> Here are some fixes. But I'm not sure that the renaming of columns for
>> the '\dAp' command is sufficiently laconic and informative. If you
>> have any suggestions on how to improve them, I will be very grateful.
>
> \dA:
>
> This is showing almost nothing. I think it's better that this
> command shows the same content with \dA+. As per Nikita's comment
> upthread, "Table" addition to "Index" is needed.
>
> \dAp:
>
> As the result \dAp gets useless. It cannot handle both Index
> and Table AMs at once.
>
> So, I propose the following behavior instead. It is similar to
> what \d does.
>
> =# \dA
> List of access methods
> Name | Type | Handler
> --------+-------+----------------------
> brin | Index | brinhandler
> ..
> heap | Table | heap_tableam_handler
>
>
> =# \dA+
> Name | Type | Handler | Description
> --------+-------+----------------------+----------------------------------------
> brin | Index | brinhandler | block range index (BRIN) access method
> ..
> heap | Table | heap_tableam_handler | heap table access method
>
>
> =# \dA brin
> Index access method "brin"
> Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
> --------+----------+--------+--------------+--------------+---------------------
> brin | No | Yes | No | No | No
>
> \dA heap
> Table access method "heap"
> (I don't have an idea what to show here..)
>
>
>
> \dAfo: I don't get the point of the command.
>
> \dAoc: This seems more useful than \dAfo but the information that
> the command shows seems a bit pointless. We sometimes want to
> know the name of operator class usable in a CREATE INDEX. So I
> suppose that something like the following might be useful
> instead.
>
> SELECT DISTINCT a.amname AS "Acess method",
> (case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
> n.nspname || '.' || o.opcname AS "Operator class",
> (case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
> FROM pg_catalog.pg_opclass o
> JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
> JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
> JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
> ORDER BY 1, 2, 4 desc, 3;
>
> \dAoc
> List of operator classes for access methods
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+-----------------------------+-------------------
> brin | bytea | pg_catalog.bytea_minmax_ops | Yes
> brin | "char" | pg_catalog.char_minmax_ops | Yes
> brin | name | pg_catalog.name_minmax_ops | Yes
> brin | bigint | pg_catalog.int8_minmax_ops | Yes
> ..
>
>
> \dAoc btree
> List of operator classes for access method 'btree'
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+-----------------------------+-------------------
> btree | boolean | pg_catalog.bool_ops | Yes
> ...
> btree | text | pg_catalog.text_ops | Yes
> btree | text | pg_catalog.text_pattern_ops | No
> btree | text | pg_catalog.varchar_ops | No
>
> \dAoc btree text
> List of operator classes for access method 'btree', type 'text'
>
> List of operator classes for access method 'btree'
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+--------------------------------+------------------
> btree | text | pg_catalog.text_ops | Yes
> btree | text | pg_catalog.text_pattern_ops | No
> btree | text | pg_catalog.varchar_ops | No
> btree | text | pg_catalog.varchar_pattern_ops | No
>
> I'm not sure it's useful, but \dAoc+ may print owner.
>
>
>
> 0002 no longer applies.
>
> \dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.
>
> \dip shows the following rseult.
>
> Index properties
> Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
> ackward scan
> --------+-----------+---------------+-------------+------------+-------------+--
> -------------
> public | x_a_idx | btree | t | t | t | t
> public | tt_a_idx | brin | f | f | t | f
> public | tt_a_idx1 | brin | f | f | t | f
>
>
> The colums arfter "Access method" don't seem informatitve for
> users since they are fixed properties of an access method, and
> they doesn't make difference in what users can do. "Clusterable"
> seems useful in certain extent, but it doesn't fit here. Instaed
> \d <table> seems to me to be the place. (It could be shown also
> in \di+, but that looks a bit odd to me.)
>
>
> \d+ <table> is already showing (ASC)/DESC, and (NULLS
> FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
> section.
>
> \d+ x
> Table "public.x"
>> Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
>> ription
>> --------+------+-----------+----------+---------+----------+--------------+-----
>> --------
>> a | text | | | | extended | |
>> Indexes:
>> "x_a_idx" btree (a varchar_ops)
> - "x_a_idx1" btree (a DESC NULLS LAST)
> + "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble
>> Access method: heap
>
> # I'm not sure "clusterable" makes sense..

Your thoughts on these comments?

Regards,
--
-David
david(at)pgmasters(dot)net

Hi.

On 08.03.2019 7:52, Kyotaro HORIGUCHI wrote:

> Hello.
>
> At Mon, 10 Dec 2018 19:38:39 +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf(at)postgrespro(dot)ru>
>> Here are some fixes. But I'm not sure that the renaming of columns for
>> the '\dAp' command is sufficiently laconic and informative. If you
>> have any suggestions on how to improve them, I will be very grateful.
> \dA:
>
> This is showing almost nothing. I think it's better that this
> command shows the same content with \dA+. As per Nikita's comment
> upthread, "Table" addition to "Index" is needed.
>
> \dAp:
>
> As the result \dAp gets useless. It cannot handle both Index
> and Table AMs at once.
>
> So, I propose the following behavior instead. It is similar to
> what \d does.
>
> =# \dA
> List of access methods
> Name | Type | Handler
> --------+-------+----------------------
> brin | Index | brinhandler
> ..
> heap | Table | heap_tableam_handler
>
>
> =# \dA+
> Name | Type | Handler | Description
> --------+-------+----------------------+----------------------------------------
> brin | Index | brinhandler | block range index (BRIN) access method
> ..
> heap | Table | heap_tableam_handler | heap table access method
>
>
> =# \dA brin
> Index access method "brin"
> Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
> --------+----------+--------+--------------+--------------+---------------------
> brin | No | Yes | No | No | No

I completely agree. Also I propose the following renaming of commands
after \dAp removing:
\dAfo => \dAo
\dAfp => \dAp
\dAoc => \dAc

> \dA heap
> Table access method "heap"
> (I don't have an idea what to show here..)

Yes, there are no functions like pg_tableam_has_property() yet.

> \dAfo: I don't get the point of the command.

This commands helps to remember which operators can be accelerated up by
each index AM. Maybe operator name and its operand type would be better to
put into a single column. Also schema can be shown only when opfamily is not
visible, or in verbose mode.

For example, for jsonb type we could have:

\dAfo * jsonb*

List operators of family related to access method
AM | Schema | Opfamily | Operator
-------+------------+----------------+--------------------
btree | pg_catalog | jsonb_ops | < (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | <= (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | = (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | >= (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | > (jsonb, jsonb)
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[])
gin | pg_catalog | jsonb_path_ops | @> (jsonb, jsonb)
hash | pg_catalog | jsonb_ops | = (jsonb, jsonb)
(11 rows)

> \dAoc: This seems more useful than \dAfo but the information that
> the command shows seems a bit pointless. We sometimes want to
> know the name of operator class usable in a CREATE INDEX. So I
> suppose that something like the following might be useful
> instead.
>
> SELECT DISTINCT a.amname AS "Acess method",
> (case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
> n.nspname || '.' || o.opcname AS "Operator class",
> (case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
> FROM pg_catalog.pg_opclass o
> JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
> JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
> JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
> ORDER BY 1, 2, 4 desc, 3;
>
> \dAoc
> List of operator classes for access methods
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+-----------------------------+-------------------
> brin | bytea | pg_catalog.bytea_minmax_ops | Yes
> brin | "char" | pg_catalog.char_minmax_ops | Yes
> brin | name | pg_catalog.name_minmax_ops | Yes
> brin | bigint | pg_catalog.int8_minmax_ops | Yes
> ..
>
>
> \dAoc btree
> List of operator classes for access method 'btree'
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+-----------------------------+-------------------
> btree | boolean | pg_catalog.bool_ops | Yes
> ...
> btree | text | pg_catalog.text_ops | Yes
> btree | text | pg_catalog.text_pattern_ops | No
> btree | text | pg_catalog.varchar_ops | No
>
> \dAoc btree text
> List of operator classes for access method 'btree', type 'text'
>
> List of operator classes for access method 'btree'
> Access method | Key type | Operator class | Default for type?
> ---------------+----------+--------------------------------+------------------
> btree | text | pg_catalog.text_ops | Yes
> btree | text | pg_catalog.text_pattern_ops | No
> btree | text | pg_catalog.varchar_ops | No
> btree | text | pg_catalog.varchar_pattern_ops | No
>
> I'm not sure it's useful, but \dAoc+ may print owner.

Mostly I agree with this idea.

I think opfamily should be shown too, if we want to list the corresponding
operators then. But \dAfo could take a type name pattern instead of opfamily
pattern. Also it seems that the same multi-table showing method can be used
in \dAfo too.

Does AM/type name really need to be duplicated in "AM", "Type" columns, if we
will show each AM/type in the separate table?

> 0002 no longer applies.
>
> \dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.
>
> \dip shows the following rseult.
>
> Index properties
> Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
> ackward scan
> --------+-----------+---------------+-------------+------------+-------------+--
> -------------
> public | x_a_idx | btree | t | t | t | t
> public | tt_a_idx | brin | f | f | t | f
> public | tt_a_idx1 | brin | f | f | t | f
>
>
> The colums arfter "Access method" don't seem informatitve for
> users since they are fixed properties of an access method, and
> they doesn't make difference in what users can do. "Clusterable"
> seems useful in certain extent, but it doesn't fit here. Instead
> \d <table> seems to me to be the place. (It could be shown also
> in \di+, but that looks a bit odd to me.)

These index properties are really not fixed properties of AM, because AMs have
ability to override them in its amproperty() method, however, none of the core
AM does this.

> \d+ <table> is already showing (ASC)/DESC, and (NULLS
> FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
> section.
>
> \d+ x
> Table "public.x"
>> Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
>> ription
>> --------+------+-----------+----------+---------+----------+--------------+-----
>> --------
>> a | text | | | | extended | |
>> Indexes:
>> "x_a_idx" btree (a varchar_ops)
> - "x_a_idx1" btree (a DESC NULLS LAST)
> + "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble
>> Access method: heap
> # I'm not sure "clusterable" makes sense..
>
> regards.

--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

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.

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

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.

On Sun, Mar 31, 2019 at 2:13 PM <s(dot)cherkashin(at)postgrespro(dot)ru> wrote:
> Thanks for review.

Hi Sergey,

A new Commitfest is here and this doesn't apply -- could you please
post a rebase?

Thanks,

--
Thomas Munro
https://enterprisedb.com

On 01.07.2019 14:06, Thomas Munro wrote:

> On Sun, Mar 31, 2019 at 2:13 PM <s(dot)cherkashin(at)postgrespro(dot)ru> wrote:
>> Thanks for review.
> Hi Sergey,
>
> A new Commitfest is here and this doesn't apply -- could you please
> post a rebase?
>
> Thanks,

Attached 7th version of the patches rebased onto current master.

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Mon, Jul 15, 2019 at 10:05 PM Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> wrote:
> On 01.07.2019 14:06, Thomas Munro wrote:
>
> > On Sun, Mar 31, 2019 at 2:13 PM <s(dot)cherkashin(at)postgrespro(dot)ru> wrote:
> >> Thanks for review.
> > Hi Sergey,
> >
> > A new Commitfest is here and this doesn't apply -- could you please
> > post a rebase?
> >
> > Thanks,
>
> Attached 7th version of the patches rebased onto current master.

Thank you for posting this patch. It looks good to me.

I've one note. Behavior of "\dA" and "\dA pattern" look
counter-intuitive to me. I would rather expect that "\dA pattern"
would just filter results of "\dA", but it displays different
information. I suggest rename displaying access method properties
from "\dA pattern" to different. And leave "\dA pattern" just filter
results of "\dA".

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 2019-Jul-21, Alexander Korotkov wrote:

> I've one note. Behavior of "\dA" and "\dA pattern" look
> counter-intuitive to me. I would rather expect that "\dA pattern"
> would just filter results of "\dA", but it displays different
> information. I suggest rename displaying access method properties
> from "\dA pattern" to different.

\dA+ maybe? Then ...

> And leave "\dA pattern" just filter results of "\dA".

"\dA+ pattern" works intuitively, I think.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Jul 22, 2019 at 6:29 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> On 2019-Jul-21, Alexander Korotkov wrote:
> > I've one note. Behavior of "\dA" and "\dA pattern" look
> > counter-intuitive to me. I would rather expect that "\dA pattern"
> > would just filter results of "\dA", but it displays different
> > information. I suggest rename displaying access method properties
> > from "\dA pattern" to different.
>
> \dA+ maybe? Then ...
>
> > And leave "\dA pattern" just filter results of "\dA".
>
> "\dA+ pattern" works intuitively, I think.

Sounds good for me.

We already have some functionality for \dA+.

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

What we need is that new \dA+ functionality cover existing one. That
it, we should add Handler and Description column to the output.

# \dA+ *
Index access method properties
AM | Ordering | Unique indexes | Multicol indexes | Exclusion
constraints | Include non-key columns
--------+----------+----------------+------------------+-----------------------+-------------------------
brin | no | no | yes | no
| no
btree | yes | yes | yes | yes
| yes
gin | no | no | yes | no
| no
gist | no | no | yes | yes
| yes
hash | no | no | no | yes
| no
spgist | no | no | no | yes
| no
(6 rows)

Table access method properties
Name | Type | Handler | Description
------+-------+----------------------+--------------------------
heap | table | heap_tableam_handler | heap table access method
(1 row)

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi,

On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote:
> + <varlistentry>
> + <term>
> + <literal>\dAc[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">input-type-pattern</replaceable></link>]]
> + </literal>
> + </term>
> + <listitem>
> + <para>
> + Shows info index access method operator classes listed in
> + <xref linkend="catalog-pg-opclass-table"/>.
> + If <replaceable class="parameter">access-method-patttern</replaceable>
> + is specified, only operator classes associated with access method whose
> + name matches pattern are shown.
> + If <replaceable class="parameter">input-type-pattern</replaceable>
> + is specified, only procedures associated with families whose input type
> + matches the pattern are shown.
> + If <literal>+</literal> is appended to the command name, operator family
> + and owner are listed.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>\dAo[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
> + </literal>
> + </term>
> +
> + <listitem>
> + <para>
> + Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated
> + with access method operator families. If
> + <replaceable class="parameter">access-method-patttern</replaceable> is
> + specified, only operators associated with access method whose name
> + matches pattern are shown. If
> + <replaceable class="parameter">operator-family-pattern</replaceable> is
> + specified, only operators associated with families whose name matches
> + the pattern are shown.
> + If <literal>+</literal> is appended to the command name, displays
> + additional info.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>\dAp[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
> + </literal>
> + </term>
> + <listitem>
> + <para>
> + Lists procedures (<xref linkend="catalog-pg-amproc-table"/>) associated
> + with access method operator families.
> + If <replaceable class="parameter">access-method-patttern</replaceable>
> + is specified, only procedures associated with access method whose name
> + matches pattern are shown.
> + If <replaceable class="parameter">operator-family-pattern</replaceable>
> + is specified, only procedures associated with families whose name
> + matches the pattern are shown.
> + If <literal>+</literal> is appended to the command name, procedures
> + listed with its names.
> </para>

Based on a quick skim of the thread - which means I most definitely
missed things - there's not been discussion of why we actually want to
add this. Who's the prospective user of this facility? And why wouldn't
they just query pg_am[proc]? None of this information seems like it's
going to be even remotely targeted towards even advanced users. For
developers it's not clear what these add?

Adding stuff to psql isn't free. It adds clutter to psql's help output,
the commands need to be maintained (including cross-version code).

Greetings,

Andres Freund

Attached 8th version of the patches.

On 22.07.2019 15:58, Alexander Korotkov wrote:
> On Mon, Jul 22, 2019 at 6:29 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
>> On 2019-Jul-21, Alexander Korotkov wrote:
>>> I've one note. Behavior of "\dA" and "\dA pattern" look
>>> counter-intuitive to me. I would rather expect that "\dA pattern"
>>> would just filter results of "\dA", but it displays different
>>> information. I suggest rename displaying access method properties
>>> from "\dA pattern" to different.
>> \dA+ maybe? Then ...
>>
>>> And leave "\dA pattern" just filter results of "\dA".
>> "\dA+ pattern" works intuitively, I think.
> Sounds good for me.
>
> We already have some functionality for \dA+.
>
> # \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)
>
> What we need is that new \dA+ functionality cover existing one. That
> it, we should add Handler and Description column to the output.
>
> # \dA+ *
> Index access method properties
> AM | Ordering | Unique indexes | Multicol indexes | Exclusion
> constraints | Include non-key columns
> --------+----------+----------------+------------------+-----------------------+-------------------------
> brin | no | no | yes | no
> | no
> btree | yes | yes | yes | yes
> | yes
> gin | no | no | yes | no
> | no
> gist | no | no | yes | yes
> | yes
> hash | no | no | no | yes
> | no
> spgist | no | no | no | yes
> | no
> (6 rows)
>
> Table access method properties
> Name | Type | Handler | Description
> ------+-------+----------------------+--------------------------
> heap | table | heap_tableam_handler | heap table access method
> (1 row)

Columns "Handler" and "Description" were added to \dA+.

\dA [NAME] now shows only amname and amtype.

Also added support for pre-9.6 server versions to both \dA and \dA+.

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Mon, Jul 22, 2019 at 11:25 PM Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> wrote:
> Columns "Handler" and "Description" were added to \dA+.
>
> \dA [NAME] now shows only amname and amtype.

Cool!

> Also added support for pre-9.6 server versions to both \dA and \dA+.

I was going to ask about that. You got ahead of me :-)

In general, patchset is very cool. It was always scary there is no
way in psql to see am/opclass/opfamily information rather than query
catalog directly. Shape of patches also looks good.

I'm going to push it. Probably, someone find that commands syntax and
output formats are not well discussed yet. But we're pretty earlier
in 13 release cycle. So, we will have time to work out a criticism if
any.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi,

On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote:
> + <varlistentry>
> + <term>
> + <literal>\dAc[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">input-type-pattern</replaceable></link>]]
> + </literal>
> + </term>
> + <listitem>
> + <para>
> + Shows info index access method operator classes listed in
> + <xref linkend="catalog-pg-opclass-table"/>.
> + If <replaceable class="parameter">access-method-patttern</replaceable>
> + is specified, only operator classes associated with access method whose
> + name matches pattern are shown.
> + If <replaceable class="parameter">input-type-pattern</replaceable>
> + is specified, only procedures associated with families whose input type
> + matches the pattern are shown.
> + If <literal>+</literal> is appended to the command name, operator family
> + and owner are listed.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>\dAo[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
> + </literal>
> + </term>
> +
> + <listitem>
> + <para>
> + Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated
> + with access method operator families. If
> + <replaceable class="parameter">access-method-patttern</replaceable> is
> + specified, only operators associated with access method whose name
> + matches pattern are shown. If
> + <replaceable class="parameter">operator-family-pattern</replaceable> is
> + specified, only operators associated with families whose name matches
> + the pattern are shown.
> + If <literal>+</literal> is appended to the command name, displays
> + additional info.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>\dAp[+]
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
> + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
> + </literal>
> + </term>
> + <listitem>
> + <para>
> + Lists procedures (<xref linkend="catalog-pg-amproc-table"/>) associated
> + with access method operator families.
> + If <replaceable class="parameter">access-method-patttern</replaceable>
> + is specified, only procedures associated with access method whose name
> + matches pattern are shown.
> + If <replaceable class="parameter">operator-family-pattern</replaceable>
> + is specified, only procedures associated with families whose name
> + matches the pattern are shown.
> + If <literal>+</literal> is appended to the command name, procedures
> + listed with its names.
> </para>

Based on a quick skim of the thread - which means I most definitely
missed things - there's not been discussion of why we actually want to
add this. Who's the prospective user of this facility? And why wouldn't
they just query pg_am[proc]? None of this information seems like it's
going to be even remotely targeted towards even advanced users. For
developers it's not clear what these add?

Adding stuff to psql isn't free. It adds clutter to psql's help output,
the commands need to be maintained (including cross-version code).

Greetings,

Andres Freund

Hi,

On 2019-07-23 01:57:29 +0300, Alexander Korotkov wrote:
> It was always scary there is no way in psql to see am/opclass/opfamily
> information rather than query catalog directly.

What does make that scary?

> I'm going to push it. Probably, someone find that commands syntax and
> output formats are not well discussed yet. But we're pretty earlier
> in 13 release cycle. So, we will have time to work out a criticism if
> any.

Please don't before we've had some discussion as to why we want this
additional code, and who'd be helped by it.

Greetings,

Andres Freund

Hi!

On Wed, Jul 24, 2019 at 9:00 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2019-07-23 01:57:29 +0300, Alexander Korotkov wrote:
> > It was always scary there is no way in psql to see am/opclass/opfamily
> > information rather than query catalog directly.
>
> What does make that scary?

For it's unclear why do we have backslash commands for observing
almost every part of system catalog, but this quite large part is
missed.

> > I'm going to push it. Probably, someone find that commands syntax and
> > output formats are not well discussed yet. But we're pretty earlier
> > in 13 release cycle. So, we will have time to work out a criticism if
> > any.
>
> Please don't before we've had some discussion as to why we want this
> additional code, and who'd be helped by it.

OK. Given that few senior developers participate in discussion of
details, I thought we kind of agree that need this. Now you've
explicitly express other opinion, so let's discuss.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Wed, Jul 24, 2019 at 9:01 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> Based on a quick skim of the thread - which means I most definitely
> missed things - there's not been discussion of why we actually want to
> add this. Who's the prospective user of this facility? And why wouldn't
> they just query pg_am[proc]? None of this information seems like it's
> going to be even remotely targeted towards even advanced users. For
> developers it's not clear what these add?

I see your point regarding pg_am details. Probably nobody expect
developers need this. And probably even developers don't need this,
because it's easier to see IndexAmRoutine directly with more details.
So, +1 for removing this.

pg_amproc for gin/gist/sp-gist/brin is probably for developers. But I
think pg_amproc for btree/hash could be useful for advanced users.
btree/hash opclasses could be written by advanced users using
pl/something, I've faced that several times.

> Adding stuff to psql isn't free. It adds clutter to psql's help output,
> the commands need to be maintained (including cross-version code).

Sure.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Wed, Jul 24, 2019 at 4:59 PM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Wed, Jul 24, 2019 at 9:01 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > Based on a quick skim of the thread - which means I most definitely
> > missed things - there's not been discussion of why we actually want to
> > add this. Who's the prospective user of this facility? And why wouldn't
> > they just query pg_am[proc]? None of this information seems like it's
> > going to be even remotely targeted towards even advanced users. For
> > developers it's not clear what these add?
>
> I see your point regarding pg_am details. Probably nobody expect
> developers need this. And probably even developers don't need this,
> because it's easier to see IndexAmRoutine directly with more details.
> So, +1 for removing this.
>
> pg_amproc for gin/gist/sp-gist/brin is probably for developers. But I
> think pg_amproc for btree/hash could be useful for advanced users.
> btree/hash opclasses could be written by advanced users using
> pl/something, I've faced that several times.

Revised patch is attached. Changes to \dA+ command are reverted. It
also contains some minor improvements.

Second patch looks problematic for me, because it provides index
description alternative to \d+. IMHO, if there is something really
useful to display about index, we should keep it in \d+. So, I
propose to postpone this.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 2019-Aug-06, Alexander Korotkov wrote:

> Revised patch is attached. Changes to \dA+ command are reverted. It
> also contains some minor improvements.
>
> Second patch looks problematic for me, because it provides index
> description alternative to \d+. IMHO, if there is something really
> useful to display about index, we should keep it in \d+. So, I
> propose to postpone this.

Are you saying that we should mark this entire CF entry as Returned with
Feedback? Or do you see a subset of your latest 0001 as a commitable
patch?

Thanks

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> On 2019-Aug-06, Alexander Korotkov wrote:
>
> > Revised patch is attached. Changes to \dA+ command are reverted. It
> > also contains some minor improvements.
> >
> > Second patch looks problematic for me, because it provides index
> > description alternative to \d+. IMHO, if there is something really
> > useful to display about index, we should keep it in \d+. So, I
> > propose to postpone this.
>
> Are you saying that we should mark this entire CF entry as Returned with
> Feedback? Or do you see a subset of your latest 0001 as a commitable
> patch?

Still hope to commit 0001. Please, don't mark RFC for now.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > On 2019-Aug-06, Alexander Korotkov wrote:
> >
> > > Revised patch is attached. Changes to \dA+ command are reverted. It
> > > also contains some minor improvements.
> > >
> > > Second patch looks problematic for me, because it provides index
> > > description alternative to \d+. IMHO, if there is something really
> > > useful to display about index, we should keep it in \d+. So, I
> > > propose to postpone this.
> >
> > Are you saying that we should mark this entire CF entry as Returned with
> > Feedback? Or do you see a subset of your latest 0001 as a commitable
> > patch?
>
> Still hope to commit 0001. Please, don't mark RFC for now.

Sorry, I meant don't mark it RWF for now :)

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Sat, Sep 14, 2019 at 1:45 PM Alexander Korotkov <
a(dot)korotkov(at)postgrespro(dot)ru> wrote:
>
> On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> > On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera
> > <alvherre(at)2ndquadrant(dot)com> wrote:
> > > On 2019-Aug-06, Alexander Korotkov wrote:
> > >
> > > > Revised patch is attached. Changes to \dA+ command are reverted.
It
> > > > also contains some minor improvements.
> > > >
> > > > Second patch looks problematic for me, because it provides index
> > > > description alternative to \d+. IMHO, if there is something really
> > > > useful to display about index, we should keep it in \d+. So, I
> > > > propose to postpone this.
> > >
> > > Are you saying that we should mark this entire CF entry as Returned
with
> > > Feedback? Or do you see a subset of your latest 0001 as a commitable
> > > patch?
> >
> > Still hope to commit 0001. Please, don't mark RFC for now.
>
> Sorry, I meant don't mark it RWF for now :)
>
Few Comments:
+
+\dA+
+ List of access methods
+ Name | Type | Handler | Description

+--------+-------+----------------------+----------------------------------------
+ brin | Index | brinhandler | block range index (BRIN) access
method

We can add test for \dA+ brin btree

When we specify multiple arguments along with \dA+, like in case of:
\dA+ brin btree
We should display a message like \d+: extra argument "btree" ignored.

postgres=# \dA+ brin btree
List of access methods
Name | Type | Handler | Description
------+-------+-------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
(1 row)

Like in case of \d+ we get the message:
postgres=# \d+ t1 t2
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target
| Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
c1 | integer | | | | plain |
|
Access method: heap

\d+: extra argument "t2" ignored

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com

It seems strange that there's a way to display AMs, and a way to display
ops and procs in an opfamily; but there's no way to list what opfamilies
exist (possibly given an AM as pattern). Should we add that too? We
had \dAf in the original submission, but that seems to have lost along
the way, not sure why.

I think \dAf is just as critical as \dAo; the former lets you know which
opfamilies you can use in CREATE INDEX, while the latter lets you know
which operators would be helped by such an index. (But, really, only if
the opfamily name is printed in \d of the index, which we currently
don't print unless it's non-default ... which is an omission that
perhaps we should consider fixing).

On the other hand, from a user perspective, what you really want to know
is: what opfamilies exist for datatype T, and what operators are
supported by the opfamily I have chosen? The current patch doesn't
really help you find that out.

I think \dAp isn't terribly informative from a user perspective. The
support procs are just an opfamily implementation detail.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> It seems strange that there's a way to display AMs, and a way to display
> ops and procs in an opfamily; but there's no way to list what opfamilies
> exist (possibly given an AM as pattern). Should we add that too? We
> had \dAf in the original submission, but that seems to have lost along
> the way, not sure why.
>
> I think \dAf is just as critical as \dAo; the former lets you know which
> opfamilies you can use in CREATE INDEX, while the latter lets you know
> which operators would be helped by such an index. (But, really, only if
> the opfamily name is printed in \d of the index, which we currently
> don't print unless it's non-default ... which is an omission that
> perhaps we should consider fixing).
>
> On the other hand, from a user perspective, what you really want to know
> is: what opfamilies exist for datatype T, and what operators are
> supported by the opfamily I have chosen? The current patch doesn't
> really help you find that out.

I think you have a point. Will add \dAf command to the patch.

> I think \dAp isn't terribly informative from a user perspective. The
> support procs are just an opfamily implementation detail.

I've expressed my opinion regarding \dAp in [1]. In my observations,
some advanced users can write btree/hash opclasses in pl/* languages.
This doesn't require knowledge of core developer. And they may find
\dAp command useful. What do you think?

Links
1. https://www.postgresql.org/message-id/CAPpHfdtj_w20hTr4fHW4MnpL-pPGU3Mw0A9pRTRBL_XP-WGsyQ%40mail.gmail.com

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 2019-Sep-18, Alexander Korotkov wrote:

> On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

> > I think \dAf is just as critical as \dAo; the former lets you know which
> > opfamilies you can use in CREATE INDEX, while the latter lets you know
> > which operators would be helped by such an index. (But, really, only if
> > the opfamily name is printed in \d of the index, which we currently
> > don't print unless it's non-default ... which is an omission that
> > perhaps we should consider fixing).

> I think you have a point. Will add \dAf command to the patch.

Great, thanks.

I think in order for this feature to be more complete "\d index" should
show the opfamily name, also, even when it's the default one. (Let's
not put the opfamily when it's the default in "\d table", as we do when
the opfamily is not default; that would lead, I think, to too much
clutter.)

> > On the other hand, from a user perspective, what you really want to know
> > is: what opfamilies exist for datatype T, and what operators are
> > supported by the opfamily I have chosen? The current patch doesn't
> > really help you find that out.

I hope that in some future somebody will contribute towards this, which
I think is more important (from users POV) than the below one:

> > I think \dAp isn't terribly informative from a user perspective. The
> > support procs are just an opfamily implementation detail.
>
> I've expressed my opinion regarding \dAp in [1]. In my observations,
> some advanced users can write btree/hash opclasses in pl/* languages.
> This doesn't require knowledge of core developer. And they may find
> \dAp command useful. What do you think?

I have never tried or had the need to do that. I'll take your word for
it, so I have no objection.

I do wonder if \? is going to end up with too much clutter, and if so do
we need to make \? show only the most important commands and relegate
some others to \?+ ... however, going over the existing \? I see no
command that I would move to \?+ so \dAp would be alone there, which
would be pretty strange. So let's forget this angle for now; but if
psql acquires too much "system innards" functionality then I say we
should consider it.

Thanks

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hello. FWIW..

At Wed, 18 Sep 2019 11:04:40 -0300, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote in <20190918140440(dot)GA28323(at)alvherre(dot)pgsql>
> I think in order for this feature to be more complete "\d index" should
> show the opfamily name, also, even when it's the default one. (Let's
> not put the opfamily when it's the default in "\d table", as we do when
> the opfamily is not default; that would lead, I think, to too much
> clutter.)
>
> > > On the other hand, from a user perspective, what you really want to know
> > > is: what opfamilies exist for datatype T, and what operators are
> > > supported by the opfamily I have chosen? The current patch doesn't
> > > really help you find that out.

I have thought that several times.

> I hope that in some future somebody will contribute towards this, which
> I think is more important (from users POV) than the below one:
>
> > > I think \dAp isn't terribly informative from a user perspective. The
> > > support procs are just an opfamily implementation detail.
> >
> > I've expressed my opinion regarding \dAp in [1]. In my observations,
> > some advanced users can write btree/hash opclasses in pl/* languages.
> > This doesn't require knowledge of core developer. And they may find
> > \dAp command useful. What do you think?
>
> I have never tried or had the need to do that. I'll take your word for
> it, so I have no objection.
>
> I do wonder if \? is going to end up with too much clutter, and if so do
> we need to make \? show only the most important commands and relegate
> some others to \?+ ... however, going over the existing \? I see no
> command that I would move to \?+ so \dAp would be alone there, which
> would be pretty strange. So let's forget this angle for now; but if
> psql acquires too much "system innards" functionality then I say we
> should consider it.

Before the fact that usable slot of two-letter commands is almost
filled, my poor memory rejects to remember the commands that is
used infrequently.. ctrl-I suggests many two-or-three letter
meta commands but I can't tell what is the command I'm searching
for. \? shows too many commands as you mentioned.

If something like "\? | grep index" works, it would be helpful.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Wed, Sep 18, 2019 at 5:04 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> On 2019-Sep-18, Alexander Korotkov wrote:
>
> > On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
>
> > > I think \dAf is just as critical as \dAo; the former lets you know which
> > > opfamilies you can use in CREATE INDEX, while the latter lets you know
> > > which operators would be helped by such an index. (But, really, only if
> > > the opfamily name is printed in \d of the index, which we currently
> > > don't print unless it's non-default ... which is an omission that
> > > perhaps we should consider fixing).
>
> > I think you have a point. Will add \dAf command to the patch.
>
> Great, thanks.

Revised patch is attached.

1) It adds \dAf[+] command showing opfamilies, which belong to given
AM and have opclasses for given datatype.
2) It turns back warning when running \dA[+] with 2 or more arguments.

Two questions are open for me:

1) Currently we allow to filter opfamilies by type, but supported
types aren't displayed. Should we display datatypes? Should we
aggregate them into comma-separated list?
2) Given we now can display the list of opfamilies, it would be
reasonable to be able to see list of opclasses belonging to particular
opfamily. But currently \dAc doesn't have filter by opclass. Should
we implement this as an separate command?

I'll be very glad for feedback.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi Alexander,

On Mon, Sep 23, 2019 at 10:54:51PM +0300, Alexander Korotkov wrote:
> Revised patch is attached.

The commit log of the patch reads like that:
"Fix handling Inf and Nan values in GiST pairing heap comparator"

That's obviously incorrect. Do you have an updated patch? I am
moving that to next CF waiting on author.
--
Michael

Hi, Michael!

On Wed, Nov 27, 2019 at 11:05 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> On Mon, Sep 23, 2019 at 10:54:51PM +0300, Alexander Korotkov wrote:
> > Revised patch is attached.
>
> The commit log of the patch reads like that:
> "Fix handling Inf and Nan values in GiST pairing heap comparator"
>
> That's obviously incorrect. Do you have an updated patch? I am
> moving that to next CF waiting on author.

Sorry for this stupid error and for fixing it this late.
Correct patch is attached.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

I think I would like this feature to be in, but I'm not sure that the
shape is final yet. My points:

a) I don't see any use for \dA as presented; I think the \dA+ output is
useful. Therefore my preference would be that \dA presents what the
latest patch has as \dA+. I think we should leave \dA+ unimplemented
for now; maybe we can use some use for it later on.

b) I think \dAp should list the function used for each support proc. I
don't have any use for \dAp actually (I already said that upthread,
sorry for repeating myself), but I think that if we have it, then
showing only the proc number is pointless.

c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that
each opfamily has opclasses for. Maybe make the output an array, like
{int4,int8,numeric,...} Something like [*] but somehow make it
prettier?

d) This one I'm unsure about: should we list the opfamily for each
opclass in \dAc? I'm not sure whether it's useful for anything.

[*]
SELECT DISTINCT am.amname AS "AM",
CASE
WHEN pg_catalog.pg_opfamily_is_visible(f.oid)
THEN format('%I', f.opfname)
ELSE format('%I.%I', n.nspname, f.opfname)
END AS "Operator family",
string_agg(format_type(c.opcintype, -1), ', ') as "Applicable types",
pg_catalog.pg_get_userbyid(f.opfowner) AS "Owner"

FROM pg_catalog.pg_opfamily f
LEFT JOIN pg_catalog.pg_am am on am.oid = f.opfmethod
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = f.opfnamespace
left join pg_catalog.pg_opclass c on (f.oid = c.opcfamily)
group by 1, 2, 4 ORDER BY 1, 2;

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2020-Jan-21, Alvaro Herrera wrote:

> c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that
> each opfamily has opclasses for. Maybe make the output an array, like
> {int4,int8,numeric,...} Something like [*] but somehow make it
> prettier?

Sorry, I forgot to copy-edit my text here: I said "make it prettier",
but the query I submitted is already pretty enough ISTM; I had written
that comment when I only had the array_agg() version, but then I changed
it to string_agg() and that seems to have mostly done the trick. Maybe
improve the format_type() bit to omit the quotes, if possible, but that
doesn't seem a big deal.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi Alexander,

On 1/21/20 5:37 PM, Alvaro Herrera wrote:
> On 2020-Jan-21, Alvaro Herrera wrote:
>
>> c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that
>> each opfamily has opclasses for. Maybe make the output an array, like
>> {int4,int8,numeric,...} Something like [*] but somehow make it
>> prettier?
>
> Sorry, I forgot to copy-edit my text here: I said "make it prettier",
> but the query I submitted is already pretty enough ISTM; I had written
> that comment when I only had the array_agg() version, but then I changed
> it to string_agg() and that seems to have mostly done the trick. Maybe
> improve the format_type() bit to omit the quotes, if possible, but that
> doesn't seem a big deal.

The last CF for PG13 has now started. Do you know when you'll be able
to supply a new patch to address Álvaro's review?

Regards,
--
-David
david(at)pgmasters(dot)net

Hi!

Thank you for the review. Revised patch is attached.

On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> I think I would like this feature to be in, but I'm not sure that the
> shape is final yet. My points:
>
> a) I don't see any use for \dA as presented; I think the \dA+ output is
> useful. Therefore my preference would be that \dA presents what the
> latest patch has as \dA+. I think we should leave \dA+ unimplemented
> for now; maybe we can use some use for it later on.

Neither \dA or \dA+ are introduced or affected by this patch. If we
like to change their behavior, we should probably do this separately
from this patch.

> b) I think \dAp should list the function used for each support proc. I
> don't have any use for \dAp actually (I already said that upthread,
> sorry for repeating myself), but I think that if we have it, then
> showing only the proc number is pointless.

It was shown by \dAp+. But I agree that it's essential information
that is unreasonable to hide under verbose option. So, procedure name
is always shown now. I've also renamed "Support function" column to
"Number".

> c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that
> each opfamily has opclasses for. Maybe make the output an array, like
> {int4,int8,numeric,...} Something like [*] but somehow make it
> prettier?

I made this change, but using subselect in target list. It's probably
slower query, but better code readability IMHO.

> d) This one I'm unsure about: should we list the opfamily for each
> opclass in \dAc? I'm not sure whether it's useful for anything.

It's already shown by \dAc+ and I think this behavior is fine.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 2020-Mar-04, Alexander Korotkov wrote:

> On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> > I think I would like this feature to be in, but I'm not sure that the
> > shape is final yet. My points:
> >
> > a) I don't see any use for \dA as presented; I think the \dA+ output is
> > useful. Therefore my preference would be that \dA presents what the
> > latest patch has as \dA+. I think we should leave \dA+ unimplemented
> > for now; maybe we can use some use for it later on.
>
> Neither \dA or \dA+ are introduced or affected by this patch. If we
> like to change their behavior, we should probably do this separately
> from this patch.

Doh, you're right, sorry.

Looking only at the regress/expected/psql.out changes, I'm satisfied
with this version of the patch.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
wrote:
>
> Hi!
>
> Thank you for the review. Revised patch is attached.
>

Thanks for working on comments and providing a new patch.
One small observation I noticed:
postgres=# \*dAc* brin oid
Index access method operator classes
AM | Input type | Storage type | Operator class | Default?
------+------------+--------------+----------------+----------
brin | oid | | oid_minmax_ops | yes
(1 row)

postgres=# \*dAcx* brin oid
Index access method operator classes
AM | Input type | Storage type | Operator class | Default?
------+------------+--------------+----------------+----------
brin | oid | | oid_minmax_ops | yes
(1 row)

Output of \dAc and \dAcx seems to be same. Is this expected?

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com

On Thu, Mar 5, 2020 at 8:34 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> >
> > Hi!
> >
> > Thank you for the review. Revised patch is attached.
> >
>
> Thanks for working on comments and providing a new patch.
> One small observation I noticed:
> postgres=# \dAc brin oid
> Index access method operator classes
> AM | Input type | Storage type | Operator class | Default?
> ------+------------+--------------+----------------+----------
> brin | oid | | oid_minmax_ops | yes
> (1 row)
>
> postgres=# \dAcx brin oid
> Index access method operator classes
> AM | Input type | Storage type | Operator class | Default?
> ------+------------+--------------+----------------+----------
> brin | oid | | oid_minmax_ops | yes
> (1 row)
>
> Output of \dAc and \dAcx seems to be same. Is this expected?

It might seem strange, but majority of psql commands allows arbitrary
suffixes and ignore them. For instance:

postgres=# \dt
Did not find any relations.
postgres=# \dtttttt
Did not find any relations.

I think if we want to fix this, we should do it in a separate path,
which would fix at the psql commands.

BTW, new revision of the patch is attached. It contains cosmetic
changes to the documentation, comments etc.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Fri, Mar 6, 2020 at 6:28 AM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
>
> On Thu, Mar 5, 2020 at 8:34 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> > >
> > > Hi!
> > >
> > > Thank you for the review. Revised patch is attached.
> > >
> >
> > Thanks for working on comments and providing a new patch.
> > One small observation I noticed:
> > postgres=# \dAc brin oid
> > Index access method operator classes
> > AM | Input type | Storage type | Operator class | Default?
> > ------+------------+--------------+----------------+----------
> > brin | oid | | oid_minmax_ops | yes
> > (1 row)
> >
> > postgres=# \dAcx brin oid
> > Index access method operator classes
> > AM | Input type | Storage type | Operator class | Default?
> > ------+------------+--------------+----------------+----------
> > brin | oid | | oid_minmax_ops | yes
> > (1 row)
> >
> > Output of \dAc and \dAcx seems to be same. Is this expected?
>
> It might seem strange, but majority of psql commands allows arbitrary
> suffixes and ignore them. For instance:
>
> postgres=# \dt
> Did not find any relations.
> postgres=# \dtttttt
> Did not find any relations.
>
> I think if we want to fix this, we should do it in a separate path,
> which would fix at the psql commands.
>

I feel your explanation sounds fair to me.

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com

On Fri, Mar 6, 2020 at 7:10 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> I feel your explanation sounds fair to me.

Thanks.

I've also revised tab-completion code. I'm going to push this if no objections.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Fri, Mar 6, 2020 at 11:46 AM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Fri, Mar 6, 2020 at 7:10 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > I feel your explanation sounds fair to me.
>
> Thanks.
>
> I've also revised tab-completion code. I'm going to push this if no objections.

So, pushed!

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company