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 <[email protected]>
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 <[email protected]>
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
