Hi
so 29. 6. 2019 v 10:19 odesÃlatel Pavel Stehule <[email protected]>
napsal:
>
>
> so 29. 6. 2019 v 9:32 odesÃlatel Fabien COELHO <[email protected]>
> napsal:
>
>>
>> Hello Pavel,
>>
>> > \set SORT_BY_SIZE on
>> > \dt -- sorted by schema, name (size is not calculated and is not
>> visible)
>> > \dt+ -- sorted by size
>>
>> Patch applies cleanly, compiles, runs. "make check" ok. doc build ok.
>>
>> There are no tests. Some infrastructure should be in place so that such
>> features can be tested, eg so psql-specific TAP tests. ISTM that there
>> was
>> a patch submitted for that, but I cannot find it:-( Maybe it is combined
>> with some other patch in the CF.
>>
>
> It is not possible - the size of relations is not stable (can be different
> on some platforms), and because showing the size is base of this patch, I
> cannot to write tests. Maybe only only set/unset of variable.
>
>
>>
>> I agree that the simpler the better for such a feature.
>>
>> ISTM that the fact that the option is ignored on \dt is a little bit
>> annoying. It means that \dt and \dt+ would not show their results in the
>> same order. I understand that the point is to avoid the cost of computing
>> the sizes, but if the user asked for it, should it be done anyway?
>>
>
> It was one objection against some previous patches. In this moment I don't
> see any wrong on different order between \dt and \dt+. When column "size"
> will be displayed, then ordering of report will be clean.
>
> I am not strongly against this - implementation of support SORT_BY_SIZE
> for non verbose mode is +/- few lines more. But now (and it is just my
> opinion and filing, nothing more), I think so sorting reports by invisible
> columns can be messy. But if somebody will have strong different option on
> this point, I am able to accept it. Both variants can have some sense, and
> some benefits - both variants are consistent with some rules (but cannot be
> together).
>
>
>> I'm wondering whether it would make sense to have a slightly more generic
>> interface allowing for more values, eg:
>>
>> \set DESCRIPTION_SORT "name"
>> \set DESCRIPTION_SORT "size"
>>
>> Well, possibly this is a bad idea, so it is really a question.
>>
>
> We was at this point already :). If you introduce this, then you have to
> support combinations schema_name, name_schema, size, schema_size, ...
>
> My goal is implementation of most common missing alternative into psql -
> but I would not to do too generic implementation - it needs more complex
> design (and UI), and I don't think so people use it. SORT_BY_SIZE (on/off)
> looks simply, and because (if will not be changed) it has not impact on non
> verbose mode, then it can be active permanently (and if not, it is not
> mental hard work to set it).
>
> I think so more generic solution needs interactive UI. Now I working on
> vertical cursor support for pspg https://github.com/okbob/pspg. Next step
> will be sort by column under vertical cursor. So, I hope, it can be good
> enough for simply sorting by any column of report (but to be user friendly,
> it needs interactive UI). Because not everywhere is pspg installed, I would
> to push some simple solution (I prefer simplicity against generic) to psql.
>
>
>
>>
>> + Setting this variable to <literal>on</literal> causes so results of
>> + <literal>\d*</literal> commands will be sorted by size, when size
>> + is displayed.
>>
>> Maybe the simpler: "Setting this variable on sorts \d* outputs by size,
>> when size is displayed."
>>
>
I used this text in today patch
Regards
Pavel
>
>> ISTM that the documentation is more generic than reality. Does it work
>> with \db+? It seems to work with \dm+.
>>
>> On equality, ISTM it it should sort by name as a secondary criterion.
>>
>> I tested a few cases, although not partitioned tables.
>>
>
> Thank you - I support now relations (tables, indexes, ), databases, and
> tablespaces. The column size is displayed for data types report, but I am
> not sure about any benefit in this case.
>
> Regards
>
> Pavel
>
>
>> --
>> Fabien.
>>
>>
>>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c6c20de243..b04e93c1f2 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3959,6 +3959,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 97167d2c4b..be149391a1 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;
@@ -3685,13 +3700,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\"",
@@ -3744,7 +3765,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);
@@ -3920,6 +3944,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"
@@ -3935,6 +3960,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");
}
}
@@ -3967,9 +3993,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 5fb1baadc5..f9b4e85cbf 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -341,7 +341,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"));
@@ -410,6 +410,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 bb9921a894..dc56188248 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -883,6 +883,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)
{
@@ -1183,6 +1189,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 7dcf342413..edea5fae0d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3682,7 +3682,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",
