Le 21/02/2019 à 21:57, Jerry Sievers a écrit :
> Gilles Darold <gilles.dar...@dalibo.com> writes:
>
>> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>>
>>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.dar...@dalibo.com>
>>> wrote:
>>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.dar...@dalibo.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
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d7539ae743..4234fed01f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1594,7 +1594,8 @@ testdb=>
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system objects.
If <literal>+</literal> is appended to the command name, each object
- is listed with its associated permissions and description, if any.
+ is listed with its associated permissions, on-disk size and description,
+ if any.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..2fed622889 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4188,6 +4188,26 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
{
appendPQExpBufferStr(&buf, ",\n ");
printACLColumn(&buf, "n.nspacl");
+ if (pset.sversion >= 80100)
+ {
+ /* As of PostgreSQL 9.3, use LATERAL JOIN */
+ if (pset.sversion >= 93000)
+ {
+ appendPQExpBuffer(&buf, ",\n schema_size AS \"%s\"",
+ gettext_noop("Size"));
+ }
+ else
+ {
+ appendPQExpBuffer(&buf,
+ ",\n ((SELECT pg_catalog.pg_size_pretty((sum(pg_catalog.pg_total_relation_size(c.oid)))::bigint) FROM pg_catalog.pg_class c\n JOIN pg_catalog.pg_namespace s ON s.oid = c.relnamespace WHERE s.nspname = n.nspname AND c.relkind IN (%s,%s,%s))) AS \"%s\"",
+ gettext_noop("Size"),
+ CppAsString2(RELKIND_RELATION),
+ CppAsString2(RELKIND_MATVIEW),
+ CppAsString2(RELKIND_SEQUENCE)
+ );
+ }
+ }
+
appendPQExpBuffer(&buf,
",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
gettext_noop("Description"));
@@ -4195,6 +4215,16 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_namespace n\n");
+ /* As of PostgreSQL 9.3, use LATERAL JOIN */
+ if (pset.sversion >= 93000)
+ {
+ appendPQExpBuffer(&buf,
+ " JOIN LATERAL (\n SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_total_relation_size(c.oid))::bigint) As \"schema_size\"\n FROM pg_catalog.pg_class c\n JOIN pg_catalog.pg_namespace s ON s.oid = c.relnamespace\n WHERE s.nspname = n.nspname AND c.relkind IN (%s,%s,%s)\n ) l ON true\n",
+ CppAsString2(RELKIND_RELATION),
+ CppAsString2(RELKIND_MATVIEW),
+ CppAsString2(RELKIND_SEQUENCE)
+ );
+ }
if (!showSystem && !pattern)
appendPQExpBufferStr(&buf,
