čt 7. 2. 2019 v 9:51 odesílatel Amit Langote <langote_amit...@lab.ntt.co.jp>
napsal:
> Hi Pavel,
>
> Thanks for sending the updated patch.
>
> On 2018/12/19 15:38, Pavel Stehule wrote:
> > út 18. 12. 2018 v 8:49 odesílatel Amit Langote <
> >> On 2018/12/17 17:48, Pavel Stehule wrote:
> >>> I can imagine new additional flag - line "n" nested - and then we can
> >>> display nested partitioned tables with parent table info. Some like
> >>>
> >>> \dPt - show only root partition tables
> >>> \dPnt or \dPtn - show root and nested partitioned tables
> >>
> >> Too much complication maybe?
> >>
> >
> > I wrote it - the setup query is more complex, but not too much. I fixed
> the
> > size calculation, when nested partitions tables are visible - it
> calculate
> > partitions only from level1 group. Then the displayed size is same as
> total
> > size
>
> \dPn seems to work fine, but I don't quite understand why \dPn+ should
> show the sizes only for nested partitions of level. Consider the
> following example outputs:
>
> create table p (a int, b char) partition by list (a);
> create table p_1 partition of p for values in (1) partition by list (b);
> create table p_1_a partition of p_1 for values in ('a');
> create table p_1_bc partition of p_1 for values in ('b', 'c') partition by
> list (b);
> create table p_1_b partition of p_1_bc for values in ('b');
> create table p_1_c partition of p_1_bc for values in ('c');
> create table p_2 partition of p for values in (2);
> insert into p values (1, 'a');
> insert into p values (1, 'b');
> insert into p values (2);
>
> \dP+
> List of partitioned relations
> Schema │ Name │ Owner │ Size │ Description
> ────────┼──────┼───────┼───────┼─────────────
> public │ p │ amit │ 24 kB │
> (1 row)
>
> -- size of 'p' shown as 8KB, whereas it's actually 24KB as per above
> -- size of 'p_1' shown as 8KB, whereas it's actually 16KB
> \dPn+
> List of partitioned relations
> Schema │ Name │ Owner │ Parent name │ Size │ Description
> ────────┼────────┼───────┼─────────────┼────────────┼─────────────
> public │ p │ amit │ │ 8192 bytes │
> public │ p_1 │ amit │ p │ 8192 bytes │
> public │ p_1_bc │ amit │ p_1 │ 8192 bytes │
> (3 rows)
>
> Also, if the root partitioned table doesn't have a directly attached leaf
> partition, it's size is shown as 0 bytes, whereas I think it should
> consider the sizes of its other nested partitions.
>
> drop table p_2;
>
> \dPn+
> List of partitioned relations
> Schema │ Name │ Owner │ Parent name │ Size │ Description
> ────────┼────────┼───────┼─────────────┼────────────┼─────────────
> public │ p │ amit │ │ 0 bytes │
> public │ p_1 │ amit │ p │ 8192 bytes │
> public │ p_1_bc │ amit │ p_1 │ 8192 bytes │
> (3 rows)
>
> If I remove the following two statements from the patched code:
>
> + if (show_nested_partitions)
> + appendPQExpBuffer(&buf, "\n WHERE d.level = 1");
>
> + /*
> + * Assign size just for directly assigned tables, when nested
> + * partitions are visible
> + */
> + if (show_nested_partitions)
> + appendPQExpBuffer(&buf, "\n WHERE ppt.isleaf AND
> ppt.level = 1");
>
> I get the following output, which I find more intuitive:
>
> create table p_2 partition of p for values in (2);
> insert into p values (2);
>
> \dP+
> List of partitioned relations
> Schema │ Name │ Owner │ Size │ Description
> ────────┼──────┼───────┼───────┼─────────────
> public │ p │ amit │ 24 kB │
> (1 row)
>
>
> \dPn+
> List of partitioned relations
> Schema │ Name │ Owner │ Parent name │ Size │ Description
> ────────┼────────┼───────┼─────────────┼────────────┼─────────────
> public │ p │ amit │ │ 24 kB │
> public │ p_1 │ amit │ p │ 16 kB │
> public │ p_1_bc │ amit │ p_1 │ 8192 bytes │
> (3 rows)
>
> drop table p_2;
>
> \dPn+
> List of partitioned relations
> Schema │ Name │ Owner │ Parent name │ Size │ Description
> ────────┼────────┼───────┼─────────────┼────────────┼─────────────
> public │ p │ amit │ │ 16 kB │
> public │ p_1 │ amit │ p │ 16 kB │
> public │ p_1_bc │ amit │ p_1 │ 8192 bytes │
> (3 rows)
>
> Thoughts?
>
>
I renamed originally calculated column "size" to "direct partitions size"
.. see Alvaro's comment. Then I introduced new column "total partitions
size" that is calculated like you propose.
Now the result of dPn+ looks like
List of partitioned relations
┌────────┬────────┬───────┬─────────────┬────────────────────────┬───────────────────────┬─────────────┐
│ Schema │ Name │ Owner │ Parent name │ Direct partitions size │ Total
partitions size │ Description │
╞════════╪════════╪═══════╪═════════════╪════════════════════════╪═══════════════════════╪═════════════╡
│ public │ p │ pavel │ │ 8192 bytes │ 24
kB │ │
│ public │ p_1 │ pavel │ p │ 8192 bytes │ 16
kB │ │
│ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ 8192
bytes │ │
└────────┴────────┴───────┴─────────────┴────────────────────────┴───────────────────────┴─────────────┘
(3 rows)
>
> Meanwhile, some comments on the patch:
>
> + If modifier <literal>n</literal> is used, then nested partition
> + tables are displayed too.
>
> Maybe, say "non-root partitioned tables" instead of "nested partition
> tables". Same comment also applies for the same text in the paragraphs
> for \dPni and \dPnt too.
>
fixed
> +/*
> + * listPartitions()
> + *
> + * handler for \dP, \dPt and \dPi
>
> Maybe mention the 'n' modifier here?
>
fixed
>
> + */
> +bool
> +listPartitions(const char *pattern, bool verbose, bool show_indexes, bool
> show_tables, bool show_nested_partitions)
> +{
>
fixed
> I'm not sure if psql's source code formatting guidelines are different
> from the backend code, but putting all the arguments on the same line
> causes the line grow well over 78 characters. Can you wrap maybe?
>
> + if (pattern)
> + /* translator: object_name is "index", "table" or "relation"
> */
> + psql_error("Did not find any partitioned %s.\n",
> + object_name);
> + else
> + /* translator: objects_name is "indexes", "tables" or
> "relations" */
> + psql_error("Did not find any partitioned %s named \"%s\".\n",
> + objects_name,
> + pattern);
>
> You're using the variable 'pattern' in the block where it's supposed to be
> NULL. Maybe it should be:
>
> + if (pattern)
> + /* translator: object_name is "index", "table" or "relation"
> */
> + psql_error("Did not find any partitioned %s named \"%s\".\n",
> + object_name, pattern);
> + else
> + /* translator: objects_name is "indexes", "tables" or
> "relations" */
> + psql_error("Did not find any partitioned %s.\n",
> + objects_name);
>
fixed
attached updated patch
Regards
Pavel
> Thanks,
> Amit
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d7539ae743..76a6ceb0dd 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1659,6 +1659,71 @@ testdb=>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\dP[n+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists partitioned relations. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ entries whose relation name or schema name matches
+ the pattern are listed. If the form <literal>\dP+</literal>
+ is used, the sum of sizes of related partitions (including the
+ table and indexes, if any) and associated description
+ are also displayed.
+ </para>
+
+ <para>
+ If modifier <literal>n</literal> is used, then non-root partition
+ tables are displayed too. The size is calculated just for directly
+ assigned partitions (not for nested partitions).
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\dPi[n+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists partitioned indexes. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ entries whose index name or schema name matches
+ the pattern are listed. If the form <literal>\dPi+</literal>
+ is used, the sum of sizes of related indexes and associated
+ description are also displayed.
+ </para>
+
+ <para>
+ If modifier <literal>n</literal> is used, then non-root partition
+ tables are displayed too. The size is calculated just for directly
+ assigned partitions (not for nested partitions).
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
+ <term><literal>\dPt[n+] [ <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>\dPt+</literal>
+ is used, the sum of sizes of related tables and associated
+ description are also displayed.
+ </para>
+
+ <para>
+ If modifier <literal>n</literal> is used, then non-root partition
+ tables are displayed too. The size is calculated just for directly
+ assigned partitions (not for nested partitions).
+ </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 ab259c473a..42ffbbe630 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -782,6 +782,36 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'p':
success = permissionsList(pattern);
break;
+ case 'P':
+ {
+ bool show_nested_partitions = strchr(cmd, 'n') ? true : false;
+
+ switch (cmd[2])
+ {
+ case 'i':
+ /* show indexes only */
+ success = listPartitions(pattern, show_verbose, true, false, show_nested_partitions);
+ break;
+ case 't':
+ /* show tables only */
+ success = listPartitions(pattern, show_verbose, false, true, show_nested_partitions);
+ break;
+ case '+':
+ case '\0':
+ case 'n':
+ /*
+ * show relations - when there are not pattern, then it shows
+ * tables with total relation size, else where it shows tables
+ * and indexes.
+ */
+ success = listPartitions(pattern, show_verbose, false, false, show_nested_partitions);
+ break;
+ default:
+ status = PSQL_CMD_UNKNOWN;
+ break;
+ }
+ }
+ 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 4da6719ce7..e69fd0d4c0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3659,6 +3659,248 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
return true;
}
+/*
+ * listPartitions()
+ *
+ * handler for \dP[t|i][n]
+ */
+bool
+listPartitions(const char *pattern, bool verbose, bool show_indexes,
+ bool show_tables, bool show_nested_partitions)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ static bool translate_columns[] = {false, false, false, false, false, false, false, false, false};
+ const char *size_function;
+ const char *relkind_str;
+ const char *object_name;
+ const char *objects_name;
+ bool mixed_output = 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;
+ }
+
+ if (show_indexes)
+ {
+ /* \dPi */
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_INDEX);
+ object_name = gettext_noop("index");
+ objects_name = gettext_noop("indexes");
+ }
+ else if (show_tables)
+ {
+ /* \dPt */
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE);
+ object_name = gettext_noop("table");
+ objects_name = gettext_noop("tables");
+ }
+ else
+ {
+ /* \dP without pattern */
+ if (!pattern)
+ {
+ size_function = "pg_total_relation_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE);
+ object_name = gettext_noop("relation");
+ objects_name = gettext_noop("relations");
+ }
+ else
+ {
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE)
+ ", " CppAsString2(RELKIND_PARTITIONED_INDEX);
+ object_name = gettext_noop("relation or index");
+ objects_name = gettext_noop("relations or indexes");
+ mixed_output = 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 (mixed_output)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n CASE c.relkind"
+ " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
+ " END as \"%s\"",
+ gettext_noop("partitioned table"),
+ gettext_noop("partitioned index"),
+ gettext_noop("Type"));
+
+ translate_columns[4] = true;
+ }
+
+ if (show_nested_partitions)
+ appendPQExpBuffer(&buf,
+ ",\n c3.relname as \"%s\"",
+ gettext_noop("Parent name"));
+
+ if (show_indexes || mixed_output)
+ appendPQExpBuffer(&buf,
+ ",\n c2.relname as \"%s\"",
+ gettext_noop("On table"));
+
+ if (verbose)
+ {
+ if (show_nested_partitions)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n s.dps as \"%s\"",
+ gettext_noop("Direct partitions size"));
+ appendPQExpBuffer(&buf,
+ ",\n s.tps as \"%s\"",
+ gettext_noop("Total partitions size"));
+ }
+ else
+ /* Both previous sizes are same in this case. Show only one */
+ appendPQExpBuffer(&buf,
+ ",\n s.tps as \"%s\"",
+ gettext_noop("Partitions 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");
+
+ if (show_indexes || mixed_output)
+ appendPQExpBufferStr(&buf,
+ "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
+ "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
+
+ if (show_nested_partitions)
+ appendPQExpBufferStr(&buf,
+ "\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid"
+ "\n LEFT JOIN pg_catalog.pg_class c3 ON c3.oid = inh.inhparent");
+
+ if (verbose)
+ {
+ if (pset.sversion < 120000)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n LATERAL (WITH RECURSIVE d\n"
+ " AS (SELECT inhrelid AS oid, 1 AS level\n"
+ " FROM pg_catalog.pg_inherits\n"
+ " WHERE inhparent = c.oid\n"
+ " UNION ALL\n"
+ " SELECT inhrelid, level + 1\n"
+ " FROM pg_catalog.pg_inherits i\n"
+ " JOIN d ON i.inhparent = d.oid)\n"
+ " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.%s("
+ "d.oid))) AS tps,\n"
+ " pg_catalog.pg_size_pretty(sum("
+ "\n CASE WHEN d.level = 1"
+ " THEN pg_catalog.%s(d.oid) ELSE 0 END)) AS dps\n"
+ " FROM d) s",
+ size_function, size_function);
+ }
+ else
+ {
+ /* PostgreSQL 12 has pg_partition_tree function */
+ appendPQExpBuffer(&buf,
+ ",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum("
+ "\n CASE WHEN ppt.isleaf AND ppt.level = 1"
+ "\n THEN pg_catalog.%s(ppt.relid)"
+ " ELSE 0 END)) AS dps"
+ ",\n pg_catalog.pg_size_pretty(sum("
+ "pg_catalog.%s(ppt.relid))) AS tps"
+ "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s",
+ size_function, size_function);
+ }
+ }
+
+ appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (%s)", relkind_str);
+ appendPQExpBufferStr(&buf, !show_nested_partitions ? " AND NOT c.relispartition\n" : "\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)
+ /* translator: objects_name is "indexes", "tables" or "relations" */
+ psql_error("Did not find any partitioned %s named \"%s\".\n",
+ objects_name,
+ pattern);
+ else
+ /* translator: object_name is "index", "table" or "relation" */
+ psql_error("Did not find any partitioned %s.\n",
+ object_name);
+ }
+ else
+ {
+ PQExpBufferData title;
+
+ initPQExpBuffer(&title);
+
+ /* translator: objects_name is "indexes", "tables" or "relations" */
+ appendPQExpBuffer(&title, _("List of partitioned %s"), objects_name);
+
+ myopt.nullPrint = NULL;
+ myopt.title = title.data;
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ termPQExpBuffer(&title);
+ }
+
+ PQclear(res);
+ return true;
+}
/*
* \dL
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 4ff1f91f38..4fc1cacbee 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[n], \dPi[n], \dPt[n] */
+extern bool listPartitions(const char *pattern, bool verbose, bool show_indexes, bool show_tables, bool show_nested_partitions);
+
/* \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 7c6fa2c590..7137452842 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -168,7 +168,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(126, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(129, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -250,6 +250,9 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n"));
fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
+ fprintf(output, _(" \\dP[n+] [PATTERN] list partitioned relations\n"));
+ fprintf(output, _(" \\dPi[n+] [PATTERN] list partitioned indexes\n"));
+ fprintf(output, _(" \\dPt[n+] [PATTERN] list partitioned tables\n"));
fprintf(output, _(" \\drds [PATRN1 [PATRN2]] list per-database role settings\n"));
fprintf(output, _(" \\dRp[+] [PATTERN] list replication publications\n"));
fprintf(output, _(" \\dRs[+] [PATTERN] list replication subscriptions\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7b7a88fda3..928ee9a5cb 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -464,6 +464,15 @@ static const SchemaQuery Query_for_list_of_indexes = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_partitioned_indexes = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "pg_catalog.quote_ident(c.relname)",
+};
+
+
/* All relations */
static const SchemaQuery Query_for_list_of_relations = {
.catname = "pg_catalog.pg_class c",
@@ -472,6 +481,15 @@ static const SchemaQuery Query_for_list_of_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+/* partitioned relations */
+static const SchemaQuery Query_for_list_of_partitioned_relations = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_TABLE),
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "pg_catalog.quote_ident(c.relname)",
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1381,7 +1399,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", "\\dPi", "\\dPt",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -3503,6 +3521,10 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
else if (TailMatchesCS("\\dp") || TailMatchesCS("\\z"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, NULL);
+ else if (TailMatchesCS("\\dPi*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_indexes, NULL);
+ else if (TailMatchesCS("\\dP*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_relations, NULL);
else if (TailMatchesCS("\\ds*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
else if (TailMatchesCS("\\dt*"))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 775b127121..52bcb3f918 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4539,3 +4539,129 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+create schema testpart;
+create role testrole_partitioning;
+alter schema testpart owner to testrole_partitioning;
+set role to testrole_partitioning;
+-- run test inside own schema and hide other partitions
+set search_path to testpart;
+create table testtable_apple(logdate date);
+create table testtable_orange(logdate date);
+create index testtable_apple_index on testtable_apple(logdate);
+create index testtable_orange_index on testtable_orange(logdate);
+create table testpart_apple(logdate date) partition by range(logdate);
+create table testpart_orange(logdate date) partition by range(logdate);
+create index testpart_apple_index on testpart_apple(logdate);
+create index testpart_orange_index on testpart_orange(logdate);
+-- only partition related object should be displayed
+\dP test*apple*
+ List of partitioned relations or indexes
+ Schema | Name | Owner | Type | On table
+----------+----------------------+-----------------------+-------------------+----------------
+ testpart | testpart_apple | testrole_partitioning | partitioned table |
+ testpart | testpart_apple_index | testrole_partitioning | partitioned index | testpart_apple
+(2 rows)
+
+\dPt test*apple*
+ List of partitioned tables
+ Schema | Name | Owner
+----------+----------------+-----------------------
+ testpart | testpart_apple | testrole_partitioning
+(1 row)
+
+\dPi test*apple*
+ List of partitioned indexes
+ Schema | Name | Owner | On table
+----------+----------------------+-----------------------+----------------
+ testpart | testpart_apple_index | testrole_partitioning | testpart_apple
+(1 row)
+
+drop table testtable_apple;
+drop table testtable_orange;
+drop table testpart_apple;
+drop table testpart_orange;
+create table parent_tab (id int) partition by range (id);
+create index parent_index on parent_tab (id);
+create table child_0_10 partition of parent_tab
+ for values from (0) to (10);
+create table child_10_20 partition of parent_tab
+ for values from (10) to (20);
+create table child_20_30 partition of parent_tab
+ for values from (20) to (30);
+insert into parent_tab values (generate_series(0,29));
+create table child_30_40 partition of parent_tab
+for values from (30) to (40)
+ partition by range(id);
+create table child_30_35 partition of child_30_40
+ for values from (30) to (35);
+create table child_35_40 partition of child_30_40
+ for values from (35) to (40);
+insert into parent_tab values (generate_series(30,39));
+\dPt
+ List of partitioned tables
+ Schema | Name | Owner
+----------+------------+-----------------------
+ testpart | parent_tab | testrole_partitioning
+(1 row)
+
+\dPi
+ List of partitioned indexes
+ Schema | Name | Owner | On table
+----------+--------------+-----------------------+------------
+ testpart | parent_index | testrole_partitioning | parent_tab
+(1 row)
+
+\dP testpart.*
+ List of partitioned relations or indexes
+ Schema | Name | Owner | Type | On table
+----------+--------------+-----------------------+-------------------+------------
+ testpart | parent_index | testrole_partitioning | partitioned index | parent_tab
+ testpart | parent_tab | testrole_partitioning | partitioned table |
+(2 rows)
+
+\dP
+ List of partitioned relations
+ Schema | Name | Owner
+----------+------------+-----------------------
+ testpart | parent_tab | testrole_partitioning
+(1 row)
+
+\dPtn
+ List of partitioned tables
+ Schema | Name | Owner | Parent name
+----------+-------------+-----------------------+-------------
+ testpart | child_30_40 | testrole_partitioning | parent_tab
+ testpart | parent_tab | testrole_partitioning |
+(2 rows)
+
+\dPin
+ List of partitioned indexes
+ Schema | Name | Owner | Parent name | On table
+----------+--------------------+-----------------------+--------------+-------------
+ testpart | child_30_40_id_idx | testrole_partitioning | parent_index | child_30_40
+ testpart | parent_index | testrole_partitioning | | parent_tab
+(2 rows)
+
+\dPn
+ List of partitioned relations
+ Schema | Name | Owner | Parent name
+----------+-------------+-----------------------+-------------
+ testpart | child_30_40 | testrole_partitioning | parent_tab
+ testpart | parent_tab | testrole_partitioning |
+(2 rows)
+
+\dPn testpart.*
+ List of partitioned relations or indexes
+ Schema | Name | Owner | Type | Parent name | On table
+----------+--------------------+-----------------------+-------------------+--------------+-------------
+ testpart | child_30_40 | testrole_partitioning | partitioned table | parent_tab |
+ testpart | child_30_40_id_idx | testrole_partitioning | partitioned index | parent_index | child_30_40
+ testpart | parent_index | testrole_partitioning | partitioned index | | parent_tab
+ testpart | parent_tab | testrole_partitioning | partitioned table | |
+(4 rows)
+
+drop table parent_tab cascade;
+drop schema testpart;
+set search_path to default;
+set role to default;
+drop role testrole_partitioning;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1bb2a6e16d..540a416a7b 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1016,3 +1016,72 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+create schema testpart;
+create role testrole_partitioning;
+
+alter schema testpart owner to testrole_partitioning;
+
+set role to testrole_partitioning;
+
+-- run test inside own schema and hide other partitions
+set search_path to testpart;
+
+create table testtable_apple(logdate date);
+create table testtable_orange(logdate date);
+create index testtable_apple_index on testtable_apple(logdate);
+create index testtable_orange_index on testtable_orange(logdate);
+
+create table testpart_apple(logdate date) partition by range(logdate);
+create table testpart_orange(logdate date) partition by range(logdate);
+
+create index testpart_apple_index on testpart_apple(logdate);
+create index testpart_orange_index on testpart_orange(logdate);
+
+-- only partition related object should be displayed
+\dP test*apple*
+\dPt test*apple*
+\dPi test*apple*
+
+drop table testtable_apple;
+drop table testtable_orange;
+drop table testpart_apple;
+drop table testpart_orange;
+
+create table parent_tab (id int) partition by range (id);
+create index parent_index on parent_tab (id);
+create table child_0_10 partition of parent_tab
+ for values from (0) to (10);
+create table child_10_20 partition of parent_tab
+ for values from (10) to (20);
+create table child_20_30 partition of parent_tab
+ for values from (20) to (30);
+insert into parent_tab values (generate_series(0,29));
+create table child_30_40 partition of parent_tab
+for values from (30) to (40)
+ partition by range(id);
+create table child_30_35 partition of child_30_40
+ for values from (30) to (35);
+create table child_35_40 partition of child_30_40
+ for values from (35) to (40);
+insert into parent_tab values (generate_series(30,39));
+
+\dPt
+\dPi
+
+\dP testpart.*
+\dP
+
+\dPtn
+\dPin
+\dPn
+\dPn testpart.*
+
+drop table parent_tab cascade;
+
+drop schema testpart;
+
+set search_path to default;
+
+set role to default;
+drop role testrole_partitioning;
