Hi
pá 12. 7. 2019 v 15:10 odesílatel Fabien COELHO <[email protected]>
napsal:
>
> Hello Pavel,
>
> > rebased patch attached
>
> I prefer patches with a number rather than a date, if possible. For one
> thing, there may be several updates in one day.
>
> About this version (20180708, probably v3): applies cleanly, compiles,
> make check ok, doc build ok. No tests.
>
attached version 4
> It works for me on a few manual tests against a 11.4 server.
>
> Documentation: if you say "\d*+", then it already applies to \db+ and
> \dP+, so why listing them? Otherwise, state all commands or make it work
> on all commands that have a size?
>
> About the text:
> - remove , before "sorts"
> - ... outputs by decreasing size, when size is displayed.
> - add: When size is not displayed, the output is sorted by names.
>
fixed
> I still think that the object name should be kept as a secondary sort
> criterion, in case of size equality, so that the output is deterministic.
> Having plenty of objects of the same size out of alphabetical order looks
> very strange.
>
fixed
Regards
Pavel
>
> I still do not like much the boolean approach. I understand that the name
> approach has been rejected, and I can understand why.
>
> I've been thinking about another more generic interface, that I'm putting
> here for discussion, I do not claim that it is a good idea. Probably could
> fall under "over engineering", but it might not be much harder to
> implement, and it solves a few potential problems.
>
> The idea is to add an option to \d commands, such as "\echo -n":
>
> \dt+ [-o 1d,2a] ...
>
> meaning do the \dt+, order by column 1 descending, column 2 ascending.
> With this there would be no need for a special variable nor other
> extensions to specify some ordering, whatever the user wishes.
>
> Maybe it could be "\dt+ [-o '1 DESC, 2 ASC'] ..." so that the string
> is roughly used as an ORDER BY specification by the query, but it would be
> longer to specify.
>
> It also solves the issue that if someone wants another sorting order we
> would end with competing boolean variables such as SORT_BY_SIZE,
> SORT_BY_TYPE, SORT_BY_SCHEMA, which would be pretty unpractical. The
> boolean approach works for *one* sorting extension and breaks at the next
> extension.
>
> Also, the boolean does not say that it is a descending order. I could be
> interested in looking at the small tables.
>
> Another benefit for me is that I do not like much variables with side
> effects, whereas with an explicit syntax there would be no such thing, the
> user has what was asked for. Ok, psql is full of them, but I cannot say I
> like it for that.
>
> The approach could be extended to specify a limit, eg \dt -l 10 would
> add a LIMIT 10 on the query.
>
> Also, the implementation could be high enough so that the description
> handlers would not have to deal with it individually, it could return
> the query which would then be completed with SORT/LIMIT clauses before
> being executed, possibly with a default order if none is specified.
>
> --
> Fabien.
>
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7789fc6177..40aeb8cef0 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>
+ Sorts <literal>\d[bimtv]+</literal> and <literal>\l+</literal>
+ outputs by decreasing size (when size is displayed). When size
+ is not displayed, then output is sorted by name.
+ </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..9a7f02607c 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, 1;", 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, 1;", 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, 1, 2;", 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..fe17316624 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, verbose outputs 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 3f7001fb69..427967be0a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3737,7 +3737,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",
