Hi Tomas,
I took a look at this today, and I think the code is ready, but the
regression test needs a bit more work:
Thanks for taking your time. :-D
1) It's probably better to use somewhat more specific names for the
objects, especially when created in public schema. It decreases the
chance of a collision with other tests (which may be hard to notice
because of timing). I suggest we use "stts_" prefix or something like
that, per the attached 0002 patch. (0001 is just the v7 patch)
I agree with your comment. Thanks.
2) The test is failing intermittently because it's executed in parallel
with stats_ext test, which is also creating extended statistics. So
depending on the timing the \dX may list some of the stats_ext stuff.
I'm not sure what to do about this. Either this part needs to be moved
to a separate test executed in a different group, or maybe we should
simply move it to stats_ext.
I thought all tests related to meta-commands exist in psql.sql, but I
realize it's not true. For example, the test of \dRp does not exist in
psql.sql. Therefore, I moved the regression test of \dX to stats_ext.sql
to avoid the test failed in parallel.
Attached patches is following:
- 0001-v8-Add-dX-command-on-psql.patch
- 0002-Add-regression-test-of-dX-to-stats_ext.sql.patch
However, I feel the test of \dX is not elegant, so I'm going to try
creating another one since it would be better to be aware of the context
of existing extended stats tests.
Regards,
Tatsuro Yamada
From 11c088cb43cdb13e445e246d0529d5721cf3bb10 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 10 Nov 2020 11:39:14 +0900
Subject: [PATCH] Add regression test of \dX to stats_ext.sql
---
src/test/regress/expected/stats_ext.out | 94 +++++++++++++++++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 31 +++++++++++
2 files changed, 125 insertions(+)
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 4c3edd213f..0ec4e24960 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1550,6 +1550,100 @@ INSERT INTO tststats.priv_test_tbl
CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
FROM tststats.priv_test_tbl;
ANALYZE tststats.priv_test_tbl;
+-- Check printing info about extended statistics by \dX
+create table stts_t1 (a int, b int);
+create statistics stts_1 (ndistinct) on a, b from stts_t1;
+create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
+create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+create table stts_t2 (a int, b int, c int);
+create statistics stts_4 on b, c from stts_t2;
+create table stts_t3 (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from stts_t3;
+create schema stts_s1;
+create schema stts_s2;
+create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
+create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from
stts_t3;
+insert into stts_t1 select i,i from generate_series(1,100) i;
+analyze stts_t1;
+\dX
+ List of extended statistics
+ Schema | Name | Definition |
N_distinct | Dependencies | Mcv
+----------+------------------------+--------------------------------------+------------+--------------+---------
+ public | func_deps_stat | a, b, c FROM functional_dependencies |
| built |
+ public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays |
| | built
+ public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool |
| | built
+ public | mcv_lists_stats | a, b, d FROM mcv_lists |
| | built
+ public | stts_1 | a, b FROM stts_t1 |
built | |
+ public | stts_2 | a, b FROM stts_t1 |
built | built |
+ public | stts_3 | a, b FROM stts_t1 |
built | built | built
+ public | stts_4 | b, c FROM stts_t2 |
defined | defined | defined
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 |
defined | defined | defined
+ stts_s1 | stts_foo | col1, col2 FROM stts_t3 |
defined | defined | defined
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 |
| defined | defined
+ tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl |
| | built
+(12 rows)
+
+\dX stts_?
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv
+--------+--------+-------------------+------------+--------------+---------
+ public | stts_1 | a, b FROM stts_t1 | built | |
+ public | stts_2 | a, b FROM stts_t1 | built | built |
+ public | stts_3 | a, b FROM stts_t1 | built | built | built
+ public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
+(4 rows)
+
+\dX *stts_hoge
+ List of extended statistics
+ Schema | Name | Definition | N_distinct |
Dependencies | Mcv
+--------+-----------+-------------------------------+------------+--------------+---------
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined
| defined
+(1 row)
+
+\dX+
+ List of extended
statistics
+ Schema | Name | Definition |
N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+----------+------------------------+--------------------------------------+------------+--------------+---------+----------+-----------+------------
+ public | func_deps_stat | a, b, c FROM functional_dependencies |
| built | | | 106 bytes |
+ public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays |
| | built | | | 24 kB
+ public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool |
| | built | | | 386 bytes
+ public | mcv_lists_stats | a, b, d FROM mcv_lists |
| | built | | | 294 bytes
+ public | stts_1 | a, b FROM stts_t1 |
built | | | 13 bytes | |
+ public | stts_2 | a, b FROM stts_t1 |
built | built | | 13 bytes | 40 bytes |
+ public | stts_3 | a, b FROM stts_t1 |
built | built | built | 13 bytes | 40 bytes | 6126 bytes
+ public | stts_4 | b, c FROM stts_t2 |
defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 |
defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+ stts_s1 | stts_foo | col1, col2 FROM stts_t3 |
defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 |
| defined | defined | | 0 bytes | 0 bytes
+ tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl |
| | built | | | 686 bytes
+(12 rows)
+
+\dX+ stts_?
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv |
N_size | D_size | M_size
+--------+--------+-------------------+------------+--------------+---------+----------+----------+------------
+ public | stts_1 | a, b FROM stts_t1 | built | | |
13 bytes | |
+ public | stts_2 | a, b FROM stts_t1 | built | built | |
13 bytes | 40 bytes |
+ public | stts_3 | a, b FROM stts_t1 | built | built | built |
13 bytes | 40 bytes | 6126 bytes
+ public | stts_4 | b, c FROM stts_t2 | defined | defined | defined | 0
bytes | 0 bytes | 0 bytes
+(4 rows)
+
+\dX+ *stts_hoge
+ List of extended statistics
+ Schema | Name | Definition | N_distinct |
Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------+-------------------------------+------------+--------------+---------+---------+---------+---------
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined
| defined | 0 bytes | 0 bytes | 0 bytes
+(1 row)
+
+\dX+ stts_s2.stts_yama
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies |
Mcv | N_size | D_size | M_size
+---------+-----------+-------------------------+------------+--------------+---------+--------+---------+---------
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined |
defined | | 0 bytes | 0 bytes
+(1 row)
+
+drop table stts_t1, stts_t2, stts_t3;
+drop schema stts_s1, stts_s2 cascade;
-- User with no access
CREATE USER regress_stats_user1;
GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 9781e590a3..2b90471a4b 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -833,6 +833,37 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
ANALYZE tststats.priv_test_tbl;
+-- Check printing info about extended statistics by \dX
+create table stts_t1 (a int, b int);
+create statistics stts_1 (ndistinct) on a, b from stts_t1;
+create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
+create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+
+create table stts_t2 (a int, b int, c int);
+create statistics stts_4 on b, c from stts_t2;
+
+create table stts_t3 (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from stts_t3;
+
+create schema stts_s1;
+create schema stts_s2;
+create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
+create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from
stts_t3;
+
+insert into stts_t1 select i,i from generate_series(1,100) i;
+analyze stts_t1;
+
+\dX
+\dX stts_?
+\dX *stts_hoge
+\dX+
+\dX+ stts_?
+\dX+ *stts_hoge
+\dX+ stts_s2.stts_yama
+
+drop table stts_t1, stts_t2, stts_t3;
+drop schema stts_s1, stts_s2 cascade;
+
-- User with no access
CREATE USER regress_stats_user1;
GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
--
2.16.5
From 6de4684532f7cdf5451f4969ab83514fa5bb19d3 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Mon, 9 Nov 2020 11:25:14 +0900
Subject: [PATCH] Add \dX command on psql
---
doc/src/sgml/ref/psql-ref.sgml | 14 ++++++
src/bin/psql/command.c | 3 ++
src/bin/psql/describe.c | 100 +++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 ++
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.c | 4 +-
6 files changed, 124 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 221a967bfe..fd860776af 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1918,6 +1918,20 @@ 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.
+ If <literal>+</literal> is appended to the command name, each extended
statistics
+ is listed with its size.
+ </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 c7a83d5dfc..c6f1653cb7 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -930,6 +930,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,
show_verbose);
+ 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 07d640021c..1807324542 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4397,6 +4397,106 @@ listEventTriggers(const char *pattern, bool verbose)
return true;
}
+/*
+ * \dX
+ *
+ * Briefly describes extended statistics.
+ */
+bool
+listExtendedStats(const char *pattern, bool verbose)
+{
+ 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"
+
"es.stxnamespace::pg_catalog.regnamespace::text AS \"%s\", \n"
+ "es.stxname AS \"%s\", \n"
+ "pg_catalog.format('%%s FROM %%s', \n"
+ " (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
+ " FROM
pg_catalog.unnest(es.stxkeys) s(attnum) \n"
+ " JOIN pg_catalog.pg_attribute a \n"
+ " ON (es.stxrelid = a.attrelid \n"
+ " AND a.attnum = s.attnum \n"
+ " AND NOT a.attisdropped)), \n"
+ "es.stxrelid::regclass) AS \"%s\", \n"
+ "CASE WHEN esd.stxdndistinct IS NOT
NULL THEN 'built' \n"
+ " WHEN 'd' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxddependencies IS
NOT NULL THEN 'built' \n"
+ " WHEN 'f' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxdmcv IS NOT NULL
THEN 'built' \n"
+ " WHEN 'm' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"),
+ gettext_noop("N_distinct"),
+ gettext_noop("Dependencies"),
+ gettext_noop("Mcv"));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(&buf,
+ ",\nCASE WHEN
esd.stxdndistinct IS NOT NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxdndistinct)::bigint) \n"
+ " WHEN 'd' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN
esd.stxddependencies IS NOT NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxddependencies)::bigint) \n"
+ " WHEN 'f' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxdmcv IS NOT
NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxdmcv)::bigint) \n"
+ " WHEN 'm' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\" ",
+ gettext_noop("N_size"),
+ gettext_noop("D_size"),
+ gettext_noop("M_size"));
+ }
+
+ appendPQExpBufferStr(&buf,
+ " \nFROM
pg_catalog.pg_statistic_ext es \n"
+ "LEFT JOIN
pg_catalog.pg_statistic_ext_data esd \n"
+ "ON es.oid = esd.stxoid \n"
+ "INNER JOIN
pg_catalog.pg_class c \n"
+ "ON es.stxrelid = c.oid \n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, false,
+ false, NULL,
+ "stxname", NULL,
+ NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
+
+ 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
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index f0e3ec957c..89b13c3f0c 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, bool verbose);
+
/* \dy */
extern bool listEventTriggers(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index af829282e6..ea249bf96d 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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5238a960f7..30a8ada7d2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1500,7 +1500,7 @@ psql_completion(const char *text, int start, int end)
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl",
"\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi",
"\\dPt",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
- "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
+ "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
"\\f",
@@ -3851,6 +3851,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
else if (TailMatchesCS("\\dx*"))
COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
+ else if (TailMatchesCS("\\dX*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics, NULL);
else if (TailMatchesCS("\\dm*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
else if (TailMatchesCS("\\dE*"))
--
2.16.5
