Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi
In general, this approach looks good for me.
> Regarding current state of patch, I'd like to see new options documented.
> Also, it would be better to replace "bool sort_size" with enum assuming
> there could be other sorting orders in future.
>
I am sending updated patch with some basic doc
Regards
Pavel
>
> --
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e520cdf3ba..7d816fe701 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1303,10 +1303,10 @@ testdb=>
\dE[S+] [ pattern ]
-\di[S+] [ pattern ]
-\dm[S+] [ pattern ]
+\di[Ssd+] [ pattern ]
+\dm[Ssd+] [ pattern ]
\ds[S+] [ pattern ]
-\dt[S+] [ pattern ]
+\dt[Ssd+] [ pattern ]
\dv[S+] [ pattern ]
@@ -1328,6 +1328,13 @@ testdb=>
pattern or the S modifier to include system
objects.
+
+
+When command contains s, then a result is
+sorted by size. When command contains d then
+result is in descend order. \dtsd+ shows list
+of tables sorted by size with descend order.
+
@@ -2253,7 +2260,7 @@ SELECT
-\l[+] or \list[+] [ pattern ]
+\l[sd+] or \list[+] [ pattern ]
List the databases in the server and show their names, owners,
@@ -2265,6 +2272,12 @@ SELECT
(Size information is only available for databases that the current
user can connect to.)
+
+
+If s is used in command name, then the list is
+sorted by size. When d is used there, then result
+is in descend order.
+
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 041b5e0c87..aae88b08b4 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_desc;
+ sortby_type sortby;
/* 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;
+ sortby = SORTBY_SCHEMA_NAME;
+ 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,20 @@ 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)
+{
+ if (strchr(&cmd[2], 's') != NULL)
+ sortby = SORTBY_SIZE;
+ 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,
+ sortby, sort_desc);
break;
case 'r':
if (cmd[2] == 'd' && cmd[3] == 's')
@@ -1655,13 +1670,17 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
char *pattern;
bool show_verbose;
+ bool sort_desc;
+ sortby_type sortby;
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
+ sortby = strchr(cmd, 's') != NULL ? SORTBY_SIZE : SORTBY_NAME;
+ sort_desc = strchr(cmd, 'd') ? true : false;
- success = listAllDbs(pattern, show_verbose);
+ success = listAllDbs(pattern,
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Sun, Oct 29, 2017 at 12:47 AM, Pavel Stehule wrote: > 2017-10-28 23:35 GMT+02:00 Alexander Korotkov : > >> On Sat, Oct 28, 2017 at 3:46 PM, Pavel Stehule >> wrote: >> >>> 2017-09-22 21:31 GMT+02:00 Pavel Stehule : >>> 2017-09-22 21:12 GMT+02:00 Peter Eisentraut < peter.eisentr...@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? >>> >> >> I think \lsd+ command would be another postgres meme :) >> BTW, are you going to provide an ability to sort by name, schema? >> > > It has sense only for tables - probably only \dtn "n" like name > In general, this approach looks good for me. Regarding current state of patch, I'd like to see new options documented. Also, it would be better to replace "bool sort_size" with enum assuming there could be other sorting orders in future. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-10-28 23:35 GMT+02:00 Alexander Korotkov : > On Sat, Oct 28, 2017 at 3:46 PM, Pavel Stehule > wrote: > >> 2017-09-22 21:31 GMT+02:00 Pavel Stehule : >> >>> >>> >>> 2017-09-22 21:12 GMT+02:00 Peter Eisentraut < >>> peter.eisentr...@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? >> > > I think \lsd+ command would be another postgres meme :) > BTW, are you going to provide an ability to sort by name, schema? > It has sense only for tables - probably only \dtn "n" like name > -- > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Sat, Oct 28, 2017 at 3:46 PM, Pavel Stehule wrote: > 2017-09-22 21:31 GMT+02:00 Pavel Stehule : > >> >> >> 2017-09-22 21:12 GMT+02:00 Peter Eisentraut < >> peter.eisentr...@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? > I think \lsd+ command would be another postgres meme :) BTW, are you going to provide an ability to sort by name, schema? -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi
2017-09-22 21:31 GMT+02:00 Pavel Stehule :
>
>
> 2017-09-22 21:12 GMT+02:00 Peter Eisentraut 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_com
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-09-22 21:12 GMT+02:00 Peter Eisentraut < peter.eisentr...@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 Regards Pavel > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
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. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-09-21 20:30 GMT+02:00 Pavel Stehule : > > > 2017-09-21 20:20 GMT+02:00 Peter Eisentraut com>: > >> On 9/21/17 13:54, Pavel Stehule wrote: >> > I see where you are coming from, but there is no association in the >> > existing UI that equates "+" to the word "verbose". I think just >> > removing the verbose prefix and applying the sorting behavior in all >> > cases should be easier to explain and implement. >> > >> > I though about it - but I am not sure if one kind of these variables is >> > practical. >> > >> > if I don't need a size, then sort by schema, name is ok (I didn't need >> > any else ever). With only one kind of these variables, this setting is >> > common - what is not practical. >> >> But you are proposing also to add a variable configuring the sort >> direction. It would be weird that \dX+ observed the sort direction but >> \dX did not. >> > > yes and no. > > schema_name, name_schema or SORT_DIRECTION has sense for both type of > commands. > > size sort has sense only for \dX+ command. > > I am thinking about solution and the most clean I see two distinct > variables: > > SORT_COLUMNS and VERBOSE_SORT_COLUMNS > > when VERBOSE_SORT_COLUMNS will be undefined, then SORT_COLUMNS is used for > \dX+ command too. > > Is it acceptable? > I though more about it, and I am thinking so this direction is not good. 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. What do you think about this proposal? Regards Pavel > > > >> -- >> Peter Eisentraut http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >> > >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-09-21 20:20 GMT+02:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 9/21/17 13:54, Pavel Stehule wrote: > > I see where you are coming from, but there is no association in the > > existing UI that equates "+" to the word "verbose". I think just > > removing the verbose prefix and applying the sorting behavior in all > > cases should be easier to explain and implement. > > > > I though about it - but I am not sure if one kind of these variables is > > practical. > > > > if I don't need a size, then sort by schema, name is ok (I didn't need > > any else ever). With only one kind of these variables, this setting is > > common - what is not practical. > > But you are proposing also to add a variable configuring the sort > direction. It would be weird that \dX+ observed the sort direction but > \dX did not. > yes and no. schema_name, name_schema or SORT_DIRECTION has sense for both type of commands. size sort has sense only for \dX+ command. I am thinking about solution and the most clean I see two distinct variables: SORT_COLUMNS and VERBOSE_SORT_COLUMNS when VERBOSE_SORT_COLUMNS will be undefined, then SORT_COLUMNS is used for \dX+ command too. Is it acceptable? > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 9/21/17 13:54, Pavel Stehule wrote: > I see where you are coming from, but there is no association in the > existing UI that equates "+" to the word "verbose". I think just > removing the verbose prefix and applying the sorting behavior in all > cases should be easier to explain and implement. > > I though about it - but I am not sure if one kind of these variables is > practical. > > if I don't need a size, then sort by schema, name is ok (I didn't need > any else ever). With only one kind of these variables, this setting is > common - what is not practical. But you are proposing also to add a variable configuring the sort direction. It would be weird that \dX+ observed the sort direction but \dX did not. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-09-21 15:30 GMT+02:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 9/21/17 04:27, Pavel Stehule wrote: > > yes. It was designed for + commands only. Can be enhanced to all > > commands - then VERBOSE prefix should be removed - not sure if it is > > necessary. For me interesting different order than default is only in > > verbose mode. > > I see where you are coming from, but there is no association in the > existing UI that equates "+" to the word "verbose". I think just > removing the verbose prefix and applying the sorting behavior in all > cases should be easier to explain and implement. > I though about it - but I am not sure if one kind of these variables is practical. if I don't need a size, then sort by schema, name is ok (I didn't need any else ever). With only one kind of these variables, this setting is common - what is not practical. I need sort by size in verbose mode (where size is visible) in 100% - so it will be saved to psqlrc. And when size will be invisible, then sort by size is not practical, and can be messy (because size is not visible). So I don't think so removing VERBOSE prefix is a good idea - or we should to do different design (have not a idea how) Regards Pavel > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 9/21/17 04:27, Pavel Stehule wrote: > yes. It was designed for + commands only. Can be enhanced to all > commands - then VERBOSE prefix should be removed - not sure if it is > necessary. For me interesting different order than default is only in > verbose mode. I see where you are coming from, but there is no association in the existing UI that equates "+" to the word "verbose". I think just removing the verbose prefix and applying the sorting behavior in all cases should be easier to explain and implement. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-09-21 10:19 GMT+02:00 Alexander Korotkov : > On Thu, Sep 21, 2017 at 1:53 AM, Peter Eisentraut < > peter.eisentr...@2ndquadrant.com> wrote: > >> On 9/8/17 00:13, Pavel Stehule wrote: >> > I am sending rebased patch >> > >> > rebased again + fix obsolete help >> >> Why are the variables called VERBOSE_SORT_* ? What is verbose about them? > > > I assume Pavel called them so, because they are working only for "verbose" > mode of command. I.e. they are working for \dt+ not \dt. > However, in \dt 2 of 3 sorting modes might work: schema_name and > name_schema. Thus, I think it worths enabling these variables for "non > verbose" mode of commands too. > yes. It was designed for + commands only. Can be enhanced to all commands - then VERBOSE prefix should be removed - not sure if it is necessary. For me interesting different order than default is only in verbose mode. > -- > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Thu, Sep 21, 2017 at 1:53 AM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 9/8/17 00:13, Pavel Stehule wrote: > > I am sending rebased patch > > > > rebased again + fix obsolete help > > Why are the variables called VERBOSE_SORT_* ? What is verbose about them? I assume Pavel called them so, because they are working only for "verbose" mode of command. I.e. they are working for \dt+ not \dt. However, in \dt 2 of 3 sorting modes might work: schema_name and name_schema. Thus, I think it worths enabling these variables for "non verbose" mode of commands too. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Thu, Sep 21, 2017 at 1:52 AM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 9/19/17 12:54, Pavel Stehule wrote: > > However, patch misses regression tests covering added functionality. > > > > I am not sure if there are any tests related to output of \dt+ commands > > - there result is platform depend. > > How so? \dt+ reports relation sizes whose are platform depended. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 9/8/17 00:13, Pavel Stehule wrote: > I am sending rebased patch > > rebased again + fix obsolete help Why are the variables called VERBOSE_SORT_* ? What is verbose about them? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 9/19/17 12:54, Pavel Stehule wrote: > However, patch misses regression tests covering added functionality. > > I am not sure if there are any tests related to output of \dt+ commands > - there result is platform depend. How so? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Tue, Sep 19, 2017 at 7:54 PM, Pavel Stehule wrote: > 2017-09-19 16:14 GMT+02:00 Alexander Korotkov : > >> On Fri, Sep 8, 2017 at 7:13 AM, Pavel Stehule >> wrote: >> >>> 2017-08-16 14:06 GMT+02:00 Pavel Stehule : >>> Hi 2017-08-15 4:37 GMT+02:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 3/11/17 07:06, Pavel Stehule wrote: > > I am sending a updated version with separated sort direction in > special > > variable > > This patch also needs a rebase. > I am sending rebased patch >>> >>> rebased again + fix obsolete help >>> >> >> For me, patch applies cleanly, builds and passed regression tests. >> However, patch misses regression tests covering added functionality. >> > > I am not sure if there are any tests related to output of \dt+ commands - > there result is platform depend. > BTW, why isn't order by name_schema available for \dt? If it's available we could at least cover this case by plain regression tests. \dt+ could be covered by TAP tests, but it isn't yet. I think one day we should add them. However, I don't think we should force you to write them in order to push this simple patch. Patch is definitely harmless, i.e. it doesn't affect anybody who doesn't >> use new functionality. >> But I still would prefer ordering to be options of \d* commands while >> psql variables be defaults for those options... >> > > I understand > > a) I don't think so commands like \dt++ (or similar) is good idea - these > commands should be simple how it is possible > I don't particularly like \dt++, but second argument is probably an option. > b) this patch doesn't block any other design - more it opens the door > because the executive part will be implemented and users can have a > experience with with different output sorts - so if people will need more > quick change of result sort, then the work in this area will continue. > OK. As reviewer, I'm not going to block this patch if you see its functionality limited by just psql variables. I think you should add support of name_schema \dt and some regression tests for this case, before I can mark this as "ready for committer". -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi 2017-09-19 16:14 GMT+02:00 Alexander Korotkov : > On Fri, Sep 8, 2017 at 7:13 AM, Pavel Stehule > wrote: > >> 2017-08-16 14:06 GMT+02:00 Pavel Stehule : >> >>> Hi >>> >>> 2017-08-15 4:37 GMT+02:00 Peter Eisentraut < >>> peter.eisentr...@2ndquadrant.com>: >>> On 3/11/17 07:06, Pavel Stehule wrote: > I am sending a updated version with separated sort direction in special > variable This patch also needs a rebase. >>> >>> I am sending rebased patch >>> >> >> rebased again + fix obsolete help >> > > For me, patch applies cleanly, builds and passed regression tests. > However, patch misses regression tests covering added functionality. > I am not sure if there are any tests related to output of \dt+ commands - there result is platform depend. > Patch is definitely harmless, i.e. it doesn't affect anybody who doesn't > use new functionality. > But I still would prefer ordering to be options of \d* commands while psql > variables be defaults for those options... > I understand a) I don't think so commands like \dt++ (or similar) is good idea - these commands should be simple how it is possible b) this patch doesn't block any other design - more it opens the door because the executive part will be implemented and users can have a experience with with different output sorts - so if people will need more quick change of result sort, then the work in this area will continue. Regards Pavel > -- > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Fri, Sep 8, 2017 at 7:13 AM, Pavel Stehule wrote: > 2017-08-16 14:06 GMT+02:00 Pavel Stehule : > >> Hi >> >> 2017-08-15 4:37 GMT+02:00 Peter Eisentraut > com>: >> >>> On 3/11/17 07:06, Pavel Stehule wrote: >>> > I am sending a updated version with separated sort direction in special >>> > variable >>> >>> This patch also needs a rebase. >>> >> >> I am sending rebased patch >> > > rebased again + fix obsolete help > For me, patch applies cleanly, builds and passed regression tests. However, patch misses regression tests covering added functionality. Patch is definitely harmless, i.e. it doesn't affect anybody who doesn't use new functionality. But I still would prefer ordering to be options of \d* commands while psql variables be defaults for those options... -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-08-16 14:06 GMT+02:00 Pavel Stehule :
> Hi
>
> 2017-08-15 4:37 GMT+02:00 Peter Eisentraut com>:
>
>> On 3/11/17 07:06, Pavel Stehule wrote:
>> > I am sending a updated version with separated sort direction in special
>> > variable
>>
>> This patch also needs a rebase.
>>
>
> I am sending rebased patch
>
rebased again + fix obsolete help
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>>
>> --
>> Peter Eisentraut http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 79468a5663..d51c4bf900 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3796,6 +3796,27 @@ bar
+VERBOSE_SORT_COLUMNS
+
+
+This variable can be set to the values schema_name,
+name_schema or size to control the
+order of content of decrible command.
+
+
+
+
+
+VERBOSE_SORT_DIRECTION
+
+
+This variable can be set to the values asc,
+or desc to control the order of content of decrible command.
+
+
+
+
+
VERBOSITY
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6fb9bdd063..3ead55856d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -200,6 +200,8 @@ describeAccessMethods(const char *pattern, bool verbose)
return true;
}
+#define SORT_DIRECTION_STR(v) ((v) == PSQL_SORT_ASC ? "ASC" : "DESC")
+
/*
* \db
* Takes an optional regexp to select particular tablespaces
@@ -268,7 +270,18 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ if (verbose && pset.sversion >= 90200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_tablespace_size(oid) %s, 1;",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -830,7 +843,19 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (verbose && pset.sversion >= 80200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END %s, 1;\n",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3424,7 +3449,26 @@ 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 (verbose && pset.sversion >= 80100)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort_columns == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 9)
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ }
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4d1c0ec3c6..a28fe07aa2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -337,7 +337,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(147, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(151, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -401,6 +401,10 @@ helpVariables(unsigned short int pager)
"the currently connected database user\n"));
fprintf(output, _(" VERBOSITY\n"
"controls verbosity of error reports [default, verbose, terse]\n"));
+ fprintf(output, _(" VERBOSE_SORT_COLUMNS\n"
+ "controls sort of result in verbose mode [schema_name, name_schema, size]\n"));
+ fprintf(output, _(" VERBOSE_SORT_DIRECTION\n"
+ "controls direction of order of result in verbose mode [asc, desc
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi
2017-08-15 4:37 GMT+02:00 Peter Eisentraut :
> On 3/11/17 07:06, Pavel Stehule wrote:
> > I am sending a updated version with separated sort direction in special
> > variable
>
> This patch also needs a rebase.
>
I am sending rebased patch
Regards
Pavel
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c592edac60..9bc391cb39 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3719,6 +3719,27 @@ bar
+VERBOSE_SORT_COLUMNS
+
+
+This variable can be set to the values schema_name,
+name_schema or size to control the
+order of content of decrible command.
+
+
+
+
+
+VERBOSE_SORT_DIRECTION
+
+
+This variable can be set to the values asc,
+or desc to control the order of content of decrible command.
+
+
+
+
+
VERBOSITY
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f6049cc9e5..1b2346d38b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -200,6 +200,8 @@ describeAccessMethods(const char *pattern, bool verbose)
return true;
}
+#define SORT_DIRECTION_STR(v) ((v) == PSQL_SORT_ASC ? "ASC" : "DESC")
+
/*
* \db
* Takes an optional regexp to select particular tablespaces
@@ -268,7 +270,18 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ if (verbose && pset.sversion >= 90200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_tablespace_size(oid) %s, 1;",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -830,7 +843,19 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (verbose && pset.sversion >= 80200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END %s, 1;\n",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3422,7 +3447,26 @@ 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 (verbose && pset.sversion >= 80100)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort_columns == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 9)
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ }
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index b3dbb5946e..abdc6555b6 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -336,7 +336,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(88, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(92, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -373,6 +373,10 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" SINGLESTEP single-step mode (same as -s option)\n"));
fprintf(output, _(" USER the currently connected database user\n"));
fprintf(output, _(" VERBOSITY controls verbosity of error reports [default, verbose, terse]\n"));
+ fprintf(output, _(" VERBOSE_SORT_COLUMNS\n"
+ " sort columns for verbose mode [schema_name, name_schema, size]\n"));
+ fprintf(output, _(" VERBOSE_SORT_DIRECTION\n"
+ " direction of sort of verbose mode [asc, desc]\n"));
fprintf(output,
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 3/11/17 07:06, Pavel Stehule wrote: > I am sending a updated version with separated sort direction in special > variable This patch also needs a rebase. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi, On 2017-03-11 13:06:13 +0100, Pavel Stehule wrote: > 2017-03-10 15:45 GMT+01:00 Alexander Korotkov : > > > On Fri, Mar 10, 2017 at 5:10 PM, Peter Eisentraut < > > peter.eisentr...@2ndquadrant.com> wrote: > > > >> On 2/24/17 16:32, Pavel Stehule wrote: > >> > set EXTENDED_DESCRIBE_SORT size_desc > >> > \dt+ > >> > \l+ > >> > \di+ > >> > > >> > Possible variants: schema_table, table_schema, size_desc, size_asc > >> > >> I can see this being useful, but I think it needs to be organized a > >> little better. > >> > >> Sort key and sort direction should be separate settings. > >> > > > > I agree. > > > > I'm not sure why we need to have separate settings to sort by schema > >> name and table name. > > > > > > I think sorting by schema name, object name makes sense for people, who > > have objects of same name in different schemas. > > > > I am sending a updated version with separated sort direction in special > variable > > There is a question. Has desc direction sense for columns like schema or > table name? > > Using desc, asc for size is natural. But for tablename? I think it's pretty clear that we don't have sufficient agreement on the design, not to speak of an implementation for an agreed upon design, to get this into v10. The patch also has been submitted late in the v10 cycle, and has received attention. I'm therefore moving it to the next commitfest. Regards, Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
> > This proposal was here already - maybe two years ago. The psql command parser doesn't allow any complex syntax - more - the more parameters in one psql commands is hard to remember, hard to read. >>> >>> Could you please provide a link to this discussion. Probably working >>> with multiple parameters in psql commands require some rework, but that's >>> definitely doable. >>> >> >> http://grokbase.com/t/postgresql/pgsql-hackers/137nt5p6s0/ >> proposal-psql-show-longest-tables/oldest >> https://www.postgresql.org/message-id/AANLkTikyaeJ0XdKDzxSvq >> pe8karrtiuqjqhwnj8ec...@mail.gmail.com >> > > I took a look to these threads, but I didn't find place where difficulties > of adding extra arguments to psql commands are pointed. > Could you, please, point particular messages about it? > I am sorry - maybe my memory doesn't serve well Pavel > > -- > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Tue, Mar 28, 2017 at 10:12 AM, Pavel Stehule
wrote:
> 2017-03-27 13:59 GMT+02:00 Alexander Korotkov :
>
>> On Fri, Mar 10, 2017 at 6:06 PM, Pavel Stehule
>> wrote:
>>
>>> 2017-03-10 16:00 GMT+01:00 Alexander Korotkov >> >:
>>>
On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost
wrote:
> * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> > On 2/24/17 16:32, Pavel Stehule wrote:
> > > set EXTENDED_DESCRIBE_SORT size_desc
> > > \dt+
> > > \l+
> > > \di+
> > >
> > > Possible variants: schema_table, table_schema, size_desc,
> size_asc
> >
> > I can see this being useful, but I think it needs to be organized a
> > little better.
> >
> > Sort key and sort direction should be separate settings.
> >
> > I'm not sure why we need to have separate settings to sort by schema
> > name and table name. But if we do, then we should support that for
> all
> > object types. I think maybe that's something we shouldn't get into
> > right now.
> >
> > So I would have one setting for sort key = {name|size} and on for
> sort
> > direction = {asc|desc}.
>
> Perhaps I'm trying to be overly cute here, but why not let the user
> simply provide a bit of SQL to be put at the end of the query?
>
> That is, something like:
>
> \pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>
I think that's the question of usability. After all, one can manually
type corresponding SQL instead of \d* commands. However, it's quite
cumbersome to do this every time.
I found quite useful to being able to switch between different sortings
quickly. For instance, after seeing tables sorted by name, user can
require them sorted by size descending, then sorted by size ascending,
etc...
Therefore, I find user-defined SQL clause to be cumbersome. Even psql
variable itself seems to be cumbersome for me.
I would propose to add sorting as second optional argument to \d*
commands. Any thoughts?
>>>
>>> This proposal was here already - maybe two years ago. The psql command
>>> parser doesn't allow any complex syntax - more - the more parameters in one
>>> psql commands is hard to remember, hard to read.
>>>
>>
>> Could you please provide a link to this discussion. Probably working
>> with multiple parameters in psql commands require some rework, but that's
>> definitely doable.
>>
>
> http://grokbase.com/t/postgresql/pgsql-hackers/
> 137nt5p6s0/proposal-psql-show-longest-tables/oldest
> https://www.postgresql.org/message-id/AANLkTikyaeJ0XdKDzxSvqPE8kaRRT
> iuqjqhwnj8ec...@mail.gmail.com
>
I took a look to these threads, but I didn't find place where difficulties
of adding extra arguments to psql commands are pointed.
Could you, please, point particular messages about it?
--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-27 13:59 GMT+02:00 Alexander Korotkov :
> On Fri, Mar 10, 2017 at 6:06 PM, Pavel Stehule
> wrote:
>
>> 2017-03-10 16:00 GMT+01:00 Alexander Korotkov
>> :
>>
>>> On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost
>>> wrote:
>>>
* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 2/24/17 16:32, Pavel Stehule wrote:
> > set EXTENDED_DESCRIBE_SORT size_desc
> > \dt+
> > \l+
> > \di+
> >
> > Possible variants: schema_table, table_schema, size_desc,
size_asc
>
> I can see this being useful, but I think it needs to be organized a
> little better.
>
> Sort key and sort direction should be separate settings.
>
> I'm not sure why we need to have separate settings to sort by schema
> name and table name. But if we do, then we should support that for
all
> object types. I think maybe that's something we shouldn't get into
> right now.
>
> So I would have one setting for sort key = {name|size} and on for sort
> direction = {asc|desc}.
Perhaps I'm trying to be overly cute here, but why not let the user
simply provide a bit of SQL to be put at the end of the query?
That is, something like:
\pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>>>
>>> I think that's the question of usability. After all, one can manually
>>> type corresponding SQL instead of \d* commands. However, it's quite
>>> cumbersome to do this every time.
>>> I found quite useful to being able to switch between different sortings
>>> quickly. For instance, after seeing tables sorted by name, user can
>>> require them sorted by size descending, then sorted by size ascending,
>>> etc...
>>> Therefore, I find user-defined SQL clause to be cumbersome. Even psql
>>> variable itself seems to be cumbersome for me.
>>> I would propose to add sorting as second optional argument to \d*
>>> commands. Any thoughts?
>>>
>>
>> This proposal was here already - maybe two years ago. The psql command
>> parser doesn't allow any complex syntax - more - the more parameters in one
>> psql commands is hard to remember, hard to read.
>>
>
> Could you please provide a link to this discussion. Probably working with
> multiple parameters in psql commands require some rework, but that's
> definitely doable.
>
http://grokbase.com/t/postgresql/pgsql-hackers/137nt5p6s0/proposal-psql-show-longest-tables/oldest
https://www.postgresql.org/message-id/aanlktikyaej0xdkdzxsvqpe8karrtiuqjqhwnj8ec...@mail.gmail.com
>
>> With my proposal, and patch I would to cover following use case. It is
>> real case. Anytime when we used \dt+ in psql we needed sort by size desc.
>> When we should to see a size, then the top is interesting. This case is not
>> absolute, but very often, so I would to create some simple way, how to do
>> some parametrization (really simple).
>>
>
> We could combine both approaches: add parameters to psql commands and add
> psql DEFAULT_(SORT_COLUMNS|DIRECTION|LIMIT) parameters.
>
It is possible - This moment is my interest concentrated to psql settings -
the unpractical order in \dt+ irritate me :). I understand so it depends on
use-case. I worked in OLAP and still I have lot of customers with
performance incidents - the first task - show most big tables, most big
indexes.
Regards
Pavel
>
> --
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Fri, Mar 10, 2017 at 6:06 PM, Pavel Stehule
wrote:
> 2017-03-10 16:00 GMT+01:00 Alexander Korotkov :
>
>> On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost
>> wrote:
>>
>>> * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
>>> > On 2/24/17 16:32, Pavel Stehule wrote:
>>> > > set EXTENDED_DESCRIBE_SORT size_desc
>>> > > \dt+
>>> > > \l+
>>> > > \di+
>>> > >
>>> > > Possible variants: schema_table, table_schema, size_desc,
>>> size_asc
>>> >
>>> > I can see this being useful, but I think it needs to be organized a
>>> > little better.
>>> >
>>> > Sort key and sort direction should be separate settings.
>>> >
>>> > I'm not sure why we need to have separate settings to sort by schema
>>> > name and table name. But if we do, then we should support that for all
>>> > object types. I think maybe that's something we shouldn't get into
>>> > right now.
>>> >
>>> > So I would have one setting for sort key = {name|size} and on for sort
>>> > direction = {asc|desc}.
>>>
>>> Perhaps I'm trying to be overly cute here, but why not let the user
>>> simply provide a bit of SQL to be put at the end of the query?
>>>
>>> That is, something like:
>>>
>>> \pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>>>
>>
>> I think that's the question of usability. After all, one can manually
>> type corresponding SQL instead of \d* commands. However, it's quite
>> cumbersome to do this every time.
>> I found quite useful to being able to switch between different sortings
>> quickly. For instance, after seeing tables sorted by name, user can
>> require them sorted by size descending, then sorted by size ascending,
>> etc...
>> Therefore, I find user-defined SQL clause to be cumbersome. Even psql
>> variable itself seems to be cumbersome for me.
>> I would propose to add sorting as second optional argument to \d*
>> commands. Any thoughts?
>>
>
> This proposal was here already - maybe two years ago. The psql command
> parser doesn't allow any complex syntax - more - the more parameters in one
> psql commands is hard to remember, hard to read.
>
Could you please provide a link to this discussion. Probably working with
multiple parameters in psql commands require some rework, but that's
definitely doable.
> With my proposal, and patch I would to cover following use case. It is
> real case. Anytime when we used \dt+ in psql we needed sort by size desc.
> When we should to see a size, then the top is interesting. This case is not
> absolute, but very often, so I would to create some simple way, how to do
> some parametrization (really simple).
>
We could combine both approaches: add parameters to psql commands and add
psql DEFAULT_(SORT_COLUMNS|DIRECTION|LIMIT) parameters.
--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
[HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi Alexander, On 3/11/17 7:06 AM, Pavel Stehule wrote: I am sending a updated version with separated sort direction in special variable There is a question. Has desc direction sense for columns like schema or table name? Using desc, asc for size is natural. But for tablename? Do you know when you'll have a chance to review the updated patch? Thanks, -- -David da...@pgmasters.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-10 15:45 GMT+01:00 Alexander Korotkov :
> On Fri, Mar 10, 2017 at 5:10 PM, Peter Eisentraut <
> peter.eisentr...@2ndquadrant.com> wrote:
>
>> On 2/24/17 16:32, Pavel Stehule wrote:
>> > set EXTENDED_DESCRIBE_SORT size_desc
>> > \dt+
>> > \l+
>> > \di+
>> >
>> > Possible variants: schema_table, table_schema, size_desc, size_asc
>>
>> I can see this being useful, but I think it needs to be organized a
>> little better.
>>
>> Sort key and sort direction should be separate settings.
>>
>
> I agree.
>
> I'm not sure why we need to have separate settings to sort by schema
>> name and table name.
>
>
> I think sorting by schema name, object name makes sense for people, who
> have objects of same name in different schemas.
>
I am sending a updated version with separated sort direction in special
variable
There is a question. Has desc direction sense for columns like schema or
table name?
Using desc, asc for size is natural. But for tablename?
Regards
Pavel
>
> --
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 2a9c412020..747db58dd8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3507,6 +3507,27 @@ bar
+VERBOSE_SORT_COLUMNS
+
+
+This variable can be set to the values schema_name,
+name_schema or size to control the
+order of content of decrible command.
+
+
+
+
+
+VERBOSE_SORT_DIRECTION
+
+
+This variable can be set to the values asc,
+or desc to control the order of content of decrible command.
+
+
+
+
+
VERBOSITY
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 61a3e2a848..7ba24ea883 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -197,6 +197,9 @@ describeAccessMethods(const char *pattern, bool verbose)
return true;
}
+#define SORT_DIRECTION_STR(v) ((v) == PSQL_SORT_ASC ? "ASC" : "DESC")
+
+
/* \db
* Takes an optional regexp to select particular tablespaces
*/
@@ -264,7 +267,18 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ if (verbose && pset.sversion >= 90200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_tablespace_size(oid) %s, 1;",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -824,7 +838,19 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (verbose && pset.sversion >= 80200)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END %s, 1;\n",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3295,7 +3321,26 @@ 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 (verbose && pset.sversion >= 80100)
+ {
+ if (pset.verbose_sort_columns == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort_columns == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 9)
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ }
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ba14df0344..1ebe397a85 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -327,7 +327,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(88, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(92, pager ? &(pset.popt.topt)
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-10 16:05 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 3/10/17 09:57, Pavel Stehule wrote: > > PREFERRED_SORT_COLUMNS > > and PREFERRED_SORT_DIRECTION ? > > I think the name "preferred" implies that it will be ignored if it's not > found or something like that, but I don't think that's what you are > implementing. > ok if it will be used only for verbose describe commands , then the name EXTENDED_DESCRIBE_SORT_COLUMNS, and EXTENDED_DESCRIBE_SORT_DIRECTION. Pavel > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-10 16:00 GMT+01:00 Alexander Korotkov :
> On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost wrote:
>
>> * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
>> > On 2/24/17 16:32, Pavel Stehule wrote:
>> > > set EXTENDED_DESCRIBE_SORT size_desc
>> > > \dt+
>> > > \l+
>> > > \di+
>> > >
>> > > Possible variants: schema_table, table_schema, size_desc, size_asc
>> >
>> > I can see this being useful, but I think it needs to be organized a
>> > little better.
>> >
>> > Sort key and sort direction should be separate settings.
>> >
>> > I'm not sure why we need to have separate settings to sort by schema
>> > name and table name. But if we do, then we should support that for all
>> > object types. I think maybe that's something we shouldn't get into
>> > right now.
>> >
>> > So I would have one setting for sort key = {name|size} and on for sort
>> > direction = {asc|desc}.
>>
>> Perhaps I'm trying to be overly cute here, but why not let the user
>> simply provide a bit of SQL to be put at the end of the query?
>>
>> That is, something like:
>>
>> \pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>>
>
> I think that's the question of usability. After all, one can manually
> type corresponding SQL instead of \d* commands. However, it's quite
> cumbersome to do this every time.
> I found quite useful to being able to switch between different sortings
> quickly. For instance, after seeing tables sorted by name, user can
> require them sorted by size descending, then sorted by size ascending,
> etc...
> Therefore, I find user-defined SQL clause to be cumbersome. Even psql
> variable itself seems to be cumbersome for me.
> I would propose to add sorting as second optional argument to \d*
> commands. Any thoughts?
>
This proposal was here already - maybe two years ago. The psql command
parser doesn't allow any complex syntax - more - the more parameters in one
psql commands is hard to remember, hard to read.
With my proposal, and patch I would to cover following use case. It is real
case. Anytime when we used \dt+ in psql we needed sort by size desc. When
we should to see a size, then the top is interesting. This case is not
absolute, but very often, so I would to create some simple way, how to do
some parametrization (really simple).
Pavel
>
> --
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 3/10/17 09:57, Pavel Stehule wrote: > PREFERRED_SORT_COLUMNS > and PREFERRED_SORT_DIRECTION ? I think the name "preferred" implies that it will be ignored if it's not found or something like that, but I don't think that's what you are implementing. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-10 15:16 GMT+01:00 Stephen Frost :
> * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> > On 2/24/17 16:32, Pavel Stehule wrote:
> > > set EXTENDED_DESCRIBE_SORT size_desc
> > > \dt+
> > > \l+
> > > \di+
> > >
> > > Possible variants: schema_table, table_schema, size_desc, size_asc
> >
> > I can see this being useful, but I think it needs to be organized a
> > little better.
> >
> > Sort key and sort direction should be separate settings.
> >
> > I'm not sure why we need to have separate settings to sort by schema
> > name and table name. But if we do, then we should support that for all
> > object types. I think maybe that's something we shouldn't get into
> > right now.
> >
> > So I would have one setting for sort key = {name|size} and on for sort
> > direction = {asc|desc}.
>
> Perhaps I'm trying to be overly cute here, but why not let the user
> simply provide a bit of SQL to be put at the end of the query?
>
> That is, something like:
>
> \pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>
For example - the size is displayed in pretty form - raw form is not
displayed - so simple ORDER BY clause is not possible.
But setting LIMIT is not bad idea - although it is probably much more
complex for implementation.
\pset DESCRIBE_LIMIT 100
\pset EXTENDED_DESCRIBE_LIMIT 100
can be implemented as next step
Regards
Pavel
>
> Thanks!
>
> Stephen
>
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost wrote:
> * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> > On 2/24/17 16:32, Pavel Stehule wrote:
> > > set EXTENDED_DESCRIBE_SORT size_desc
> > > \dt+
> > > \l+
> > > \di+
> > >
> > > Possible variants: schema_table, table_schema, size_desc, size_asc
> >
> > I can see this being useful, but I think it needs to be organized a
> > little better.
> >
> > Sort key and sort direction should be separate settings.
> >
> > I'm not sure why we need to have separate settings to sort by schema
> > name and table name. But if we do, then we should support that for all
> > object types. I think maybe that's something we shouldn't get into
> > right now.
> >
> > So I would have one setting for sort key = {name|size} and on for sort
> > direction = {asc|desc}.
>
> Perhaps I'm trying to be overly cute here, but why not let the user
> simply provide a bit of SQL to be put at the end of the query?
>
> That is, something like:
>
> \pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
>
I think that's the question of usability. After all, one can manually type
corresponding SQL instead of \d* commands. However, it's quite cumbersome
to do this every time.
I found quite useful to being able to switch between different sortings
quickly. For instance, after seeing tables sorted by name, user can
require them sorted by size descending, then sorted by size ascending,
etc...
Therefore, I find user-defined SQL clause to be cumbersome. Even psql
variable itself seems to be cumbersome for me.
I would propose to add sorting as second optional argument to \d*
commands. Any thoughts?
--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
2017-03-10 15:10 GMT+01:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:
> On 2/24/17 16:32, Pavel Stehule wrote:
> > set EXTENDED_DESCRIBE_SORT size_desc
> > \dt+
> > \l+
> > \di+
> >
> > Possible variants: schema_table, table_schema, size_desc, size_asc
>
> I can see this being useful, but I think it needs to be organized a
> little better.
>
> Sort key and sort direction should be separate settings.
>
ok
maybe
PREFERRED_SORT_COLUMNS
and PREFERRED_SORT_DIRECTION ?
>
> I'm not sure why we need to have separate settings to sort by schema
> name and table name. But if we do, then we should support that for all
> object types. I think maybe that's something we shouldn't get into
> right now.
>
It can be useful, when you repeat one table name in more schema - usually,
where schema is related per one customer, project, ...
Regards
Pavel
>
> So I would have one setting for sort key = {name|size} and on for sort
> direction = {asc|desc}.
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On Fri, Mar 10, 2017 at 5:10 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 2/24/17 16:32, Pavel Stehule wrote: > > set EXTENDED_DESCRIBE_SORT size_desc > > \dt+ > > \l+ > > \di+ > > > > Possible variants: schema_table, table_schema, size_desc, size_asc > > I can see this being useful, but I think it needs to be organized a > little better. > > Sort key and sort direction should be separate settings. > I agree. I'm not sure why we need to have separate settings to sort by schema > name and table name. I think sorting by schema name, object name makes sense for people, who have objects of same name in different schemas. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 2/24/17 16:32, Pavel Stehule wrote:
> > set EXTENDED_DESCRIBE_SORT size_desc
> > \dt+
> > \l+
> > \di+
> >
> > Possible variants: schema_table, table_schema, size_desc, size_asc
>
> I can see this being useful, but I think it needs to be organized a
> little better.
>
> Sort key and sort direction should be separate settings.
>
> I'm not sure why we need to have separate settings to sort by schema
> name and table name. But if we do, then we should support that for all
> object types. I think maybe that's something we shouldn't get into
> right now.
>
> So I would have one setting for sort key = {name|size} and on for sort
> direction = {asc|desc}.
Perhaps I'm trying to be overly cute here, but why not let the user
simply provide a bit of SQL to be put at the end of the query?
That is, something like:
\pset EXTENDED_DESCRIBE_ORDER_LIMIT 'ORDER BY 5 DESC LIMIT 10'
Thanks!
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
On 2/24/17 16:32, Pavel Stehule wrote:
> set EXTENDED_DESCRIBE_SORT size_desc
> \dt+
> \l+
> \di+
>
> Possible variants: schema_table, table_schema, size_desc, size_asc
I can see this being useful, but I think it needs to be organized a
little better.
Sort key and sort direction should be separate settings.
I'm not sure why we need to have separate settings to sort by schema
name and table name. But if we do, then we should support that for all
object types. I think maybe that's something we shouldn't get into
right now.
So I would have one setting for sort key = {name|size} and on for sort
direction = {asc|desc}.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Hi
2017-02-23 12:17 GMT+01:00 Pavel Stehule :
> Hi
>
> Currently is not possible to control sort columns for \d* commands.
> Usually schema and table name is used. Really often task is collect the
> most big objects in database. "\dt+, \di+" shows necessary information, but
> not in practical order.
>
> Instead introduction some additional flags to backslash commands, I
> propose a special psql variable that can be used for specification of order
> used when some plus command is used.
>
> some like
>
> set EXTENDED_DESCRIBE_SORT size_desc
> \dt+
> \l+
> \di+
>
> Possible variants: schema_table, table_schema, size_desc, size_asc
>
> Comments, notes?
>
here is a patch
Regards
Pavel
>
> Regards
>
> Pavel
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ae58708aae..b4dfd1f71c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3495,6 +3495,18 @@ bar
+VERBOSE_SORT
+
+
+This variable can be set to the values schema_name,
+name_schema, size_asc, or
+size_desc to control the order of content of
+decrible command.
+
+
+
+
+
VERBOSITY
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e2e4cbcc08..7ae5992b90 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -263,7 +263,18 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ if (verbose && pset.sversion >= 90200)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SIZE_ASC)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_tablespace_size(oid), 1;");
+ else if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_tablespace_size(oid) DESC, 1;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -822,7 +833,21 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (verbose && pset.sversion >= 80200)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SIZE_ASC)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END ASC, 1;\n");
+ else if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END DESC, 1;\n");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3258,7 +3283,29 @@ 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 (verbose && pset.sversion >= 80100)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 9)
+appendPQExpBufferStr(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) ");
+ else
+appendPQExpBufferStr(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) ");
+
+ if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+appendPQExpBufferStr(&buf, "DESC, 1,2;");
+ else
+appendPQExpBufferStr(&buf, "ASC, 1,2;");
+ }
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 3e3cab4941..09c1a49413 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -327,7 +327,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(88, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(90, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -364,6 +364,8 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" SINGLESTEP single-step mode (same as -s option)\n"));
fprintf(output, _(" USER the currently connected database user\n"));
fprintf(output, _(" VERBOSITY controls verbosity of error reports [default, verbose, terse]\n"));
+ fprintf(output, _(" VERBOSE_SORT controls sort of result in verbose mode\n"
+ " [schema_name, name_schema, size
