Thread
-
[PATCH 1/3] Add \dX command on psql
Tatsuro Yamada <yamatattsu@gmail.com> — 2020-11-09T02:25:14Z
--- 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.26.2 --------------999B33330AF227EEF048FB79 Content-Type: text/x-patch; charset=UTF-8; name="0002-Add-regression-test-of-dX-to-stats_ext.sql-20201115.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0002-Add-regression-test-of-dX-to-stats_ext.sql-20201115.pat"; filename*1="ch"