Re: proposal - patch: psql - sort_by_size

On Fri, Jun 28, 2019 at 10:13 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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
>
>
One oddity about pg_relation_size and pg_table_size is that they can be
easily blocked by user activity. In fact it happens to us often in
reporting environments and we have instead written different versions of
them that avoid the lock contention and still give "close enough" results.

This blocking could result in quite unexpected behavior, that someone uses
your proposed command and it never returns. Has that been considered as a
reality at least to be documented?

Thanks,
Jeremy