Hi
2017-09-22 21:31 GMT+02:00 Pavel Stehule <[email protected]>:
>
>
> 2017-09-22 21:12 GMT+02:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.
> com>:
>
>> On 9/22/17 09:16, Pavel Stehule wrote:
>> > Example: somebody set SORT_COLUMNS to schema_name value. This is
>> > nonsense for \l command
>> >
>> > Now, I am thinking so more correct and practical design is based on
>> > special mode, activated by variable
>> >
>> > PREFER_SIZE_SORT .. (off, asc, desc)
>> >
>> > This has sense for wide group of commands that can show size. And when
>> > size is not visible, then this option is not active.
>>
>> Maybe this shouldn't be a variable at all. It's not like you'll set
>> this as a global preference. You probably want it for one command only.
>> So a per-command option might make more sense.
>>
>
> Sure, I cannot to know, what users will do. But, when I need to see a size
> of objects, then I prefer the sort by size desc every time. If I need to
> find some object, then I can to use a searching in pager. So in my case,
> this settings will be in psqlrc. In GoodData we used years own
> customization - the order by size was hardcoded and nobody reported me any
> issue.
>
> Alexander proposed some per command option, but current syntax of psql
> commands don't allows some simple parametrization. If it can be user
> friendly, then it should be short. From implementation perspective, it
> should be simply parsed. It should be intuitive too - too much symbols
> together is not good idea.
>
> Maybe some prefix design - but it is not design for common people
> (although these people don't use psql usually)
>
> '\sort size \dt ?
>
> \dt:sort_by_size
> \dt+:sort_by_size ?
>
> I don't see any good design in this direction
>
>
I though about Alexander proposal, and I am thinking so it can be probably
best if we respect psql design. I implemented two command suffixes
(supported only when it has sense) "s" sorted by size and "d" as descent
so list of tables can be sorted with commands:
\dt+sd (in this case, the order is not strict), so command
\dtsd+ is working too (same \disd+ or \di+sd)
These two chars are acceptable. Same principle is used for \l command
\lsd+ or \l+sd
What do you think about it?
Regards
Pavel
> Regards
>
> Pavel
>
>
>
>
>
>
>
>>
>> --
>> Peter Eisentraut http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 041b5e0c87..548b0d8d41 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -349,8 +349,9 @@ exec_command(const char *cmd,
status = exec_command_include(scan_state, active_branch, cmd);
else if (strcmp(cmd, "if") == 0)
status = exec_command_if(scan_state, cstack, query_buf);
- else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
- strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+ else if (strcmp(cmd, "list") == 0 || strcmp(cmd, "list+") == 0 ||
+ strcmp(cmd, "l") == 0 || strncmp(cmd, "l+", 2) == 0 ||
+ strncmp(cmd, "ls", 2) == 0)
status = exec_command_list(scan_state, active_branch, cmd);
else if (strncmp(cmd, "lo_", 3) == 0)
status = exec_command_lo(scan_state, active_branch, cmd);
@@ -702,7 +703,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
char *pattern;
bool show_verbose,
- show_system;
+ show_system,
+ sort_size,
+ sort_desc;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
@@ -711,6 +714,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
show_verbose = strchr(cmd, '+') ? true : false;
show_system = strchr(cmd, 'S') ? true : false;
+ sort_size = false;
+ sort_desc = false;
+
switch (cmd[1])
{
case '\0':
@@ -720,7 +726,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = describeTableDetails(pattern, show_verbose, show_system);
else
/* standard listing of interesting things */
- success = listTables("tvmsE", NULL, show_verbose, show_system);
+ success = listTables("tvmsE", NULL, show_verbose, show_system,
+ false, false);
break;
case 'A':
success = describeAccessMethods(pattern, show_verbose);
@@ -789,12 +796,19 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = describeTypes(pattern, show_verbose, show_system);
break;
case 't':
- case 'v':
case 'm':
case 'i':
+ if (strlen(cmd) >= 2)
+ {
+ sort_size = strchr(&cmd[2], 's') ? true : false;
+ sort_desc = strchr(&cmd[2], 'd') ? true : false;
+ }
+
+ case 'v':
case 's':
case 'E':
- success = listTables(&cmd[1], pattern, show_verbose, show_system);
+ success = listTables(&cmd[1], pattern, show_verbose, show_system,
+ sort_size, sort_desc);
break;
case 'r':
if (cmd[2] == 'd' && cmd[3] == 's')
@@ -1655,13 +1669,17 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
char *pattern;
bool show_verbose;
+ bool sort_size;
+ bool sort_desc;
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
+ sort_size = strchr(cmd, 's') ? true : false;
+ sort_desc = strchr(cmd, 'd') ? true : false;
- success = listAllDbs(pattern, show_verbose);
+ success = listAllDbs(pattern, show_verbose, sort_size, sort_desc);
if (pattern)
free(pattern);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 638275ca2f..e0acb95166 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -782,7 +782,7 @@ describeOperators(const char *pattern, bool verbose, bool showSystem)
* for \l, \list, and -l switch
*/
bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, bool verbose, bool sort_size, bool sort_desc)
{
PGresult *res;
PQExpBufferData buf;
@@ -830,7 +830,27 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (sort_size)
+ {
+ if (pset.sversion < 80200)
+ {
+ char sverbuf[32];
+
+ psql_error("The server (version %s) does not support database size function.\n",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_database_size(d.datname)");
+
+ if (sort_desc)
+ appendPQExpBuffer(&buf, " DESC");
+
+ appendPQExpBuffer(&buf, ", 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3336,7 +3356,8 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
* (any order of the above is fine)
*/
bool
-listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
+listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem,
+ bool sort_size, bool sort_desc)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
@@ -3457,7 +3478,34 @@ 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 (sort_size)
+ {
+ appendPQExpBufferStr(&buf, "ORDER BY ");
+
+ /*
+ * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
+ * size of a table, including FSM, VM and TOAST tables.
+ */
+ if (pset.sversion >= 90000)
+ appendPQExpBuffer(&buf, "pg_catalog.pg_table_size(c.oid)");
+ else if (pset.sversion >= 80100)
+ appendPQExpBuffer(&buf, "pg_catalog.pg_relation_size(c.oid)");
+ else
+ {
+ char sverbuf[32];
+
+ psql_error("The server (version %s) does not support table size function.\n",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ if (sort_desc)
+ appendPQExpBufferStr(&buf, "DESC");
+ appendPQExpBufferStr(&buf, " , 1,2;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 14a5667f3e..1941aab181 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -58,10 +58,11 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, bool verbose, bool sort_size, bool sort_desc);
/* \dt, \di, \ds, \dS, etc. */
-extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
+extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem,
+ bool sort_size, bool sort_desc);
/* \dD */
extern bool listDomains(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a926c40b9b..620b8930a5 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -241,10 +241,10 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
- fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\di[Ssd+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
- fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
+ fprintf(output, _(" \\dm[Ssd+] [PATTERN] list materialized views\n"));
fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n"));
fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n"));
@@ -253,13 +253,13 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dRp[+] [PATTERN] list replication publications\n"));
fprintf(output, _(" \\dRs[+] [PATTERN] list replication subscriptions\n"));
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
- fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
+ fprintf(output, _(" \\dt[Ssd+] [PATTERN] list tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
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, _(" \\dy [PATTERN] list event triggers\n"));
- fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
+ fprintf(output, _(" \\l[sd+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's definition\n"));
fprintf(output, _(" \\sv[+] VIEWNAME show a view's definition\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
