Thanks a lot Greg!
> Changes look good to me, thanks. Can you make a new patch that applies a
single set of changes to HEAD?
Please find attached the diff to Head in a single file.
Best Regards,
Sadeq Dousti
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cedccc14129..37b516fc558 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1636,6 +1636,7 @@ SELECT $1 \parse stmt1
<term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1643,15 +1644,16 @@ SELECT $1 \parse stmt1
<listitem>
<para>
In this group of commands, the letters <literal>E</literal>,
- <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
- <literal>t</literal>, and <literal>v</literal>
- stand for foreign table, index, materialized view,
+ <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+ <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+ stand for foreign table, index, materialized view, no partitions,
sequence, table, and view,
respectively.
You can specify any or all of
these letters, in any order, to obtain a listing of objects
of these types. For example, <literal>\dti</literal> lists
- tables and indexes.
+ tables and indexes, and <literal>\dNt</literal> lists
+ tables that are not partitions of any other relation.
If <literal>x</literal> is appended to the command name, the results
are displayed in expanded mode.
If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..cf65df42459 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1163,6 +1163,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'i':
case 's':
case 'E':
+ case 'N':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..d4be468de55 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4016,6 +4016,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
bool showMatViews = strchr(tabtypes, 'm') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showForeign = strchr(tabtypes, 'E') != NULL;
+ bool showNoPartitions = strchr(tabtypes, 'N') != NULL;
int ntypes;
PQExpBufferData buf;
@@ -4024,12 +4025,31 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
int cols_so_far;
bool translate_columns[] = {false, false, true, false, false, false, false, false, false};
+ /*
+ * Note: Declarative table partitioning is only supported as of Pg 10.0.
+ */
+ if (showNoPartitions && 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;
+ }
+
/* Count the number of explicitly-requested relation types */
ntypes = showTables + showIndexes + showViews + showMatViews +
showSeq + showForeign;
- /* If none, we default to \dtvmsE (but see also command.c) */
+
if (ntypes == 0)
- showTables = showViews = showMatViews = showSeq = showForeign = true;
+ {
+ if (showNoPartitions)
+ showTables = showIndexes = true;
+ else
+ /* If none, we default to \dtvmsE (but see also command.c) */
+ showTables = showViews = showMatViews = showSeq = showForeign = true;
+ }
initPQExpBuffer(&buf);
@@ -4155,6 +4175,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" AND n.nspname !~ '^pg_toast'\n"
" AND n.nspname <> 'information_schema'\n");
+ if (showNoPartitions)
+ appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
if (!validateSQLNamePattern(&buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4228,8 +4251,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
else
{
myopt.title =
+ (ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
(ntypes != 1) ? _("List of relations") :
+ (showTables && showNoPartitions) ? _("List of tables (no partitions)") :
(showTables) ? _("List of tables") :
+ (showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
(showIndexes) ? _("List of indexes") :
(showViews) ? _("List of views") :
(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..09f98da98f2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -259,6 +259,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dL[Sx+] [PATTERN] list procedural languages\n");
HELP0(" \\dm[Sx+] [PATTERN] list materialized views\n");
HELP0(" \\dn[Sx+] [PATTERN] list schemas\n");
+ HELP0(" \\dN[Sx+] [PATTERN] list tables and indexes (no partitions)\n");
HELP0(" \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
" list operators\n");
HELP0(" \\dO[Sx+] [PATTERN] list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..33ac14f10e2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
.result = "c.relname",
};
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
static const SchemaQuery Query_for_list_of_partitioned_tables = {
.catname = "pg_catalog.pg_class c",
.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
.result = "c.relname",
};
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+ CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
static const SchemaQuery Query_for_list_of_partitioned_indexes = {
.catname = "pg_catalog.pg_class c",
.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
.result = "c.relname",
};
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+ CppAsString2(RELKIND_INDEX) ", "
+ CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
/* partitioned relations */
static const SchemaQuery Query_for_list_of_partitioned_relations = {
.catname = "pg_catalog.pg_class c",
@@ -1881,7 +1916,7 @@ psql_completion(const char *text, int start, int end)
"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
- "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
+ "\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5272,6 +5307,8 @@ match_previous_words(int pattern_id,
else if (TailMatchesCS("\\dF*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
+ else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
else if (TailMatchesCS("\\di*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
else if (TailMatchesCS("\\dL*"))
@@ -5295,6 +5332,8 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
else if (TailMatchesCS("\\ds*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+ else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
else if (TailMatchesCS("\\dt*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (TailMatchesCS("\\dT*"))
@@ -5317,6 +5356,8 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
/* must be at end of \d alternatives: */
+ else if (TailMatchesCS("\\dN*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
else if (TailMatchesCS("\\d*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..9394a677248 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,47 @@ create index testpart_orange_index on testpart_orange(logdate);
testpart | testpart_apple_index | regress_partitioning_role | | testpart_apple
(1 row)
+-- only non-partition relations should be displayed
+\dN
+ List of relations (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple | partitioned table | regress_partitioning_role |
+ testpart | testpart_apple_index | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange | partitioned table | regress_partitioning_role |
+ testpart | testpart_orange_index | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple | table | regress_partitioning_role |
+ testpart | testtable_apple_index | index | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange | table | regress_partitioning_role |
+ testpart | testtable_orange_index | index | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+ List of relations (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple | partitioned table | regress_partitioning_role |
+ testpart | testpart_apple_index | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple | table | regress_partitioning_role |
+ testpart | testtable_apple_index | index | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+ List of tables (no partitions)
+ Schema | Name | Type | Owner
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+ List of indexes (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index | regress_partitioning_role | testtable_apple
+(2 rows)
+
drop table testtable_apple;
drop table testtable_orange;
drop table testpart_apple;
@@ -5038,6 +5079,7 @@ create table child_30_35 partition of child_30_40
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));
+-- only partition related object should be displayed
\dPt
List of partitioned tables
Schema | Name | Owner
@@ -5106,6 +5148,45 @@ insert into parent_tab values (generate_series(30,39));
testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
+-- only non-partition relations should be displayed
+\dNt
+ List of tables (no partitions)
+ Schema | Name | Type | Owner
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+ List of indexes (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+ List of relations (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab | partitioned table | regress_partitioning_role |
+(2 rows)
+
+\dN
+ List of relations (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab | partitioned table | regress_partitioning_role |
+(2 rows)
+
+\dN testpart.*
+ List of relations (no partitions)
+ Schema | Name | Type | Owner | Table
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab | partitioned table | regress_partitioning_role |
+(2 rows)
+
drop table parent_tab cascade;
drop schema testpart;
set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..2f0f7bd1dc0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,12 @@ create index testpart_orange_index on testpart_orange(logdate);
\dPt test*apple*
\dPi test*apple*
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
drop table testtable_apple;
drop table testtable_orange;
drop table testpart_apple;
@@ -1280,6 +1286,7 @@ 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));
+-- only partition related object should be displayed
\dPt
\dPi
@@ -1291,6 +1298,14 @@ insert into parent_tab values (generate_series(30,39));
\dPn
\dPn testpart.*
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
drop table parent_tab cascade;
drop schema testpart;
