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