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

Hi

2017-02-23 12:17 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

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

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

* 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: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 15:10 GMT+01:00 Peter Eisentraut <
peter.eisentraut@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

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

2017-03-10 15:16 GMT+01:00 Stephen Frost <sfrost@snowman.net>:

* 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'

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

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

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).

Pavel

2017-03-10 16:05 GMT+01:00 Peter Eisentraut <
peter.eisentraut@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

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

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.

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

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
david@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

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
/messages/by-id/AANLkTikyaeJ0XdKDzxSvqPE8kaRRTiUQJQHwNJ8ecN2W@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

On Tue, Mar 28, 2017 at 10:12 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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
/messages/by-id/AANLkTikyaeJ0XdKDzxSvqPE8kaRRT
iUQJQHwNJ8ecN2W@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

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
/messages/by-id/AANLkTikyaeJ0XdKDzxSvq
PE8kaRRTiUQJQHwNJ8ecN2W@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

Hi,

On 2017-03-11 13:06:13 +0100, Pavel Stehule wrote:

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

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.

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

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

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

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

Regards

Pavel

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.
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

Hi

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.

Regards

Pavel

On Tue, Sep 19, 2017 at 7:54 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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.

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

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

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

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

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

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

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.

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.

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

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

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

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?

2017-09-21 20:30 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

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?

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

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

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

Hi

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?

Regards

Pavel

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 Sun, Oct 29, 2017 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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

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

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

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:

\d[S+] [ pattern ] [ ( auxiliary sql ) ]

(and similar for the other commands)

The auxiliary SQL could be pretty much anything to allow user-defined
ordering and filtering.

I think parsing the optional auxiliary SQL snippet in parentheses at
the end should be quite straightforward, provided that psql makes no
attempt to actually parse the SQL contained in the parentheses -- it
should just add it to the SQL it sends to the backend (like \copy
does). For example, for \d+, instead of generating

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

we could generate

SELECT schema as "Schema",
name as "Name",
type as "Type",
owner as "Owner",
pg_catalog.pg_size_pretty(size) as "Size",
description as "Description"
FROM (
SELECT n.nspname as schema,
c.relname as name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'table' END as type,
pg_catalog.pg_get_userbyid(c.relowner) as owner,
pg_catalog.pg_table_size(c.oid) as size,
pg_catalog.obj_description(c.oid, 'pg_class') as description
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2
) as t
<auxiliary sql>;

That would allow things like

\d+ (order by size)

which would sort by the numeric size, while displaying the pretty size
in the output.

This would also allow more complex orderings that would be hard to
achieve any other way, such as

\d+ (order by type, schema, size desc)
\dt (order by pg_total_relation_size(name::regclass))

(note the size reported by \d+ is not the total relation size, because
it excludes indexes)

Also, it would allow user-defined WHERE clauses to filter the results
shown, for example:

\d+ (where size > pg_size_bytes('1GB'))
\dv (where pg_relation_is_updatable(name::regclass, true) != 0)

and many more things are possible, without needing to learn any new
syntax, and without needing to keep adding more and more options to
the psql syntax.

Regards,
Dean

On Mon, Nov 13, 2017 at 3:17 PM, Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

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.

+1. While useful in itself, I think it's definitely a dangerous pattern to
go down, as it'll only get worse.

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:

+1 here as well. And anybody who is actually going to need this level of
control definitely will know SQL...

And if one wants to save some "standard patterns", it should be doable to
save the pattern itself in a variable and then use it with something like
"\dt :mysort" and have it expand the normal way there.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Tue, Nov 14, 2017 at 3:26 PM, Magnus Hagander <magnus@hagander.net>
wrote:

On Mon, Nov 13, 2017 at 3:17 PM, Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

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.

+1. While useful in itself, I think it's definitely a dangerous pattern to
go down, as it'll only get worse.

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:

+1 here as well. And anybody who is actually going to need this level of
control definitely will know SQL...

And if one wants to save some "standard patterns", it should be doable to
save the pattern itself in a variable and then use it with something like
"\dt :mysort" and have it expand the normal way there.

+1
I agree, that would look better, especially with "standard patterns" which
could help with too long to type each time SQL snippets.

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

2017-11-27 8:58 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

On Tue, Nov 14, 2017 at 3:26 PM, Magnus Hagander <magnus@hagander.net>
wrote:

On Mon, Nov 13, 2017 at 3:17 PM, Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

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.

+1. While useful in itself, I think it's definitely a dangerous pattern
to go down, as it'll only get worse.

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:

+1 here as well. And anybody who is actually going to need this level of
control definitely will know SQL...

And if one wants to save some "standard patterns", it should be doable to
save the pattern itself in a variable and then use it with something like
"\dt :mysort" and have it expand the normal way there.

+1
I agree, that would look better, especially with "standard patterns" which
could help with too long to type each time SQL snippets.

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

This proposal is first time, when we cannot to detect full semantic from
\xxx command. When user extend query correctly, then it is better than
before, when not it is worse than before.

Regards

Pavel

On Tue, Nov 28, 2017 at 4:18 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

This proposal is first time, when we cannot to detect full semantic from
\xxx command. When user extend query correctly, then it is better than
before, when not it is worse than before.

As the discussion is still going on, I am moving this patch to next CF.
--
Michael

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

2017-12-08 22:49 GMT+01:00 Alexander Korotkov <a.korotkov@postgrespro.ru>:

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?

Because you don't know the filter and sort clause (it can be generic), you
don't know, if you should to calculate or not the size. Or there should be
rule, so filter, order must be limited to displayed columns.

Regards

Pavel

On Thu, Nov 2, 2017 at 12:44 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am sending updated patch with some basic doc

Hi Pavel,

I am not sure what the status of this patch is, but FYI:

startup.c: In function ‘main’:
startup.c:284:3: error: too few arguments to function ‘listAllDbs’
success = listAllDbs(NULL, false);
^
In file included from startup.c:21:0:
describe.h:68:13: note: declared here
extern bool listAllDbs(const char *pattern, bool verbose, sortby_type
sortby, bool sort_desc);
^

--
Thomas Munro
http://www.enterprisedb.com

Hi

2018-01-12 2:35 GMT+01:00 Thomas Munro <thomas.munro@enterprisedb.com>:

On Thu, Nov 2, 2017 at 12:44 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

I am sending updated patch with some basic doc

Hi Pavel,

I am not sure what the status of this patch is, but FYI:

startup.c: In function ‘main’:
startup.c:284:3: error: too few arguments to function ‘listAllDbs’
success = listAllDbs(NULL, false);
^
In file included from startup.c:21:0:
describe.h:68:13: note: declared here
extern bool listAllDbs(const char *pattern, bool verbose, sortby_type
sortby, bool sort_desc);
^

There are no agreement about optimal form of this feature - so I'll remove
this patch from commitfest.

Regards

Pavel