On 2017/07/08 14:12, Mark Kirkwood wrote: > On 07/07/17 19:54, Michael Banck wrote: >> On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: >>> On 07/07/17 13:29, Amit Langote wrote: >>>> Someone complained about this awhile back [1]. And then it came up again >>>> [2], where Noah appeared to take a stance that partitions should be >>>> visible in views / output of commands that list "tables". >>>> >>>> Although I too tend to prefer not filling up the \d output space by >>>> listing partitions (pg_class.relispartition = true relations), there >>>> wasn't perhaps enough push for creating a patch for that. If some >>>> committer is willing to consider such a patch, I can make one. >>> >>> Yeah, me too (clearly). However if the consensus is that all these >>> partition >>> tables *must* be shown in \d output, then I'd be happy if they were >>> identified as such rather than just 'table' (e.g 'partition table'). >> +1. >> >> Or maybe just 'partition' is enough if 'partition table' would widen the >> column output unnecessarily. > > Yeah, that is probably better (and 'partition table' is potentially > confusing as Robert pointed out).
So, it seems at least that showing "partition" as the Type of tables that are actually partitions is preferable. I created a patch (attached 0001) that implements that. It makes \d show any relations that have relispartition = true as of type "partition" or "foreign partition". With the patch: create table p (a int) partition by list (a); -- regular table as partition create table p1 partition of p for values in (1) -- foreign table as partition create foreign data wrapper dummy; create server dummy foreign data wrapper dummy; create foreign table p2 partition of p for values in (2) server dummy; -- partitioned table as partition create table p3 partition of p for values in (3) partition by list (a); \d List of relations Schema | Name | Type | Owner --------+------+-------------------+------- public | p | table | amit public | p1 | partition | amit public | p2 | foreign partition | amit public | p3 | partition | amit (4 rows) Also, there seems to be at least some preference for excluding partitions by default from the \d listing. Attached 0002 implements that. To enable showing partitions, the patch adds a new modifier '!' to \d (picked '!' from Robert's email on this thread [1]). With the patch: \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | p | table | amit (1 row) \d! List of relations Schema | Name | Type | Owner --------+------+-------------------+------- public | p | table | amit public | p1 | partition | amit public | p2 | foreign partition | amit public | p3 | partition | amit (4 rows) \d!+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------------------+-------+---------+------------- public | p | table | amit | 0 bytes | public | p1 | partition | amit | 0 bytes | public | p2 | foreign partition | amit | 0 bytes | public | p3 | partition | amit | 0 bytes | (4 rows) Thanks, Amit [1] https://www.postgresql.org/message-id/CA%2BTgmoYNPHFjY%2BObFF9%3DTbX%2BT6ez1FAU%2BsmGuXeoiOMasDc-0g%40mail.gmail.com
From c73da2fcfc81ffa351f96be000ae5d262d828ae1 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Mon, 10 Jul 2017 13:25:20 +0900 Subject: [PATCH 1/2] Show "(foreign) partition" as Type in \d output --- src/bin/psql/describe.c | 48 +++++++++++++++++++++++++++++++++++++++--------- 1 file changed, 39 insertions(+), 9 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index e6833eced5..bbdac8d50d 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3321,27 +3321,57 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" - " CASE c.relkind" - " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'" + " CASE c.relkind", + gettext_noop("Schema"), + gettext_noop("Name")); + + /* + * Starting in PG 10, certain kinds of relations could be partitions, which + * if so, we show Type accordingly. + */ + if (pset.sversion >= 100000) + appendPQExpBuffer(&buf, + " WHEN " CppAsString2(RELKIND_RELATION) " THEN" + " CASE c.relispartition" + " WHEN 'true' THEN '%s' ELSE '%s'" + " END" + " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN" + " CASE c.relispartition" + " WHEN 'true' THEN '%s' ELSE '%s'" + " END" + " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN" + " CASE c.relispartition" + " WHEN 'true' THEN '%s' ELSE '%s'" + " END", + gettext_noop("partition"), + gettext_noop("table"), + gettext_noop("partition"), /* partitioned table + * that's a partition */ + gettext_noop("table"), /* partitioned table */ + gettext_noop("foreign partition"), + gettext_noop("foreign table")); + else + appendPQExpBuffer(&buf, + " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'" + " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'" + " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'", + gettext_noop("table"), + gettext_noop("table"), /* partitioned table */ + gettext_noop("foreign table")); + + appendPQExpBuffer(&buf, " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'" " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'" " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'" " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'" " WHEN 's' THEN '%s'" - " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'" - " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'" " END as \"%s\",\n" " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", - gettext_noop("Schema"), - gettext_noop("Name"), - gettext_noop("table"), gettext_noop("view"), gettext_noop("materialized view"), gettext_noop("index"), gettext_noop("sequence"), gettext_noop("special"), - gettext_noop("foreign table"), - gettext_noop("table"), /* partitioned table */ gettext_noop("Type"), gettext_noop("Owner")); -- 2.11.0
From 14974f272eaa5e9e6a0ae0862d6d32ca02196efb Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Mon, 10 Jul 2017 13:57:47 +0900 Subject: [PATCH 2/2] Exclude partitions by default from the the psql \d listing Add a new modifier '!' to \d to request listing partitions. --- doc/src/sgml/ref/psql-ref.sgml | 14 +++++++++----- src/bin/psql/command.c | 14 ++++++++++---- src/bin/psql/describe.c | 12 ++++++++++-- src/bin/psql/describe.h | 4 ++-- src/bin/psql/help.c | 12 ++++++------ 5 files changed, 37 insertions(+), 19 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index c592edac60..fef1aba963 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1096,7 +1096,7 @@ testdb=> <varlistentry> - <term><literal>\d[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + <term><literal>\d[S+!] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <listitem> <para> @@ -1132,7 +1132,9 @@ testdb=> <para> By default, only user-created objects are shown; supply a pattern or the <literal>S</literal> modifier to include system - objects. + objects. Also, by default, only non-partition objects are shown; + supply a pattern or the <literal>!</literal> modifier to include + partitions. </para> <note> @@ -1296,11 +1298,11 @@ testdb=> <varlistentry> - <term><literal>\dE[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + <term><literal>\dE[S+!] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <term><literal>\di[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <term><literal>\dm[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <term><literal>\ds[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> - <term><literal>\dt[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + <term><literal>\dt[S+!] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <term><literal>\dv[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> <listitem> @@ -1320,7 +1322,9 @@ testdb=> specified, only objects whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the <literal>S</literal> modifier to include system - objects. + objects. Also, by default, only non-partition objects are shown; + supply a pattern or the <literal>!</literal> modifier to include + partitions. </para> </listitem> </varlistentry> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 14c64208ca..9c90414b49 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -703,7 +703,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { char *pattern; bool show_verbose, - show_system; + show_system, + show_partitions; /* We don't do SQLID reduction on the pattern yet */ pattern = psql_scan_slash_option(scan_state, @@ -711,17 +712,21 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) show_verbose = strchr(cmd, '+') ? true : false; show_system = strchr(cmd, 'S') ? true : false; + show_partitions = strchr(cmd, '!') ? true : false; switch (cmd[1]) { case '\0': case '+': case 'S': + case '!': if (pattern) - success = describeTableDetails(pattern, show_verbose, show_system); + success = describeTableDetails(pattern, show_verbose, show_system, + show_partitions); else /* standard listing of interesting things */ - success = listTables("tvmsE", NULL, show_verbose, show_system); + success = listTables("tvmsE", NULL, show_verbose, show_system, + show_partitions); break; case 'A': success = describeAccessMethods(pattern, show_verbose); @@ -795,7 +800,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'i': case 's': case 'E': - success = listTables(&cmd[1], pattern, show_verbose, show_system); + success = listTables(&cmd[1], pattern, show_verbose, show_system, + show_partitions); break; case 'r': if (cmd[2] == 'd' && cmd[3] == 's') diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index bbdac8d50d..abc569442f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1284,7 +1284,8 @@ objectDescription(const char *pattern, bool showSystem) * verbose: if true, this is \d+ */ bool -describeTableDetails(const char *pattern, bool verbose, bool showSystem) +describeTableDetails(const char *pattern, bool verbose, bool showSystem, + bool showPartitions) { PQExpBufferData buf; PGresult *res; @@ -1303,6 +1304,9 @@ describeTableDetails(const char *pattern, bool verbose, bool showSystem) appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" " AND n.nspname <> 'information_schema'\n"); + if (pset.sversion >= 100000 && !showPartitions && !pattern) + appendPQExpBufferStr(&buf, " AND relispartition = false\n"); + processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, "n.nspname", "c.relname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); @@ -3294,7 +3298,8 @@ listDbRoleSettings(const char *pattern, const char *pattern2) * (any order of the above is fine) */ bool -listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem) +listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem, + bool showPartitions) { bool showTables = strchr(tabtypes, 't') != NULL; bool showIndexes = strchr(tabtypes, 'i') != NULL; @@ -3441,6 +3446,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys */ appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n"); + if (pset.sversion >= 100000 && !showPartitions) + appendPQExpBufferStr(&buf, " AND relispartition = 'false'\n"); + processSQLNamePattern(pset.db, &buf, pattern, true, false, "n.nspname", "c.relname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 14a5667f3e..f48c505798 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -43,7 +43,7 @@ extern bool listDefaultACLs(const char *pattern); extern bool objectDescription(const char *pattern, bool showSystem); /* \d foo */ -extern bool describeTableDetails(const char *pattern, bool verbose, bool showSystem); +extern bool describeTableDetails(const char *pattern, bool verbose, bool showSystem, bool showPartitions); /* \dF */ extern bool listTSConfigs(const char *pattern, bool verbose); @@ -61,7 +61,7 @@ extern bool listTSTemplates(const char *pattern, bool verbose); 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); +extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem, bool showPartitions); /* \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 b3dbb5946e..dc3c8e57cb 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -218,9 +218,9 @@ slashUsage(unsigned short int pager) fprintf(output, "\n"); fprintf(output, _("Informational\n")); - fprintf(output, _(" (options: S = show system objects, + = additional detail)\n")); - fprintf(output, _(" \\d[S+] list tables, views, and sequences\n")); - fprintf(output, _(" \\d[S+] NAME describe table, view, sequence, or index\n")); + fprintf(output, _(" (options: S = show system objects, + = additional detail, ! = show partitions)\n")); + fprintf(output, _(" \\d[S+!] list tables, views, and sequences\n")); + fprintf(output, _(" \\d[S+!] NAME describe table, view, sequence, or index\n")); fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n")); fprintf(output, _(" \\dA[+] [PATTERN] list access methods\n")); fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n")); @@ -229,8 +229,8 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dd[S] [PATTERN] show object descriptions not displayed elsewhere\n")); fprintf(output, _(" \\dD[S+] [PATTERN] list domains\n")); fprintf(output, _(" \\ddp [PATTERN] list default privileges\n")); - fprintf(output, _(" \\dE[S+] [PATTERN] list foreign tables\n")); - fprintf(output, _(" \\det[+] [PATTERN] list foreign tables\n")); + fprintf(output, _(" \\dE[S+!] [PATTERN] list foreign tables\n")); + fprintf(output, _(" \\det[+!] [PATTERN] list foreign tables\n")); fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n")); fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n")); fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data wrappers\n")); @@ -252,7 +252,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dRp[+] [PATTERN] list replication publications\n")); 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, _(" \\dt[S+!] [PATTERN] list 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")); -- 2.11.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers