[patch] Add schema total size to psql \dn+
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.
Best regards,
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
Attachments:
psql-schema-size.difftext/x-patch; name=psql-schema-size.diffDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..8702e52e4d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4188,6 +4188,10 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
{
appendPQExpBufferStr(&buf, ",\n ");
printACLColumn(&buf, "n.nspacl");
+ 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"));
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
Attachments:
psql-schema-size-v2.difftext/x-patch; name=psql-schema-size-v2.diffDownload
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,
On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@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 needed that quite often, so I'm +1 to add this! Please register
this patch on the next commitfest.
Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@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 needed that quite often, so I'm +1 to add this! Please register
this patch on the next commitfest.
Added to next commitfest.
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@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.darold@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.
Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@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.darold@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.
Thanks for the review.
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
Attachments:
psql-schema-size-v3.difftext/x-patch; name=psql-schema-size-v3.diffDownload
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,
Gilles Darold <gilles.darold@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.darold@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.darold@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 :-)
FWIW
Thanks for the review.
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
Le 21/02/2019 à 21:57, Jerry Sievers a écrit :
Gilles Darold <gilles.darold@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.darold@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.darold@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
Attachments:
psql-schema-size-v4.difftext/x-patch; name=psql-schema-size-v4.diffDownload
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,
On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
The attached simple patch adds this feature. Is there any cons adding
this information?
Well, it'll take time to compute, maybe a lot of time if the database
is big and the server is busy. Not sure how serious that problem can
get.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
The attached simple patch adds this feature. Is there any cons adding
this information?
Well, it'll take time to compute, maybe a lot of time if the database
is big and the server is busy. Not sure how serious that problem can
get.
Is there any permissions issue involved here? I'd be a bit worried
about whether \dn+ could fail, or deliver misleading answers, when
run by a user without permissions on (some) tables. Also, even if
we allow people to get size info on tables they can't read today,
having this feature would be a roadblock to tightening that in
the future.
regards, tom lane
On Fri, Feb 22, 2019 at 7:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Is there any permissions issue involved here? I'd be a bit worried
about whether \dn+ could fail, or deliver misleading answers, when
run by a user without permissions on (some) tables. Also, even if
we allow people to get size info on tables they can't read today,
having this feature would be a roadblock to tightening that in
the future.
Gilles' patch is using pg_total_relation_size(), so there's no
permission check at all. Also AFAICS this function even allows any
user to get the size of any other user backend's temporary table.
Le 22/02/2019 à 19:21, Tom Lane a écrit :
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
The attached simple patch adds this feature. Is there any cons adding
this information?Well, it'll take time to compute, maybe a lot of time if the database
is big and the server is busy. Not sure how serious that problem can
get.Is there any permissions issue involved here? I'd be a bit worried
about whether \dn+ could fail, or deliver misleading answers, when
run by a user without permissions on (some) tables. Also, even if
we allow people to get size info on tables they can't read today,
having this feature would be a roadblock to tightening that in
the future.
That's right, I've removed the patch. My first idea was to add a server
side function pg_schema_size() but I was thinking that a psql
implementation was enough but obviously that was not my best idea ever.
Let me know if there is any interest in having this pg_schema_size()
server side function that could take care of user permissions or be used
by a super user only.
Best regards,
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
Le 22/02/2019 à 17:06, Robert Haas a écrit :
On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
The attached simple patch adds this feature. Is there any cons adding
this information?Well, it'll take time to compute, maybe a lot of time if the database
is big and the server is busy. Not sure how serious that problem can
get.
I agree, this king of report should be reserved to a super user
voluntary action and not as a default psql behavior. Patch removed.
Best regards,
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org