pgsql: Show opclass and opfamily related information in psql

Started by Alexander Korotkovalmost 6 years ago7 messages

akorotkov@postgresql.org

almost 6 years ago

Show opclass and opfamily related information in psql

This commit provides psql commands for listing operator classes, operator
families and its contents in psql. New commands will be useful for exploring
capabilities of both builtin opclasses/opfamilies as well as
opclasses/opfamilies defined in extensions.

Discussion: /messages/by-id/1529675324.14193.5.camel@postgrespro.ru
Author: Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov
Reviewed-by: Michael Paquier, Alvaro Herrera, Arthur Zakirov
Reviewed-by: Kyotaro Horiguchi, Andres Freund

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b0b5e20cd8d1a58a8782d5dc806a5232db116e2f

Modified Files
--------------
doc/src/sgml/ref/psql-ref.sgml | 91 ++++++++++
src/bin/psql/command.c | 33 +++-
src/bin/psql/describe.c | 335 +++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 19 +++
src/bin/psql/help.c | 4 +
src/bin/psql/tab-complete.c | 16 +-
src/test/regress/expected/psql.out | 162 ++++++++++++++++++
src/test/regress/sql/psql.sql | 18 ++
8 files changed, 676 insertions(+), 2 deletions(-)

Alvaro Herrera

alvherre@2ndquadrant.com

over 5 years ago

In reply to: Alexander Korotkov (#1)

1 attachment(s)

Re: pgsql: Show opclass and opfamily related information in psql

On 2020-Mar-08, Alexander Korotkov wrote:

Show opclass and opfamily related information in psql

This commit provides psql commands for listing operator classes, operator
families and its contents in psql. New commands will be useful for exploring
capabilities of both builtin opclasses/opfamilies as well as
opclasses/opfamilies defined in extensions.

I had chance to use these new commands this morning. I noticed the
ORDER BY clause of \dAo is not very useful; for example:

=# \dAo+ brin datetime_minmax_ops
List of operators of operator families
AM │ Opfamily Name │ Operator │ Strategy │ Purpose │ Sort opfamily
──────┼─────────────────────┼───────────────────────────────────────────────────────────────┼──────────┼─────────┼───────────────
brin │ datetime_minmax_ops │ < (date, date) │ 1 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp with time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp without time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, date) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, timestamp with time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, timestamp without time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, date) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, timestamp with time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, timestamp without time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, date) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp with time zone) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp without time zone) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, date) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, timestamp with time zone) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, timestamp without time zone) │ 2 │ search │

Note how operator for strategy 1 are all together, then strategy 2, and
so on. But I think we'd prefer the operators to be grouped together for
the same types (just like \dAp already works); so I would change the clause
from:
ORDER BY 1, 2, o.amopstrategy, 3;
to:
ORDER BY 1, 2, pg_catalog.format_type(o.amoplefttype, NULL), pg_catalog.format_type(o.amoprighttype, NULL), o.amopstrategy;

which gives this table:

AM │ Opfamily Name │ Operator │ Strategy │ Purpose │ Sort opfamily
──────┼─────────────────────┼───────────────────────────────────────────────────────────────┼──────────┼─────────┼───────────────
brin │ datetime_minmax_ops │ < (date, date) │ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, date) │ 2 │ search │
brin │ datetime_minmax_ops │ = (date, date) │ 3 │ search │
brin │ datetime_minmax_ops │ >= (date, date) │ 4 │ search │
brin │ datetime_minmax_ops │ > (date, date) │ 5 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp with time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp with time zone) │ 2 │ search │
brin │ datetime_minmax_ops │ = (date, timestamp with time zone) │ 3 │ search │
brin │ datetime_minmax_ops │ >= (date, timestamp with time zone) │ 4 │ search │
brin │ datetime_minmax_ops │ > (date, timestamp with time zone) │ 5 │ search │

Also, while I'm going about this, ISTM it'd make sense to
list same-class operators first, followed by cross-class operators.
That requires to add "o.amoplefttype = o.amoprighttype DESC," after
"ORDER BY 1, 2,". For brin's integer_minmax_ops, the resulting list
would have first (bigint,bigint) then (integer,integer) then
(smallint,smallint), then all the rest:

brin │ integer_minmax_ops │ < (bigint, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, integer) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, smallint) │ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, integer) │ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, smallint) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, smallint) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, integer) │ 5 │ search │

instead of listing putting cross-type ops that have bigint first, which
are of secundary importance, which is what you get without it:

brin │ integer_minmax_ops │ < (bigint, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, integer) │ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, smallint) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, integer) │ 5 │ search │
brin │ integer_minmax_ops │ < (integer, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (integer, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (integer, smallint) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, bigint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, bigint) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, bigint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, bigint) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, bigint) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, integer) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, integer) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, integer) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, integer) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, integer) │ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, smallint) │ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, smallint) │ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, smallint) │ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, smallint) │ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, smallint) │ 5 │ search │

which in my mind is a clear improvement.

So I propose the attached patch.

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

Attachments:

sort-dAo.patchtext/x-diff; charset=us-asciiDownload

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8dca6d8bb4..9bd0bf8356 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6288,7 +6288,11 @@ listOpFamilyOperators(const char *access_method_pattern,
 		processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
 							  "nsf.nspname", "of.opfname", NULL, NULL);
 
-	appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+	appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
+						 "  o.amoplefttype = o.amoprighttype DESC,\n"
+						 "  pg_catalog.format_type(o.amoplefttype, NULL),\n"
+						 "  pg_catalog.format_type(o.amoprighttype, NULL),\n"
+						 "  o.amopstrategy;");
 
 	res = PSQLexec(buf.data);
 	termPQExpBuffer(&buf);

Alvaro Herrera

alvherre@2ndquadrant.com

over 5 years ago

In reply to: Alvaro Herrera (#2)

Re: pgsql: Show opclass and opfamily related information in psql

I would appreciate opinions from the patch authors on this ordering
change (rationale in previous email). I forgot to CC Sergei and Nikita.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8dca6d8bb4..9bd0bf8356 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6288,7 +6288,11 @@ listOpFamilyOperators(const char *access_method_pattern,
processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
"nsf.nspname", "of.opfname", NULL, NULL);

-	appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+	appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
+						 "  o.amoplefttype = o.amoprighttype DESC,\n"
+						 "  pg_catalog.format_type(o.amoplefttype, NULL),\n"
+						 "  pg_catalog.format_type(o.amoprighttype, NULL),\n"
+						 "  o.amopstrategy;");

res = PSQLexec(buf.data);
termPQExpBuffer(&buf);

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

Alexander Korotkov

a.korotkov@postgrespro.ru

over 5 years ago

In reply to: Alvaro Herrera (#2)

Re: pgsql: Show opclass and opfamily related information in psql

Hi!

On Tue, May 12, 2020 at 12:09 AM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

On 2020-Mar-08, Alexander Korotkov wrote:

Show opclass and opfamily related information in psql

This commit provides psql commands for listing operator classes, operator
families and its contents in psql. New commands will be useful for exploring
capabilities of both builtin opclasses/opfamilies as well as
opclasses/opfamilies defined in extensions.

I had chance to use these new commands this morning.

Great, thank you!

Note how operator for strategy 1 are all together, then strategy 2, and
so on. But I think we'd prefer the operators to be grouped together for
the same types (just like \dAp already works); so I would change the clause
from:
ORDER BY 1, 2, o.amopstrategy, 3;
to:
ORDER BY 1, 2, pg_catalog.format_type(o.amoplefttype, NULL), pg_catalog.format_type(o.amoprighttype, NULL), o.amopstrategy;

Also, while I'm going about this, ISTM it'd make sense to
list same-class operators first, followed by cross-class operators.
That requires to add "o.amoplefttype = o.amoprighttype DESC," after
"ORDER BY 1, 2,". For brin's integer_minmax_ops, the resulting list
would have first (bigint,bigint) then (integer,integer) then
(smallint,smallint), then all the rest:

Nikita, what do you think?

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

Nikita Glukhov

n.gluhov@postgrespro.ru

over 5 years ago

In reply to: Alexander Korotkov (#4)

Re: pgsql: Show opclass and opfamily related information in psql

On 14.05.2020 12:52, Alexander Korotkov wrote:

Nikita, what do you think?

I agree that this patch is an improvement.

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

Alexander Korotkov

a.korotkov@postgrespro.ru

over 5 years ago

In reply to: Nikita Glukhov (#5)

Re: pgsql: Show opclass and opfamily related information in psql

On Thu, May 14, 2020 at 1:30 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:

I agree that this patch is an improvement.

OK, I'm going to push this patch if no objections.
(Sergey doesn't seem to continue involvement in PostgreSQL
development, so it doesn't look like we should wait for him)

------
Alexander Korotkov

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

Alexander Korotkov

a.korotkov@postgrespro.ru

over 5 years ago

In reply to: Alexander Korotkov (#6)

Re: pgsql: Show opclass and opfamily related information in psql

On Thu, May 14, 2020 at 1:34 PM Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:

On Thu, May 14, 2020 at 1:30 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:

I agree that this patch is an improvement.

OK, I'm going to push this patch if no objections.
(Sergey doesn't seem to continue involvement in PostgreSQL
development, so it doesn't look like we should wait for him)

Pushed. I also applied the same ordering modification to \dAp.

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