Hi

Ășt 30. 10. 2018 v 7:52 odesĂ­latel Amit Langote <
langote_amit...@lab.ntt.co.jp> napsal:

> Hi Mathias, Pavel,
>
> On 2018/08/17 12:26, Mathias Brossard wrote:
> > On Thu, Aug 16, 2018 at 12:46 AM Pavel Stehule <pavel.steh...@gmail.com>
> >>
> >> This is question - maybe we can support older partitioning based on only
> >> inheritance - and the query can be more exact on PostgreSQL 10 and
> newer.
> >>
> >> Please, send any patch. You are welcome.
> >
> > In my very humble opinion, I would restrict the definition of partitions
> to
> > declarative partitioning. My justification would be that partitions all
> use
> > inheritance, but not all inheritance is a partition (how would you handle
> > multiple inheritance).
> >
> > See patch attached that fails (in a way similar to other features) when
> > connected to servers with version earlier than 10.0.
>
> The patch to add the pg_partition_tree() function was just committed:
>
> Add pg_partition_tree to display information about partitions
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d5eec4eefde70
>
> Could one of you please revise the patch to use that function to produce
> the output of \dP+?
>

here it is.

It is based on Mathias's patch. Although we can use pg_partition_tree on
PostgreSQL, we still should to support PostgreSQL 10, 11 where this
function is not available

Regards

Pavel



> Note that pg_partition_tree simply scans the underlying catalog to get all
> the tables, so if you pass it a table that's not partitioned (relkind ==
> 'r'), but has inheritance children, the children will be returned in its
> output.  So, if you want to limit the output of \dP to partitioned tables,
> be sure to include relkind = 'p' condition in the query.
>
> Thanks,
> Amit
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index eb9d93a168..89c60aa340 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1635,6 +1635,21 @@ testdb=&gt;
         </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 5b4d54a442..9d76bec38e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -766,6 +766,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 4ca0db1d0c..9c69351277 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3635,6 +3635,132 @@ 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};
+
+	/*
+	 * Note: Declarative table partitions are only supported as of Pg 10.0.
+	 */
+
+	if (pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		psql_error("The server (version %s) does not support declarative table partitioning.\n",
+				   formatPGVersionNumber(pset.sversion, false,
+										 sverbuf, sizeof(sverbuf)));
+		return true;
+	}
+
+	initPQExpBuffer(&buf);
+
+	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)
+	{
+		if (pset.sversion < 120000)
+		{
+			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"
+							  "         SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
+							  "oid))) FROM d) AS \"%s\""
+							  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+							  gettext_noop("Size"),
+							  gettext_noop("Description"));
+		}
+		else
+		{
+			/* PostgreSQL 11 has pg_partition_tree function */
+			appendPQExpBuffer(&buf,
+							  ",\n  (SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
+							  "relid)))\n"
+							  "     FROM pg_partition_tree(c.oid)) AS \"%s\""
+							  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+							  gettext_noop("Size"),
+							  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 586aebddd3..07d46ef3cf 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 a980f92e11..7b57a79e38 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1332,7 +1332,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",

Reply via email to