Le 20/02/2019 à 23:26, Gilles Darold a écrit :
> Hi all,
>
>
> 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 ?
>
>
> gilles=# \dn+
> List of schemas
> Name | Owner | Access privileges | Size | Description
> --------+----------+----------------------+---------+------------------------
> public | postgres | postgres=UC/postgres+| 608 kB | standard public schema
> | | =UC/postgres | |
> test | gilles | | 57 MB |
> empty | gilles | | 0 bytes |
> (3 rows)
>
> 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.
Improve this patch by using LATERAL JOIN when version >= 9.3.
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..9b57f59ec7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4188,6 +4188,19 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
{
appendPQExpBufferStr(&buf, ",\n ");
printACLColumn(&buf, "n.nspacl");
+ /* 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_relation_size(c.oid)))::bigint) FROM pg_catalog.pg_class c\n LEFT JOIN pg_catalog.pg_namespace s ON s.oid = c.relnamespace WHERE s.nspname = n.nspname)) AS \"%s\"",
+ gettext_noop("Size"));
+ }
+
appendPQExpBuffer(&buf,
",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
gettext_noop("Description"));
@@ -4195,6 +4208,12 @@ 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,
+ " LEFT JOIN LATERAL (\n SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_relation_size(c.oid))::bigint) As \"schema_size\"\n FROM pg_catalog.pg_class c\n LEFT JOIN pg_catalog.pg_namespace s ON s.oid = c.relnamespace\n WHERE s.nspname = n.nspname\n ) l ON true\n");
+ }
if (!showSystem && !pattern)
appendPQExpBufferStr(&buf,
