From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: [PATCH] psql: \dn+ to show size of each schema..
Date: Tue, 13 Jul 2021 22:07:25 -0500
Message-ID: <20210714030725.GG9600@telsasoft.com> (raw)
\db+ and \l+ show sizes of tablespaces and databases, so I was surprised in the
past that \dn+ didn't show sizes of schemas. I would find that somewhat
convenient, and I assume other people would use it even more useful.
\db+ and \l+ seem to walk the filesystem, and this is distinguished from those
cases. (Also, schemas are per-DB, not global).
Maybe it's an issue if \dn+ is slow and expensive, since that's how to display
ACL. But \db+ has the same issue. Maybe there should be a \db++ and \dn++ to
allow \dn+ to showing the ACL but not the size.
pg_relation_size() only includes one fork, and the other functions include
toast, which should be in its separate schema, so it has to be summed across
forks.
postgres=# \dnS+
child | postgres | | | 946 MB
information_schema | postgres | postgres=UC/postgres+| | 88 kB
| | =U/postgres | |
pg_catalog | postgres | postgres=UC/postgres+| system catalog schema | 42 MB
| | =U/postgres | |
pg_toast | postgres | | reserved schema for TOAST tables | 3908 MB
public | postgres | postgres=UC/postgres+| standard public schema | 5627 MB
| | =UC/postgres | |
inline patch 1: src/bin/psql/describe.c (+5/-0 lines) download 0001-psql-dn-to-show-size-of-each-schema.patchFrom c2d68eb54f785c759253d4100460aa1af9cbc676 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 13 Jul 2021 21:25:48 -0500
Subject: [PATCH] psql: \dn+ to show size of each schema..
See also: 358a897fa, 528ac10c7
---
src/bin/psql/describe.c | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 2abf255798..6b9b6ea34a 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -5036,6 +5036,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
appendPQExpBuffer(&buf,
",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
gettext_noop("Description"));
++ appendPQExpBuffer(&buf,+ ",\n (SELECT pg_catalog.pg_size_pretty(sum(pg_relation_size(oid,fork))) FROM pg_catalog.pg_class c,\n"+ " (VALUES('main'),('fsm'),('vm'),('init')) AS fork(fork) WHERE c.relnamespace = n.oid) AS \"%s\"",+ gettext_noop("Size"));
}
appendPQExpBufferStr(&buf,
--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com
Subject: Re: [PATCH] psql: \dn+ to show size of each schema..
In-Reply-To: <20210714030725.GG9600@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox