pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: s.cherkashin@postgrespro.ru
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: n.gluhov@postgrespro.ru
Cc: david@pgmasters.net
Cc: michael@paquier.xyz
Cc: alvherre@2ndquadrant.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: Psql patch to show access methods info
Date: Sun, 31 Mar 2019 04:13:07 +0300
Message-ID: <b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru> (raw )
In-Reply-To: <20190326.201220.229385888.horiguchi.kyotaro@lab.ntt.co.jp >
References: <20190308.135236.65082441.horiguchi.kyotaro@lab.ntt.co.jp >
<c742d473-25f4-ddb3-aaf8-ab421eaacc21@postgrespro.ru >
<fd9f7eb2ffc800157fbf35fa8aa7733a9cbce7cb.camel@postgrespro.ru >
<20190326.201220.229385888.horiguchi.kyotaro@lab.ntt.co.jp >
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.
Attachments:
[text/x-diff] 0001-psql_add_am_info-v6.patch (28.8K, ../b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru/2-0001-psql_add_am_info-v6.patch)
download | inline diff:
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f4aabf5dc7..9ed0f83996 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
</para>
- <table>
+ <table id="catalog-pg-amop-table">
<title><structname>pg_amop</structname> Columns</title>
<tgroup cols="4">
@@ -818,7 +818,7 @@
is one row for each support function belonging to an operator family.
</para>
- <table>
+ <table id="catalog-pg-amproc-table">
<title><structname>pg_amproc</structname> Columns</title>
<tgroup cols="4">
@@ -4458,7 +4458,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator classes are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opclass-table">
<title><structname>pg_opclass</structname> Columns</title>
<tgroup cols="4">
@@ -4720,7 +4720,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator families are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opfamily-table">
<title><structname>pg_opfamily</structname> Columns</title>
<tgroup cols="4">
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1b5d82ed8e..da85f63400 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1219,11 +1219,82 @@ testdb=>
<listitem>
<para>
- Lists access methods. If <replaceable
- class="parameter">pattern</replaceable> is specified, only access
- methods whose names match the pattern are shown. If
- <literal>+</literal> is appended to the command name, each access
- method is listed with its associated handler function and description.
+ Lists access methods with their associated handler function. If
+ <literal>+</literal> is appended to the command name, additional
+ description is provided.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ the command displays the properties of the access methods whose names
+ match the search pattern.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <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>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab259c473a..7b57c7cb49 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,25 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
- success = describeAccessMethods(pattern, show_verbose);
+ {
+ char *pattern2 = NULL;
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
+
+ if (strncmp(cmd, "dAo", 3) == 0)
+ success = listFamilyClassOperators(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAp", 3) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAc", 3) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else if (pattern)
+ success = describeAccessMethodProperties(pattern);
+ else
+ success = listAccessMethods(show_verbose);
+
+ if (pattern2)
+ free(pattern2);
+ }
break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f94a7a9c30..d3e349498f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -14,6 +14,7 @@
#include <ctype.h>
+#include "catalog/pg_am.h"
#include "catalog/pg_attribute_d.h"
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
@@ -146,7 +147,7 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
* Takes an optional regexp to select particular access methods
*/
bool
-describeAccessMethods(const char *pattern, bool verbose)
+listAccessMethods(bool verbose)
{
PQExpBufferData buf;
PGresult *res;
@@ -168,31 +169,23 @@ describeAccessMethods(const char *pattern, bool verbose)
printfPQExpBuffer(&buf,
"SELECT amname AS \"%s\",\n"
" CASE amtype"
- " WHEN 'i' THEN '%s'"
- " WHEN 't' THEN '%s'"
- " END AS \"%s\"",
+ " WHEN 'i' THEN '%s'"
+ " WHEN 't' THEN '%s'"
+ " END AS \"%s\",\n"
+ " amhandler AS \"%s\"",
gettext_noop("Name"),
- gettext_noop("Index"),
- gettext_noop("Table"),
- gettext_noop("Type"));
+ gettext_noop("index"),
+ gettext_noop("table"),
+ gettext_noop("Type"),
+ gettext_noop("Handler"));
if (verbose)
- {
appendPQExpBuffer(&buf,
- ",\n amhandler AS \"%s\",\n"
- " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
- gettext_noop("Handler"),
+ ",\n pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
gettext_noop("Description"));
- }
-
appendPQExpBufferStr(&buf,
- "\nFROM pg_catalog.pg_am\n");
-
- processSQLNamePattern(pset.db, &buf, pattern, false, false,
- NULL, "amname", NULL,
- NULL);
-
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ "\nFROM pg_catalog.pg_am\n"
+ "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -5740,3 +5733,386 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dA NAME
+ * Describes access method properties.
+ *
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethodProperties(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ bool found_result = false;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns_i[] = {true, true, true, true, true, true};
+ static const bool translate_columns_t[] = {true, true, true, true};
+
+ if (pset.sversion < 90600)
+ {
+ char sverbuf[32];
+
+ psql_error("The server (version %s) does not support access methods.\n",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ "SELECT a.amname AS \"%1$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_order')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%4$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_unique')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_multi_col')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%6$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_exclude')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n"
+ :
+ "SELECT a.amname AS \"%1$s\",\n"
+ " CASE WHEN a.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%4$s\",\n"
+ " CASE WHEN a.amcanunique THEN '%2$s' ELSE '%3$s' END AS \"%5$s\",\n"
+ " CASE WHEN a.amcanmulticol THEN '%2$s' ELSE '%3$s' END AS \"%6$s\",\n"
+ " CASE WHEN a.amgettuple <> 0 THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n",
+ gettext_noop("AM"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Ordering"),
+ gettext_noop("Unique indexes"),
+ gettext_noop("Multicol indexes"),
+ gettext_noop("Exclusion constraints"));
+
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 110000
+ ? " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_include')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\""
+ : " CASE WHEN false THEN '%1$s' ELSE '%2$s' END AS \"%3$s\"",
+ gettext_noop("yes"), gettext_noop("no"),
+ gettext_noop("Include non-key columns"));
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_am a\n"
+ " WHERE a.amtype = 'i'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ NULL, "amname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns_i;
+ myopt.n_translate_columns = lengthof(translate_columns_i);
+
+ if (PQntuples(res) > 0)
+ {
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ found_result = true;
+ }
+
+ PQclear(res);
+
+ /* Table AM */
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT a.amname AS \"%s\",\n"
+ " 'table' AS \"%s\",\n"
+ " a.amhandler AS \"%s\",\n"
+ " pg_catalog.obj_description(a.oid, 'pg_am') AS \"%s\"\n"
+ "FROM pg_catalog.pg_am a\n"
+ " WHERE a.amtype = 't'\n",
+ gettext_noop("Name"),
+ gettext_noop("Type"),
+ gettext_noop("Handler"),
+ gettext_noop("Description"));
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ NULL, "a.amname", NULL, NULL);
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Table access method properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns_t;
+ myopt.n_translate_columns = lengthof(translate_columns_t);
+
+ if (PQntuples(res) > 0)
+ {
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ found_result = true;
+ }
+
+ PQclear(res);
+
+ if (!found_result)
+ psql_error("Did not find any AM named \"%s\".\n", pattern);
+
+ return true;
+}
+
+/*
+ * \dAo
+ * Lists operators associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listFamilyClassOperators(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, true, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', nsf.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " format ('%%s (%%s, %%s)',\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_operator_is_visible(op.oid) \n"
+ " THEN op.oprname::pg_catalog.text \n"
+ " ELSE o.amopopr::pg_catalog.regoper::pg_catalog.text \n"
+ " END,\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL),\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL)\n"
+ " ) AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Operator"));
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", o.amopstrategy AS \"%s\",\n"
+ " CASE o.amoppurpose\n"
+ " WHEN 'o' THEN '%s'\n"
+ " WHEN 's' THEN '%s'\n"
+ " END AS \"%s\",\n"
+ " ofs.opfname AS \"%s\"\n",
+ gettext_noop("Strategy"),
+ gettext_noop("ordering"),
+ gettext_noop("search"),
+ gettext_noop("Purpose"),
+ gettext_noop("Sort opfamily"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amop o\n"
+ " LEFT JOIN pg_catalog.pg_operator op ON op.oid = o.amopopr\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+
+ if (family_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;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List operators of family related to access method");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAp
+ * Lists procedures associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ns.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
+ " ap.amprocnum AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Operator family"),
+ gettext_noop("Left arg type"),
+ gettext_noop("Right arg type"),
+ gettext_noop("Support function"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", ap.amproc::pg_catalog.regproc::pg_catalog.text AS \"%s\"\n",
+ gettext_noop("Proc name"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amproc ap\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "ns.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf,
+ "ORDER BY 1, 2, 3, 4, 5;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator family procedures");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAc
+ * List index access method operator classes.
+ * Takes an optional regexp to select particular access method and operator class.
+ */
+bool
+describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *type_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT"
+ " am.amname AS \"%s\",\n"
+ " c.opcintype::pg_catalog.regtype AS \"%s\",\n"
+ " (CASE WHEN c.opckeytype <> 0 AND c.opckeytype <> c.opcintype\n"
+ " THEN c.opckeytype\n"
+ " ELSE NULL -- c.opcintype\n"
+ " END)::pg_catalog.regtype AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opclass_is_visible(c.oid)\n"
+ " THEN format('%%I', c.opcname)\n"
+ " ELSE format('%%I.%%I', n.nspname, c.opcname)\n"
+ " END AS \"%s\",\n"
+ " (CASE WHEN c.opcdefault\n"
+ " THEN '%s'\n"
+ " ELSE '%s'\n"
+ " END) AS \"%s\"",
+ gettext_noop("AM"),
+ gettext_noop("Input type"),
+ gettext_noop("Storage type"),
+ gettext_noop("Operator class"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Default?"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ofn.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\"\n",
+ gettext_noop("Operator family"),
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ "\nFROM pg_catalog.pg_opclass c\n"
+ " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
+ " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = c.opcintype\n"
+ );
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
+ " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname", NULL, NULL);
+ if (type_pattern)
+ processSQLNamePattern(pset.db, &buf, type_pattern, have_where, false,
+ NULL, "t1.typname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method operator classes");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 4ff1f91f38..6cce80f260 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,7 +13,7 @@
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
/* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool listAccessMethods(bool verbose);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
@@ -111,4 +111,21 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \dA foo*/
+extern bool describeAccessMethodProperties(const char *pattern);
+
+/* \dAp */
+extern bool listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern,
+ bool verbose);
+
+/* \dAo */
+extern bool listFamilyClassOperators(const char *accessMethod_pattern,
+ const char *family_pattern, bool verbose);
+
+/* \dAc */
+extern bool describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *opclass_pattern,
+ bool verbose);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 6fc4ebab1e..a8d2b53b62 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -224,7 +224,11 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\d[S+] list tables, views, and sequences\n"));
fprintf(output, _(" \\d[S+] NAME describe table, view, sequence, or index\n"));
fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n"));
- fprintf(output, _(" \\dA[+] [PATTERN] list access methods\n"));
+ fprintf(output, _(" \\dA[+] list access methods\n"));
+ fprintf(output, _(" \\dA NAME describe properties of access method\n"));
+ fprintf(output, _(" \\dAc[+] [AMPTRN [TYPEPTRN]] list operator classes of index access methods\n"));
+ fprintf(output, _(" \\dAo[+] [AMPTRN [OPFPTRN]] list operators of family related to access method\n"));
+ fprintf(output, _(" \\dAp[+] [AMPTRN [OPFPTRN]] list procedures of operator family related to access method\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n"));
fprintf(output, _(" \\dC[+] [PATTERN] list casts\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d6d8fd02f5..770d08f843 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -472,6 +472,23 @@ static const SchemaQuery Query_for_list_of_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_operator_families = {
+ /* min_server_version */
+ 0,
+ /* catname */
+ "pg_catalog.pg_opfamily c",
+ /* selcondition */
+ NULL,
+ /* viscondition */
+ "true",
+ /* namespace */
+ "c.opfnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.opfname)",
+ /* qualresult */
+ NULL
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1378,7 +1395,8 @@ psql_completion(const char *text, int start, int end)
"\\a",
"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
"\\copyright", "\\crosstabview",
- "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
+ "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc",
+ "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp",
@@ -3492,6 +3510,12 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS("\\da*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS("\\dAp*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
+ else if (TailMatchesCS("\\dAo*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
+ else if (TailMatchesCS("\\dAc*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
else if (TailMatchesCS("\\dA*"))
COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (TailMatchesCS("\\db*"))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index aa101de906..7496ede282 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4578,3 +4578,56 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+-- check printing info about access methods
+\dA gin
+ Index access method properties
+ AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns
+-----+----------+----------------+------------------+-----------------------+-------------------------
+ gin | no | no | yes | no | no
+(1 row)
+
+\dAo brin uuid_minmax_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+------+-----------------+-----------------
+ brin | uuid_minmax_ops | < (uuid, uuid)
+ brin | uuid_minmax_ops | <= (uuid, uuid)
+ brin | uuid_minmax_ops | = (uuid, uuid)
+ brin | uuid_minmax_ops | >= (uuid, uuid)
+ brin | uuid_minmax_ops | > (uuid, uuid)
+(5 rows)
+
+\dAo * pg_catalog.jsonb_path_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+-----+----------------+-------------------
+ gin | jsonb_path_ops | @> (jsonb, jsonb)
+(1 row)
+
+\dAp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+------+-----------------+---------------+----------------+------------------
+ brin | uuid_minmax_ops | uuid | uuid | 1
+ brin | uuid_minmax_ops | uuid | uuid | 2
+ brin | uuid_minmax_ops | uuid | uuid | 3
+ brin | uuid_minmax_ops | uuid | uuid | 4
+(4 rows)
+
+\dAp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+-------+-----------------+---------------+----------------+------------------
+ btree | uuid_ops | uuid | uuid | 1
+ btree | uuid_ops | uuid | uuid | 2
+ hash | uuid_ops | uuid | uuid | 1
+ hash | uuid_ops | uuid | uuid | 2
+(4 rows)
+
+\dAc brin pg*.oid*
+ Index access method operator classes
+ AM | Input type | Storage type | Operator class | Default?
+------+------------+--------------+----------------+----------
+ brin | oid | | oid_minmax_ops | yes
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index fb7d17fc76..74cc7142da 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1031,3 +1031,11 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+-- check printing info about access methods
+\dA gin
+\dAo brin uuid_minmax_ops
+\dAo * pg_catalog.jsonb_path_ops
+\dAp brin uuid_minmax_ops
+\dAp * pg_catalog.uuid_ops
+\dAc brin pg*.oid*
[text/x-diff] 0002-psql_add_index_info-v6.patch (16.6K, ../b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru/3-0002-psql_add_index_info-v6.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index da85f63400..16d45d5c49 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1446,6 +1446,34 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Shows index properties listed in
+ <xref linkend="functions-info-index-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Shows index column properties listed in
+ <xref linkend="functions-info-index-column-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 7b57c7cb49..0ab4a4c2ab 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -807,6 +807,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'v':
case 'm':
case 'i':
+ if (strncmp(cmd, "dip", 3) == 0)
+ {
+ success = describeIndexProperties(pattern, show_system);
+ break;
+ }
+ else if (strncmp(cmd, "dicp", 4) == 0)
+ {
+ success = describeIndexColumnProperties(pattern, show_system);
+ break;
+ }
case 's':
case 'E':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d3e349498f..de66534973 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -19,6 +19,7 @@
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_default_acl_d.h"
+#include "catalog/pg_index.h"
#include "fe_utils/string_utils.h"
#include "common.h"
@@ -45,6 +46,9 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
const char *pnspname, const char *prsname);
static void printACLColumn(PQExpBuffer buf, const char *colname);
static bool listOneExtensionContents(const char *extname, const char *oid);
+static bool describeOneIndexColumnProperties(const char *oid, const char *nspname,
+ const char *idxname, const char *amname,
+ const char *tabname);
/*----------------
@@ -6116,3 +6120,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern,
PQclear(res);
return true;
}
+
+/*
+ * \dip
+ * Describes index properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexProperties(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT"
+ " n.nspname AS \"%s\",\n"
+ " c.relname AS \"%s\",\n"
+ " am.amname AS \"%s\",\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Access method"));
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n"
+ :
+ " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n"
+ " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n"
+ " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n",
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Clusterable"),
+ gettext_noop("Index scan"),
+ gettext_noop("Bitmap scan"),
+ gettext_noop("Backward scan"));
+ appendPQExpBufferStr(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"
+ "WHERE c.relkind='i'\n"
+ " AND n.nspname !~ 'pg_toast'\n");
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "c.relname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dicp
+ * Describes index index column properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexColumnProperties(const char *index_pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ int i;
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT c.oid,\n"
+ " n.nspname,\n"
+ " c.relname,\n"
+ " am.amname,\n"
+ " c2.relname\n"
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"
+ " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n");
+
+ appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n");
+
+ if (!showSystem && !index_pattern)
+ appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n"
+ "AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, index_pattern, true, false,
+ "n.nspname", "c.relname", NULL,
+ "pg_catalog.pg_table_is_visible(c.oid)");
+
+ appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ {
+ if (index_pattern)
+ psql_error("Did not find any index named \"%s\"\n",
+ index_pattern);
+ else
+ psql_error("Did not find any relations.\n");
+ }
+ PQclear(res);
+ return false;
+ }
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ const char *oid = PQgetvalue(res, i, 0);
+ const char *nspname = PQgetvalue(res, i, 1);
+ const char *idxname = PQgetvalue(res, i, 2);
+ const char *amname = PQgetvalue(res, i, 3);
+ const char *tabname = PQgetvalue(res, i, 4);
+
+ if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname,
+ tabname))
+ {
+ PQclear(res);
+ return false;
+ }
+ if (cancel_pressed)
+ {
+ PQclear(res);
+ return false;
+ }
+ }
+
+ PQclear(res);
+ return true;
+}
+
+static bool
+describeOneIndexColumnProperties(const char *oid,
+ const char *nspname,
+ const char *idxname,
+ const char *amname,
+ const char *tabname)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ char *footers[3] = {NULL, NULL};
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " a.attname AS \"%s\",\n"
+ " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n"
+ " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n",
+ gettext_noop("Column name"),
+ gettext_noop("Expr"),
+ gettext_noop("Opclass"));
+
+ if (pset.sversion >= 90600)
+ appendPQExpBuffer(&buf,
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n"
+ " THEN '%1$s' ELSE '%2$s' END \n"
+ " ELSE NULL"
+ " END AS \"%3$s\","
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n"
+ " THEN '%1$s' ELSE '%2$s' END \n"
+ " ELSE NULL"
+ " END AS \"%4$s\","
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n",
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("ASC"),
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Returnable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+ else
+ appendPQExpBuffer(&buf,
+ " CASE WHEN am.amcanorder THEN CASE\n"
+ " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */
+ " THEN '%2$s' ELSE '%3$s' END\n"
+ " ELSE NULL END AS \"%4$s\",\n"
+ " CASE WHEN am.amcanorder THEN CASE\n"
+ " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */
+ " THEN '%2$s' ELSE '%3$s' END\n"
+ " ELSE NULL END AS \"%6$s\",\n"
+ " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n"
+ " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n"
+ " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n"
+ " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n",
+ INDOPTION_DESC,
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("ASC"),
+ INDOPTION_NULLS_FIRST,
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n");
+ if (pset.sversion < 90600)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n");
+ appendPQExpBuffer(&buf,
+ "WHERE c.oid = %s\n"
+ "ORDER BY a.attnum",
+ oid);
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+ if (PQntuples(res) == 0)
+ {
+ PQclear(res);
+ return true;
+ }
+
+ myopt.nullPrint = NULL;
+ myopt.title = psprintf(_("Index %s.%s"), nspname, idxname);
+ footers[0] = psprintf(_("Table: %s"), tabname);
+ footers[1] = psprintf(_("Access method: %s"), amname);
+ myopt.footers = footers;
+ myopt.topt.default_footer = false;
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 6cce80f260..86ce18071b 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -128,4 +128,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter
const char *opclass_pattern,
bool verbose);
+/* \dip */
+extern bool describeIndexProperties(const char *pattern, bool showSystem);
+
+/* \dicp */
+extern bool describeIndexColumnProperties(const char *indexPattern,
+ bool showSystem);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a8d2b53b62..1c9a242e2e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -247,6 +247,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n"));
+ fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 770d08f843..93218741c2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1398,8 +1398,8 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc",
"\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
- "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
- "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
+ "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip",
+ "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 7496ede282..33036cadad 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4631,3 +4631,19 @@ List operators of family related to access method
brin | oid | | oid_minmax_ops | yes
(1 row)
+-- check printing info about indexes
+\dip pg_am_name_index
+ Index properties
+ Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan
+------------+------------------+---------------+-------------+------------+-------------+---------------
+ pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes
+(1 row)
+
+\dicp pg_am_name_index
+ Index pg_catalog.pg_am_name_index
+ Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+--------------
+ amname | amname | name_ops | yes | no | yes | no | yes | yes | yes
+Table: pg_am
+Access method: btree
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 74cc7142da..0b7daa5d0e 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1039,3 +1039,6 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\dAp brin uuid_minmax_ops
\dAp * pg_catalog.uuid_ops
\dAc brin pg*.oid*
+-- check printing info about indexes
+\dip pg_am_name_index
+\dicp pg_am_name_index
view thread (56+ messages) latest in thread
Message-ID: <b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru>
Permalink: ../b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru/
Also on: postgresql.org/message-id/b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: s.cherkashin@postgrespro.ru, horiguchi.kyotaro@lab.ntt.co.jp, n.gluhov@postgrespro.ru, david@pgmasters.net, michael@paquier.xyz, alvherre@2ndquadrant.com
Subject: Re: Psql patch to show access methods info
In-Reply-To: <b416da7f636a3c712abf5f2b0318ff0e@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox