On 2019-Apr-02, Alvaro Herrera wrote:
> Something is not right:
Another thing that was not right is that translated_columns was being
marked static, and modified in the function; so if you called \dP twice
where one called for translation and the second not, the second time
we'd also translating that column.
Attached contains fixes for those three issues.
I'm gonna go over the docs now.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index b29e7547c6a..a789042ce5f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1659,6 +1659,72 @@ 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 name matches the pattern are listed. By default, only
+ partitioned tables are listed; supply a pattern to also include
+ partitioned indexes. If the form <literal>\dP+</literal>
+ is used, the sum of sizes of table's partitions (including their
+ indexes) is also displayed, along with the associated description.
+ </para>
+
+ <para>
+ If the modifier <literal>n</literal> (<quote>nested</quote>) is used,
+ then non-root partitioned tables are included, and a column is shown
+ displaying the parent of each partitioned relation.
+
+ If <literal>n</literal> is combined with <literal>+</literal>, two
+ sizes are shown: one including the total size of directly-attached
+ leaf partitions, and another showing the total size of all partitions,
+ including indirectly attached sub-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 name matches the pattern are listed. If the form
+ <literal>\dPi+</literal> is used, the sum of sizes of index's
+ partitions is also displayed, along with the associated description.
+ </para>
+
+ <para>
+ If the <literal>n</literal> modifier is used, non-root partitioned
+ indexes are displayed too.
+ </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 name matches the pattern are listed. If the form
+ <literal>\dPt+</literal> is used, the sum of sizes of table's
+ partitions is also displayed, along with the associated description.
+ </para>
+
+ <para>
+ If the <literal>n</literal> modifier is used, non-root partitioned
+ tables are displayed too.
+ </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 72188b7f3ef..ce8bee99087 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -782,6 +782,42 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'p':
success = permissionsList(pattern);
break;
+ case 'P':
+ {
+ bool show_nested = strchr(cmd, 'n') ? true : false;
+
+ switch (cmd[2])
+ {
+ case 'i':
+ /* show indexes only */
+ success = listPartitions(pattern, show_verbose,
+ true, false,
+ show_nested);
+ break;
+ case 't':
+ /* show tables only */
+ success = listPartitions(pattern, show_verbose,
+ false, true,
+ show_nested);
+ break;
+ case '+':
+ case '\0':
+ case 'n':
+ /*
+ * Show only tables if there is no pattern. Also
+ * show indexes if pattern is specified. Show
+ * total size if verbose output is specified.
+ */
+ success = listPartitions(pattern, show_verbose,
+ false, false,
+ show_nested);
+ 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 036810303a6..736cca33d6b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3774,6 +3774,247 @@ 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)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool translate_columns[] = {false, false, false, false, false, false, false, false, false};
+ const char *size_function;
+ const char *relkind_str;
+ const char *errmsg_pattern;
+ const char *errmsg_no_pattern;
+ const char *tabletitle;
+ bool mixed_output = false;
+
+ /*
+ * Note: Declarative table partitions are only supported as of Pg 10.0.
+ */
+ if (pset.sversion < 100000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support declarative table partitioning.",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ if (show_indexes)
+ {
+ /* \dPi */
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_INDEX);
+ errmsg_pattern = gettext_noop("Did not find partitioned indexes matching \"%s\".");
+ errmsg_no_pattern = gettext_noop("Did not find partitioned indexes.");
+ tabletitle = gettext_noop("List of partitioned indexes");
+ }
+ else if (show_tables)
+ {
+ /* \dPt */
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE);
+ errmsg_pattern = gettext_noop("Did not find partitioned tables matching \"%s\".");
+ errmsg_no_pattern = gettext_noop("Did not find partitioned tables.");
+ tabletitle = gettext_noop("List of partitioned tables");
+ }
+ else
+ {
+ /* \dP without pattern */
+ if (!pattern)
+ {
+ size_function = "pg_total_relation_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE);
+ errmsg_pattern = gettext_noop("Did not find partitioned tables matching \"%s\".");
+ errmsg_no_pattern = gettext_noop("Did not find partitioned tables.");
+ tabletitle = gettext_noop("List of partitioned tables");
+ }
+ else
+ {
+ size_function = "pg_table_size";
+ relkind_str = CppAsString2(RELKIND_PARTITIONED_TABLE)
+ ", " CppAsString2(RELKIND_PARTITIONED_INDEX);
+ errmsg_pattern = gettext_noop("Did not find partitioned tables or indexes matching \"%s\".");
+ errmsg_no_pattern = gettext_noop("Did not find partitioned tables or indexes.");
+ tabletitle = gettext_noop("List of partitioned tables and 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[3] = true;
+ }
+
+ if (show_nested)
+ 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)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n s.dps as \"%s\"",
+ gettext_noop("Leaf partition size"));
+ appendPQExpBuffer(&buf,
+ ",\n s.tps as \"%s\"",
+ gettext_noop("Total size"));
+ }
+ else
+ /* Sizes of all partitions are considered in this case. */
+ appendPQExpBuffer(&buf,
+ ",\n s.tps as \"%s\"",
+ gettext_noop("Total 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)
+ 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 ? " 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)
+ pg_log_error(errmsg_pattern, pattern);
+ else
+ pg_log_error("%s", errmsg_no_pattern);
+ }
+ else
+ {
+ PQExpBufferData title;
+
+ initPQExpBuffer(&title);
+
+ appendPQExpBuffer(&title, "%s", gettext(tabletitle));
+
+ 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 4ff1f91f381..e3c6dda0213 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);
+
/* \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 eb3a0f36d94..32ab0cf525a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -169,7 +169,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"));
@@ -251,6 +251,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/po/es.po b/src/bin/psql/po/es.po
index 0765be4d6d6..a77c3539dff 100644
--- a/src/bin/psql/po/es.po
+++ b/src/bin/psql/po/es.po
@@ -12,7 +12,7 @@ msgstr ""
"Project-Id-Version: psql (PostgreSQL) 10\n"
"Report-Msgid-Bugs-To: pgsql-b...@postgresql.org\n"
"POT-Creation-Date: 2017-07-05 15:15+0000\n"
-"PO-Revision-Date: 2017-07-10 12:14-0400\n"
+"PO-Revision-Date: 2019-04-02 16:39-0300\n"
"Last-Translator: Álvaro Herrera <alvhe...@alvh.no-ip.org>\n"
"Language-Team: PgSQL Español <pgsql-es-ay...@postgresql.org>\n"
"Language: es\n"
@@ -21,6 +21,12 @@ msgstr ""
"Content-Transfer-Encoding: 8bit\n"
"Plural-Forms: nplurals=2; plural=n != 1;\n"
+msgid "partitioned table"
+msgstr "table particionada"
+
+msgid "partitioned index"
+msgstr "índice particionado"
+
#: ../../common/exec.c:127 ../../common/exec.c:241 ../../common/exec.c:284
#, c-format
msgid "could not identify current directory: %s"
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d34bf86fc20..8c46d44a9fa 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",
@@ -3525,6 +3543,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 aa101de9063..ad6958f9b08 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4578,3 +4578,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 tables and 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 tables and 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 tables
+ 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 tables
+ 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 tables and 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 fb7d17fc76e..65c30d60e24 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1031,3 +1031,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;
