Hi
I returned to possibility to sort output of \d* and \l by size. There was
more a experiments in this area, but without success. Last patch was
example of over engineering, and now, I try to implement this feature
simply how it is possible. I don't think so we need too complex solution -
if somebody needs specific report, then it is not hard to run psql with
"-E" option, get and modify used query (and use a power of SQL). But
displaying databases objects sorted by size is very common case.
This proposal is based on new psql variable "SORT_BY_SIZE". This variable
will be off by default. The value of this variable is used only in verbose
mode (when the size is displayed - I don't see any benefit sort of size
without showing size). Usage is very simple and implementation too:
\dt -- sorted by schema, name
\dt+ -- still sorted by schema, name
\set SORT_BY_SIZE on
\dt -- sorted by schema, name (size is not calculated and is not visible)
\dt+ -- sorted by size
\dt+ public.* -- sorted by size from schema public
Comments, notes?
Regards
Pavel
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c6c20de243..14fae1abd3 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> causes so results of
+ <literal>\d*</literal> commands will be sorted 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..c4665bca2c 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"
+ " describe reports are sorted by size when size is displayed\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",
