Thread: proposal - patch: psql - sort_by_size

Hello Pavel,

> \set SORT_BY_SIZE on
> \dt -- sorted by schema, name (size is not calculated and is not visible)
> \dt+ -- sorted by size

Patch applies cleanly, compiles, runs. "make check" ok. doc build ok.

There are no tests. Some infrastructure should be in place so that such
features can be tested, eg so psql-specific TAP tests. ISTM that there was
a patch submitted for that, but I cannot find it:-( Maybe it is combined
with some other patch in the CF.

I agree that the simpler the better for such a feature.

ISTM that the fact that the option is ignored on \dt is a little bit
annoying. It means that \dt and \dt+ would not show their results in the
same order. I understand that the point is to avoid the cost of computing
the sizes, but if the user asked for it, should it be done anyway?

I'm wondering whether it would make sense to have a slightly more generic
interface allowing for more values, eg:

  \set DESCRIPTION_SORT "name"
  \set DESCRIPTION_SORT "size"

Well, possibly this is a bad idea, so it is really a question.

+   Setting this variable to <literal>on</literal> causes so results of
+   <literal>\d*</literal> commands will be sorted by size, when size
+   is displayed.

Maybe the simpler: "Setting this variable on sorts \d* outputs by size,
when size is displayed."

ISTM that the documentation is more generic than reality. Does it work
with \db+? It seems to work with \dm+.

On equality, ISTM it it should sort by name as a secondary criterion.

I tested a few cases, although not partitioned tables.

--
Fabien.

so 29. 6. 2019 v 9:32 odesílatel Fabien COELHO <coelho@cri.ensmp.fr> napsal:

Hello Pavel,

> \set SORT_BY_SIZE on
> \dt -- sorted by schema, name (size is not calculated and is not visible)
> \dt+ -- sorted by size

Patch applies cleanly, compiles, runs. "make check" ok. doc build ok.

There are no tests. Some infrastructure should be in place so that such
features can be tested, eg so psql-specific TAP tests. ISTM that there was
a patch submitted for that, but I cannot find it:-( Maybe it is combined
with some other patch in the CF.

It is not possible - the size of relations is not stable (can be different on some platforms), and because showing the size is base of this patch, I cannot to write tests. Maybe only only set/unset of variable.

 

I agree that the simpler the better for such a feature.

ISTM that the fact that the option is ignored on \dt is a little bit
annoying. It means that \dt and \dt+ would not show their results in the
same order. I understand that the point is to avoid the cost of computing
the sizes, but if the user asked for it, should it be done anyway?

It was one objection against some previous patches. In this moment I don't see any wrong on different order between \dt and \dt+. When column "size" will be displayed, then ordering of report will be clean.

I am not strongly against this - implementation of support SORT_BY_SIZE for non verbose mode is +/- few lines more. But now (and it is just my opinion and filing, nothing more), I think so sorting reports by invisible columns can be messy. But if somebody will have strong different option on this point, I am able to accept it. Both variants can have some sense, and some benefits - both variants are consistent with some rules (but cannot be together).

I'm wondering whether it would make sense to have a slightly more generic
interface allowing for more values, eg:

  \set DESCRIPTION_SORT "name"
  \set DESCRIPTION_SORT "size"

Well, possibly this is a bad idea, so it is really a question.

We was at this point already :). If you introduce this, then you have to support combinations schema_name, name_schema, size, schema_size, ...

My goal is implementation of most common missing alternative into psql - but I would not to do too generic implementation - it needs more complex design (and UI), and I don't think so people use it. SORT_BY_SIZE (on/off) looks simply, and because (if will not be changed) it has not impact on non verbose mode, then it can be active permanently (and if not, it is not mental hard work to set it).

I think so more generic solution needs interactive UI. Now I working on vertical cursor support for pspg https://github.com/okbob/pspg. Next step will be sort by column under vertical cursor. So, I hope, it can be good enough for simply sorting by any column of report (but to be user friendly, it needs interactive UI). Because not everywhere is pspg installed, I would to push some simple solution (I prefer simplicity against generic) to psql.

+   Setting this variable to <literal>on</literal> causes so results of
+   <literal>\d*</literal> commands will be sorted by size, when size
+   is displayed.

Maybe the simpler: "Setting this variable on sorts \d* outputs by size,
when size is displayed."

ISTM that the documentation is more generic than reality. Does it work
with \db+? It seems to work with \dm+.

On equality, ISTM it it should sort by name as a secondary criterion.

I tested a few cases, although not partitioned tables.

Thank you - I support now relations (tables, indexes, ), databases, and tablespaces. The column size is displayed  for data types report, but I am not sure about any benefit in this case.

Regards

Pavel

--
Fabien.

On Sun, Jun 30, 2019 at 8:48 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
> I used this text in today patch

Hi Pavel,

Could you please post a rebased patch?

Thanks,

--
Thomas Munro
https://enterprisedb.com

Hello Pavel,

> rebased patch attached

I prefer patches with a number rather than a date, if possible. For one
thing, there may be several updates in one day.

About this version (20180708, probably v3): applies cleanly, compiles,
make check ok, doc build ok. No tests.

It works for me on a few manual tests against a 11.4 server.

Documentation: if you say "\d*+", then it already applies to \db+ and
\dP+, so why listing them? Otherwise, state all commands or make it work
on all commands that have a size?

About the text:
   - remove , before "sorts"
   - ... outputs by decreasing size, when size is displayed.
   - add: When size is not displayed, the output is sorted by names.

I still think that the object name should be kept as a secondary sort
criterion, in case of size equality, so that the output is deterministic.
Having plenty of objects of the same size out of alphabetical order looks
very strange.

I still do not like much the boolean approach. I understand that the name
approach has been rejected, and I can understand why.

I've been thinking about another more generic interface, that I'm putting
here for discussion, I do not claim that it is a good idea. Probably could
fall under "over engineering", but it might not be much harder to
implement, and it solves a few potential problems.

The idea is to add an option to \d commands, such as "\echo -n":

   \dt+ [-o 1d,2a] ...

meaning do the \dt+, order by column 1 descending, column 2 ascending.
With this there would be no need for a special variable nor other
extensions to specify some ordering, whatever the user wishes.

Maybe it could be "\dt+ [-o '1 DESC, 2 ASC'] ..." so that the string
is roughly used as an ORDER BY specification by the query, but it would be
longer to specify.

It also solves the issue that if someone wants another sorting order we
would end with competing boolean variables such as SORT_BY_SIZE,
SORT_BY_TYPE, SORT_BY_SCHEMA, which would be pretty unpractical. The
boolean approach works for *one* sorting extension and breaks at the next
extension.

Also, the boolean does not say that it is a descending order. I could be
interested in looking at the small tables.

Another benefit for me is that I do not like much variables with side
effects, whereas with an explicit syntax there would be no such thing, the
user has what was asked for. Ok, psql is full of them, but I cannot say I
like it for that.

The approach could be extended to specify a limit, eg \dt -l 10 would
add a LIMIT 10 on the query.

Also, the implementation could be high enough so that the description
handlers would not have to deal with it individually, it could return
the query which would then be completed with SORT/LIMIT clauses before
being executed, possibly with a default order if none is specified.

--
Fabien.

Hello Pavel,

> rebased patch attached

I prefer patches with a number rather than a date, if possible. For one
thing, there may be several updates in one day.

About this version (20180708, probably v3): applies cleanly, compiles,
make check ok, doc build ok. No tests.

It works for me on a few manual tests against a 11.4 server.

Documentation: if you say "\d*+", then it already applies to \db+ and
\dP+, so why listing them? Otherwise, state all commands or make it work
on all commands that have a size?

About the text:
   - remove , before "sorts"
   - ... outputs by decreasing size, when size is displayed.
   - add: When size is not displayed, the output is sorted by names.

I still think that the object name should be kept as a secondary sort
criterion, in case of size equality, so that the output is deterministic.
Having plenty of objects of the same size out of alphabetical order looks
very strange.

I still do not like much the boolean approach. I understand that the name
approach has been rejected, and I can understand why.

I've been thinking about another more generic interface, that I'm putting
here for discussion, I do not claim that it is a good idea. Probably could
fall under "over engineering", but it might not be much harder to
implement, and it solves a few potential problems.

The idea is to add an option to \d commands, such as "\echo -n":

   \dt+ [-o 1d,2a] ...

meaning do the \dt+, order by column 1 descending, column 2 ascending.
With this there would be no need for a special variable nor other
extensions to specify some ordering, whatever the user wishes.

Maybe it could be "\dt+ [-o '1 DESC, 2 ASC'] ..." so that the string
is roughly used as an ORDER BY specification by the query, but it would be
longer to specify.

It also solves the issue that if someone wants another sorting order we
would end with competing boolean variables such as SORT_BY_SIZE,
SORT_BY_TYPE, SORT_BY_SCHEMA, which would be pretty unpractical. The
boolean approach works for *one* sorting extension and breaks at the next
extension.

Also, the boolean does not say that it is a descending order. I could be
interested in looking at the small tables.

Another benefit for me is that I do not like much variables with side
effects, whereas with an explicit syntax there would be no such thing, the
user has what was asked for. Ok, psql is full of them, but I cannot say I
like it for that.

The approach could be extended to specify a limit, eg \dt -l 10 would
add a LIMIT 10 on the query.

Also, the implementation could be high enough so that the description
handlers would not have to deal with it individually, it could return
the query which would then be completed with SORT/LIMIT clauses before
being executed, possibly with a default order if none is specified.

--
Fabien.

Hi

I returned to possibility to sort output of \d* and \l by size. There was more a experiments in this area, but without success. Last patch was example of over engineering, and now, I try to implement this feature simply how it is possible. I don't think so we need too complex solution - if somebody needs specific report, then it is not hard to run psql with "-E" option, get and modify used query (and use a power of SQL). But displaying databases objects sorted by size is very common case.

This proposal is based on new psql variable "SORT_BY_SIZE". This variable will be off by default. The value of this variable is used only in verbose mode (when the size is displayed - I don't see any benefit sort of size without showing size). Usage is very simple and implementation too:

\dt -- sorted by schema, name

\dt+ -- still sorted  by schema, name

\set SORT_BY_SIZE on

\dt -- sorted by schema, name (size is not calculated and is not visible)

\dt+ -- sorted by size

\dt+ public.* -- sorted by size from schema public

Comments, notes?

Regards

Pavel

On 2019-Jul-31, Rafia Sabih wrote:

> I had a look at this patch, seems like a useful thing to have.

So the two initial questions for this patch are

1. Is this a feature we want?
2. Is the user interface correct?

I think the feature is useful, and Rafia also stated as much.  Therefore
ISTM we're okay on that front.

As for the UI, Fabien thinks the patch adopts one that's far too
simplistic, and I agree.  Fabien has proposed a number of different UIs,
but doesn't seem convinced of any of them.  One of them was to have
"options" in the command,
 \dt+ [-o 1d,2a]

Another idea is to use variables in a more general form.  So instead of
Pavel's proposal of SORT_BY_SIZE=on we could do something like
SORT_BY=[list]
where the list after the equal sign consists of predetermined elements
(say SIZE, NAME, SCHEMA and so on) and indicates a specific column to
sort by.  This is less succint than Fabien's idea, and in particular you
can't specify it in the command itself but have to set the variable
beforehand instead.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

čt 12. 9. 2019 v 0:01 odesílatel Alvaro Herrera from 2ndQuadrant <alvherre@alvh.no-ip.org> napsal:

On 2019-Jul-31, Rafia Sabih wrote:

> I had a look at this patch, seems like a useful thing to have.

So the two initial questions for this patch are

1. Is this a feature we want?
2. Is the user interface correct?

I think the feature is useful, and Rafia also stated as much.  Therefore
ISTM we're okay on that front.

As for the UI, Fabien thinks the patch adopts one that's far too
simplistic, and I agree.  Fabien has proposed a number of different UIs,
but doesn't seem convinced of any of them.  One of them was to have
"options" in the command,
 \dt+ [-o 1d,2a]

Another idea is to use variables in a more general form.  So instead of
Pavel's proposal of SORT_BY_SIZE=on we could do something like
SORT_BY=[list]
where the list after the equal sign consists of predetermined elements
(say SIZE, NAME, SCHEMA and so on) and indicates a specific column to
sort by.  This is less succint than Fabien's idea, and in particular you
can't specify it in the command itself but have to set the variable
beforehand instead.

for more generic design probably you need redesign psql report systems. You cannot to use just ORDER BY 1,2 on some columns, but you need to produce (and later hide) some content (for size).

So it can be unfunny complex patch. I finished sort inside pspg and I it looks to be better solution, than increase complexity (and less maintainability (due support old releases)).

Regards

Pavel

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services