Hi Julien!
Thanks also for the documentation and regression tests. This overall looks
good, I just have a two comments:
Thank you for reviewing the patch! :-D
- there's a whitespace issue in the documentation part:
add_list_extended_stats_for_psql_by_dX_command.patch:10: tab in indent.
<varlistentry>
warning: 1 line adds whitespace errors.
Oops, I forgot to use "git diff --check". I fixed it.
- You're sorting the output on schema, table, extended statistics and columns
but I think the last one isn't required since extended statistics names are
unique.
You are right.
The sort key "columns" was not necessary so I removed it.
Attached new patch includes the above two fixes:
- Fix whitespace issue in the documentation part
- Remove unnecessary sort key from the query
(ORDER BY 1, 2, 3, 4 -> ORDER BY 1, 2, 3)
Thanks,
Tatsuro Yamada
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index fc16e6c..8b0568c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1893,6 +1893,18 @@ testdb=>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\dX [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists extended statistics.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only those extended statistics whose names match the
pattern
+ are listed.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\dy[+] [ <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 9902a4a..dcc9fba 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,9 @@ exec_command_d(PsqlScanState scan_state, bool
active_branch, const char *cmd)
else
success = listExtensions(pattern);
break;
+ case 'X': /* Extended Statistics
*/
+ success = listExtendedStats(pattern);
+ break;
case 'y': /* Event Triggers */
success = listEventTriggers(pattern,
show_verbose);
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d81f157..e43241f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4368,6 +4368,74 @@ listEventTriggers(const char *pattern, bool verbose)
}
/*
+ * \dX
+ *
+ * Briefly describes extended statistics.
+ */
+bool
+listExtendedStats(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 100000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support extended
statistics.",
+ formatPGVersionNumber(pset.sversion,
false,
+
sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT \n"
+
"stxnamespace::pg_catalog.regnamespace AS \"%s\", \n"
+ "c.relname AS \"%s\", \n"
+ "stxname AS \"%s\", \n"
+ "(SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') \n"
+ " FROM pg_catalog.unnest(stxkeys)
s(attnum) \n"
+ " JOIN pg_catalog.pg_attribute a ON
(stxrelid = a.attrelid AND \n"
+ " a.attnum = s.attnum AND NOT
attisdropped)) AS \"%s\", \n"
+ "'d' = any(stxkind) AS \"%s\", \n"
+ "'f' = any(stxkind) AS \"%s\", \n"
+ "'m' = any(stxkind) AS \"%s\" \n"
+ "FROM pg_catalog.pg_statistic_ext \n"
+ "INNER JOIN pg_catalog.pg_class c \n"
+ "ON stxrelid = c.oid \n",
+ gettext_noop("Schema"),
+ gettext_noop("Table"),
+ gettext_noop("Name"),
+ gettext_noop("Columns"),
+ gettext_noop("Ndistinct"),
+ gettext_noop("Dependencies"),
+ gettext_noop("MCV"));
+
+ processSQLNamePattern(pset.db, &buf, pattern, false,
+ false, NULL,
+ "stxname", NULL,
+ NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of extended statistics");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
* \dC
*
* Describes casts.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index f0e3ec9..f0b3f91 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -102,6 +102,9 @@ extern bool listExtensions(const char *pattern);
/* \dx+ */
extern bool listExtensionContents(const char *pattern);
+/* \dX */
+extern bool listExtendedStats(const char *pattern);
+
/* \dy */
extern bool listEventTriggers(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index af82928..35c39db 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -267,6 +267,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
+ fprintf(output, _(" \\dX [PATTERN] list extended
statistics\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's
definition\n"));
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 555d464..caad618 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5012,3 +5012,41 @@ List of access methods
hash | uuid_ops | uuid | uuid |
2 | uuid_hash_extended
(5 rows)
+-- check printing info about extended statistics
+create table t1 (a int, b int);
+create statistics stts_1 (dependencies) on a, b from t1;
+create statistics stts_2 (dependencies, ndistinct) on a, b from t1;
+create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;
+create table t2 (a int, b int, c int);
+create statistics stts_4 on b, c from t2;
+create table hoge (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from hoge;
+\dX
+ List of extended statistics
+ Schema | Table | Name | Columns | Ndistinct | Dependencies |
MCV
+--------+-------+-----------+------------------+-----------+--------------+-----
+ public | hoge | stts_hoge | col1, col2, col3 | t | t | t
+ public | t1 | stts_1 | a, b | f | t | f
+ public | t1 | stts_2 | a, b | t | t | f
+ public | t1 | stts_3 | a, b | t | t | t
+ public | t2 | stts_4 | b, c | t | t | t
+(5 rows)
+
+\dX stts_?
+ List of extended statistics
+ Schema | Table | Name | Columns | Ndistinct | Dependencies | MCV
+--------+-------+--------+---------+-----------+--------------+-----
+ public | t1 | stts_1 | a, b | f | t | f
+ public | t1 | stts_2 | a, b | t | t | f
+ public | t1 | stts_3 | a, b | t | t | t
+ public | t2 | stts_4 | b, c | t | t | t
+(4 rows)
+
+\dX *hoge
+ List of extended statistics
+ Schema | Table | Name | Columns | Ndistinct | Dependencies |
MCV
+--------+-------+-----------+------------------+-----------+--------------+-----
+ public | hoge | stts_hoge | col1, col2, col3 | t | t | t
+(1 row)
+
+drop table t1, t2, hoge;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 5a16080..163c1fb 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1207,3 +1207,20 @@ drop role regress_partitioning_role;
\dAo * pg_catalog.jsonb_path_ops
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
+
+-- check printing info about extended statistics
+create table t1 (a int, b int);
+create statistics stts_1 (dependencies) on a, b from t1;
+create statistics stts_2 (dependencies, ndistinct) on a, b from t1;
+create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;
+
+create table t2 (a int, b int, c int);
+create statistics stts_4 on b, c from t2;
+
+create table hoge (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from hoge;
+
+\dX
+\dX stts_?
+\dX *hoge
+drop table t1, t2, hoge;
