Hi
I am sending a prototype of patch. Now, it calculates size of partitioned
tables with recursive query. When any more simple method will be possible,
the size calculation will be changed.
postgres=# \dt+
List of relations
+--------+------------+-------+-------+---------+-------------+
| Schema | Name | Type | Owner | Size | Description |
+--------+------------+-------+-------+---------+-------------+
| public | data | table | pavel | 0 bytes | |
| public | data_2016 | table | pavel | 15 MB | |
| public | data_2017 | table | pavel | 15 MB | |
| public | data_other | table | pavel | 11 MB | |
+--------+------------+-------+-------+---------+-------------+
(4 rows)
postgres=# \dP+
List of partitioned tables
+--------+------+-------+-------+-------------+
| Schema | Name | Owner | Size | Description |
+--------+------+-------+-------+-------------+
| public | data | pavel | 42 MB | |
+--------+------+-------+-------+-------------+
(1 row)
Regards
Pavel
p.s. Another patch can be replacement of relation type from "table" to
"partitioned table"
postgres=# \dt+
List of relations
+--------+------------+-------------------+-------+---------+-------------+
| Schema | Name | Type | Owner | Size | Description |
+--------+------------+-------------------+-------+---------+-------------+
| public | data | partitioned table | pavel | 0 bytes | |
| public | data_2016 | table | pavel | 15 MB | |
| public | data_2017 | table | pavel | 15 MB | |
| public | data_other | table | pavel | 11 MB | |
+--------+------------+-------------------+-------+---------+-------------+
(4 rows)
A patch is simple for this case
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c3bdf8555d..491e58eb29 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3490,8 +3490,8 @@ listTables(const char *tabtypes, const char *pattern,
bool verbose, bool showSys
gettext_noop("sequence"),
gettext_noop("special"),
gettext_noop("foreign table"),
- gettext_noop("table"), /* partitioned table */
- gettext_noop("index"), /* partitioned index */
+ gettext_noop("partitioned table"), /* partitioned
table */
+ gettext_noop("partitioned index"), /* partitioned
index */
gettext_noop("Type"),
gettext_noop("Owner"));
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index b17039d60f..d2eb701a96 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1635,6 +1635,21 @@ testdb=>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\dP[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists partitioned tables. If <replaceable class="parameter">pattern</replaceable> is
+ specified, only entries whose table name or schema name matches
+ the pattern are listed. If the form <literal>\dP+</literal>
+ is used, a sum of size of related partitions and a description
+ are also displayed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\drds [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4c85f43f09..09f49b4f39 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -785,6 +785,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'p':
success = permissionsList(pattern);
break;
+ case 'P':
+ success = listPartitions(pattern, show_verbose);
+ break;
case 'T':
success = describeTypes(pattern, show_verbose, show_system);
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c3bdf8555d..4542fd511c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3601,6 +3601,122 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
return true;
}
+/*
+ * listPartitions()
+ *
+ * handler for \dP
+ */
+bool
+listPartitions(const char *pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns[] = {false, false, true, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ /*
+ * Note: as of Pg 8.2, we no longer use relkind 's' (special), but we keep
+ * it here for backwards compatibility.
+ */
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname as \"%s\",\n"
+ " c.relname as \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Owner"));
+
+ if (verbose)
+ {
+ /* CTE is supported from 8.4 */
+ if (pset.sversion >= 80400)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n (WITH RECURSIVE d\n"
+ " AS (SELECT inhrelid AS oid\n"
+ " FROM pg_inherits\n"
+ " WHERE inhparent = c.oid\n"
+ " UNION ALL\n"
+ " SELECT inhrelid\n"
+ " FROM pg_inherits i\n"
+ " JOIN d ON i.inhparent = d.oid)\n");
+
+ if (pset.sversion >= 90000)
+ appendPQExpBuffer(&buf,
+ " SELECT pg_catalog.pg_size_pretty(sum(pg_table_size(");
+ else
+ appendPQExpBuffer(&buf,
+ " SELECT pg_catalog.pg_size_pretty(sum(pg_relation_size(");
+
+ appendPQExpBuffer(&buf,
+ "oid))) FROM d) AS \"%s\"",
+ gettext_noop("Size"));
+ }
+
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+ gettext_noop("Description"));
+ }
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_class c"
+ "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
+
+ appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN ('p')\n");
+
+ if (!pattern)
+ appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ /*
+ * TOAST objects are suppressed unconditionally. Since we don't provide
+ * any way to select RELKIND_TOASTVALUE above, we would never show toast
+ * tables in any case; it seems a bit confusing to allow their indexes to
+ * be shown. Use plain \d if you really need to look at a TOAST
+ * table/index.
+ */
+ appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "c.relname", NULL,
+ "pg_catalog.pg_table_is_visible(c.oid)");
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ /*
+ * Most functions in this file are content to print an empty table when
+ * there are no matching objects. We intentionally deviate from that
+ * here, but only in !quiet mode, for historical reasons.
+ */
+ if (PQntuples(res) == 0 && !pset.quiet)
+ {
+ if (pattern)
+ psql_error("Did not find any relation named \"%s\".\n",
+ pattern);
+ else
+ psql_error("Did not find any relations.\n");
+ }
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of partitioned tables");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ }
+
+ PQclear(res);
+ return true;
+}
/*
* \dL
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index a4cc5efae0..0fb7c4b91e 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -63,6 +63,9 @@ extern bool listAllDbs(const char *pattern, bool verbose);
/* \dt, \di, \ds, \dS, etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
+/* \dP */
+extern bool listPartitions(const char *pattern, bool verbose);
+
/* \dD */
extern bool listDomains(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 702e742af4..ccb85e8cb1 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -167,7 +167,7 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(125, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(126, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -254,6 +254,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dRs[+] [PATTERN] list replication subscriptions\n"));
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
+ fprintf(output, _(" \\dP[+] [PATTERN] list partitioned tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index bb696f8ee9..ce911983dc 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1621,7 +1621,7 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
- "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
+ "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
