Hi
po 8. 7. 2019 v 6:12 odesÃlatel Thomas Munro <[email protected]>
napsal:
> On Sun, Jun 30, 2019 at 8:48 PM Pavel Stehule <[email protected]>
> wrote:
> > I used this text in today patch
>
> Hi Pavel,
>
> Could you please post a rebased patch?
>
rebased patch attached
Regards
Pavel
>
> Thanks,
>
> --
> Thomas Munro
> https://enterprisedb.com
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7789fc6177..96fc4f489a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3973,6 +3973,17 @@ bar
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><varname>SORT_BY_SIZE</varname></term>
+ <listitem>
+ <para>
+ Setting this variable to <literal>on</literal>, sorts
+ <literal>\d*+</literal>, <literal>\db+</literal>, <literal>\l+</literal>
+ and <literal>\dP*+</literal> outputs by size (when size is displayed).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><varname>SQLSTATE</varname></term>
<listitem>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8b4cd53631..ae79cbb312 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -223,6 +223,7 @@ describeTablespaces(const char *pattern, bool verbose)
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
+ const char *sizefunc = NULL;
if (pset.sversion < 80000)
{
@@ -265,9 +266,12 @@ describeTablespaces(const char *pattern, bool verbose)
gettext_noop("Options"));
if (verbose && pset.sversion >= 90200)
+ {
appendPQExpBuffer(&buf,
",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
gettext_noop("Size"));
+ sizefunc = "pg_catalog.pg_tablespace_size(oid)";
+ }
if (verbose && pset.sversion >= 80200)
appendPQExpBuffer(&buf,
@@ -281,7 +285,10 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (pset.sort_by_size && sizefunc)
+ appendPQExpBuffer(&buf, "ORDER BY %s DESC;", sizefunc);
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -863,6 +870,7 @@ listAllDbs(const char *pattern, bool verbose)
PGresult *res;
PQExpBufferData buf;
printQueryOpt myopt = pset.popt;
+ const char *sizefunc = NULL;
initPQExpBuffer(&buf);
@@ -882,12 +890,15 @@ listAllDbs(const char *pattern, bool verbose)
appendPQExpBufferStr(&buf, " ");
printACLColumn(&buf, "d.datacl");
if (verbose && pset.sversion >= 80200)
+ {
appendPQExpBuffer(&buf,
",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
" THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
" ELSE 'No Access'\n"
" END as \"%s\"",
gettext_noop("Size"));
+ sizefunc = "pg_catalog.pg_database_size(d.datname)";
+ }
if (verbose && pset.sversion >= 80000)
appendPQExpBuffer(&buf,
",\n t.spcname as \"%s\"",
@@ -906,7 +917,10 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (pset.sort_by_size && sizefunc)
+ appendPQExpBuffer(&buf, "ORDER BY %s DESC;", sizefunc);
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3628,6 +3642,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
bool showMatViews = strchr(tabtypes, 'm') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showForeign = strchr(tabtypes, 'E') != NULL;
+ const char *sizefunc = NULL;
PQExpBufferData buf;
PGresult *res;
@@ -3711,13 +3726,19 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
* size of a table, including FSM, VM and TOAST tables.
*/
if (pset.sversion >= 90000)
+ {
appendPQExpBuffer(&buf,
",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
gettext_noop("Size"));
+ sizefunc = "pg_catalog.pg_table_size(c.oid)";
+ }
else if (pset.sversion >= 80100)
+ {
appendPQExpBuffer(&buf,
",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
gettext_noop("Size"));
+ sizefunc = "pg_catalog.pg_relation_size(c.oid)";
+ }
appendPQExpBuffer(&buf,
",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
@@ -3770,7 +3791,10 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
- appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ if (pset.sort_by_size && sizefunc)
+ appendPQExpBuffer(&buf, "ORDER BY %s DESC;", sizefunc);
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -3946,6 +3970,7 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
" JOIN d ON i.inhparent = d.oid)\n"
" SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
"d.oid))) AS tps,\n"
+ " sum(pg_catalog.pg_table_size(d.oid)) AS rps,\n"
" pg_catalog.pg_size_pretty(sum("
"\n CASE WHEN d.level = 1"
" THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n"
@@ -3961,6 +3986,7 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
" ELSE 0 END)) AS dps"
",\n pg_catalog.pg_size_pretty(sum("
"pg_catalog.pg_table_size(ppt.relid))) AS tps"
+ ",\n sum(pg_catalog.pg_table_size(ppt.relid)) AS rps"
"\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
}
}
@@ -3993,9 +4019,14 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
- appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s%s\"Name\";",
- mixed_output ? "\"Type\" DESC, " : "",
- showNested || pattern ? "\"Parent name\" NULLS FIRST, " : "");
+ if (pset.sort_by_size && verbose)
+ appendPQExpBuffer(&buf, "ORDER BY %s%srps DESC",
+ mixed_output ? "\"Type\" DESC, " : "",
+ showNested || pattern ? "\"Parent name\" NULLS FIRST, " : "");
+ else
+ appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s%s\"Name\";",
+ mixed_output ? "\"Type\" DESC, " : "",
+ showNested || pattern ? "\"Parent name\" NULLS FIRST, " : "");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index d9b982d3a0..2037ba352f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -342,7 +342,7 @@ helpVariables(unsigned short int pager)
* Windows builds currently print one more line than non-Windows builds.
* Using the larger number is fine.
*/
- output = PageOutput(158, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(160, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -411,6 +411,8 @@ helpVariables(unsigned short int pager)
" if set, end of line terminates SQL commands (same as -S option)\n"));
fprintf(output, _(" SINGLESTEP\n"
" single-step mode (same as -s option)\n"));
+ fprintf(output, _(" SORT_BY_SIZE\n"
+ " if set, outputs of \\d*+, \\db+, \\l+, \\dP*+ are sorted by size\n"));
fprintf(output, _(" SQLSTATE\n"
" SQLSTATE of last query, or \"00000\" if no error\n"));
fprintf(output, _(" USER\n"
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 5be5091f0e..dc0033652b 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -127,6 +127,7 @@ typedef struct _psqlSettings
bool quiet;
bool singleline;
bool singlestep;
+ bool sort_by_size;
bool hide_tableam;
int fetch_count;
int histsize;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 4730c73396..974c965875 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -884,6 +884,12 @@ singlestep_hook(const char *newval)
return ParseVariableBool(newval, "SINGLESTEP", &pset.singlestep);
}
+static bool
+sort_by_size_hook(const char *newval)
+{
+ return ParseVariableBool(newval, "SORT_BY_SIZE", &pset.sort_by_size);
+}
+
static char *
fetch_count_substitute_hook(char *newval)
{
@@ -1184,6 +1190,9 @@ EstablishVariableSpace(void)
SetVariableHooks(pset.vars, "SINGLESTEP",
bool_substitute_hook,
singlestep_hook);
+ SetVariableHooks(pset.vars, "SORT_BY_SIZE",
+ bool_substitute_hook,
+ sort_by_size_hook);
SetVariableHooks(pset.vars, "FETCH_COUNT",
fetch_count_substitute_hook,
fetch_count_hook);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9009b05e12..66d34be43b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3698,7 +3698,7 @@ psql_completion(const char *text, int start, int end)
else if (TailMatchesCS("\\set", MatchAny))
{
if (TailMatchesCS("AUTOCOMMIT|ON_ERROR_STOP|QUIET|"
- "SINGLELINE|SINGLESTEP"))
+ "SINGLELINE|SINGLESTEP|SORT_BY_SIZE"))
COMPLETE_WITH_CS("on", "off");
else if (TailMatchesCS("COMP_KEYWORD_CASE"))
COMPLETE_WITH_CS("lower", "upper",
