Re: [patch] Add schema total size to psql \dn+

Le 21/02/2019 à 21:57, Jerry Sievers a écrit :
> Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> writes:
>
>> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>>
>>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> wrote:
>>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> wrote:
>>>>>>> When we want to get total size of all relation in a schema we have to
>>>>>>> execute one of our favorite DBA query. It is quite simple but what
>>>>>>> about displaying schema size when using \dn+ in psql ?
>>>>>>> [...]
>>>>>>> The attached simple patch adds this feature. Is there any cons adding
>>>>>>> this information? The patch tries to be compatible to all PostgreSQL
>>>>>>> version. Let me know if I have missed something.
>>> I have a few comments about the patch.
>>>
>>> You're using pg_class LEFT JOIN pg_namespace while we need INNER JOIN
>>> here AFAICT. Also, you're using pg_relation_size(), so fsm, vm won't
>>> be accounted for. You should also be bypassing the size for 8.0-
>>> servers where there's no pg_*_size() functions.
>>
>> I agree all points. Attached is a new version of the patch that use
>> pg_total_relation_size() and a filter on relkind IN ('r','m','S'), JOIN
>> fixes and no size report before 8.1.
> Beware that those pg_relation_size() functions are going to block in
> cases where existing objects are (for example) in transactionss such
> as...
>
> begin;
> truncate foo;
> big-nasty-reporting-jobs...;
>
> Thus a bare-metal tallying of pg_class.relpages for heap/index/toast,
> along with missing the FSM/VM size could be $preferred.
>
> And/or at least mentioning this caveat in the related manual section :-)

It's true but we already have this caveats with \d+ or \dt+. They are
interactive commands so they can be canceled if they takes too long time.

I've attached the v4 of the patch that adds psql documentation update
for the \dn command to add on-disk report in verbose mode. Thanks for
the reminder :-)

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org