Thread: [HACKERS] proposal - psql: possibility to specify sort for describe commands,when size is printed

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?

Regards

Pavel

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.

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

* Peter Eisentraut (peter.eisentraut@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'

* Peter Eisentraut (peter.eisentraut@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

On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Peter Eisentraut (peter.eisentraut@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 

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

On Fri, Mar 10, 2017 at 5:10 PM, Peter Eisentraut <peter.eisentraut@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

 

2017-03-10 16:00 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Peter Eisentraut (peter.eisentraut@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).

On Fri, Mar 10, 2017 at 6:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-03-10 16:00 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Peter Eisentraut (peter.eisentraut@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 

 

2017-03-27 13:59 GMT+02:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Fri, Mar 10, 2017 at 6:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-03-10 16:00 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Fri, Mar 10, 2017 at 5:16 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Peter Eisentraut (peter.eisentraut@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/AANLkTikyaeJ0XdKDzxSvqPE8kaRRTiUQJQHwNJ8ecN2W@mail.gmail.com

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/AANLkTikyaeJ0XdKDzxSvqPE8kaRRTiUQJQHwNJ8ecN2W@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 

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

Hi

2017-08-15 4:37 GMT+02:00 Peter Eisentraut <peter.eisentraut@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

Regards

Pavel

 

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

2017-08-16 14:06 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2017-08-15 4:37 GMT+02:00 Peter Eisentraut <peter.eisentraut@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

2017-09-19 16:14 GMT+02:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Fri, Sep 8, 2017 at 7:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-08-16 14:06 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2017-08-15 4:37 GMT+02:00 Peter Eisentraut <peter.eisentraut@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.

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?

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?

On Thu, Sep 21, 2017 at 1:53 AM, Peter Eisentraut <peter.eisentraut@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 

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

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

2017-09-21 20:20 GMT+02:00 Peter Eisentraut <peter.eisentraut@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

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

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

Regards

Pavel

 

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

2017-09-22 21:31 GMT+02:00 Pavel Stehule <pavel.stehule@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?

On Sat, Oct 28, 2017 at 3:46 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-09-22 21:31 GMT+02:00 Pavel Stehule <pavel.stehule@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?

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 

2017-10-28 23:35 GMT+02:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Sat, Oct 28, 2017 at 3:46 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-09-22 21:31 GMT+02:00 Pavel Stehule <pavel.stehule@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?

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

On 28 October 2017 at 13:46, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 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 really hate that syntax. This is going to turn into an
incomprehensible mess, and isn't easily extended to support other
options.

I agree with people who have said they would prefer this to be
available as a per-command option rather than as a variable that you
have to set, but it needs a clearer syntax. I actually like Stephen's
idea of using a user-defined SQL snippet, because that's a familiar
syntax to people, and it avoids adding an ever-increasing number of
options to these commands. Instead, the syntax could simply be:

I though about this design more time. I see following disadvantages

1. we are not able to check all possible variants of extended query. If there will be some custom error, then we will raise pretty ugly error messages,

2. I don't thing so using "Size" as table size in human readable format and "size" as table size in raw format is intuitive, but any change of "Size" can introduce some (less probability compatibility issues),

3. What queries will contains size calculations? It is not cheap - requires AccessShareLock

Hi!

On Mon, Nov 27, 2017 at 10:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I though about this design more time. I see following disadvantages

1. we are not able to check all possible variants of extended query. If there will be some custom error, then we will raise pretty ugly error messages,

 

Yes, that's an inevitable shortcoming.  psql is not backend and can't perform all the required checks on its side... 

2. I don't thing so using "Size" as table size in human readable format and "size" as table size in raw format is intuitive, but any change of "Size" can introduce some (less probability compatibility issues),

Oh, this is surprisingly hard problem which probably have only imperative solution...

3. What queries will contains size calculations? It is not cheap - requires AccessShareLock

 

Sorry, I didn't understand this point.  Yes, size calculation requires locking, but that is already true for \dt+ and \l+.  Why this is disadvantage of proposed approach?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com

The Russian Postgres Company