Hi

2017-09-22 21:31 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:

>
>
> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to