New partitioning - some feedback
I've been trying out the new partitioning in version 10. Firstly, I must
say this is excellent - so much nicer than the old inheritance based method!
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)
Now it can be inferred from the names that date_fact is a partitioned
table and the various date_fact_dddd are its partitions - but \d is not
providing any hints of this. The more detailed individual describe is fine:
part=# \d date_fact
Table "public.date_fact"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
id | integer | | not null |
dte | timestamp with time zone | | not null |
val | integer | | not null |
Partition key: RANGE (dte)
Number of partitions: 6 (Use \d+ to list them.)
I'd prefer *not* to see a table and its partitions all intermixed in the
same display (especially with nothing indicating which are partitions) -
as this will make for unwieldy long lists when tables have many
partitions. Also it would be good if the 'main' partitioned table and
its 'partitions' showed up as a different type in some way.
I note the they do in pg_class:
part=# SELECT relname,relkind,relispartition FROM pg_class WHERE relname
LIKE 'date_fact%';
relname | relkind | relispartition
----------------------+---------+----------------
date_fact | p | f
date_fact_201705 | r | t
date_fact_201706 | r | t
date_fact_20170601 | r | t
date_fact_2017060100 | r | t
date_fact_201707 | r | t
date_fact_rest | r | t
(7 rows)
...so it looks to be possible to hide the partitions from the main
display and/or mark them as such. Now I realize that making this comment
now that beta is out is a bit annoying - apologies, but I think seeing a
huge list of 'tables' is going to make \d frustrating for folk doing
partitioning.
regards
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi Mark,
On 2017/07/07 9:02, Mark Kirkwood wrote:
I've been trying out the new partitioning in version 10. Firstly, I must
say this is excellent - so much nicer than the old inheritance based method!
Thanks. :)
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)Now it can be inferred from the names that date_fact is a partitioned
table and the various date_fact_dddd are its partitions - but \d is not
providing any hints of this. The more detailed individual describe is fine:part=# \d date_fact
Table "public.date_fact"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
id | integer | | not null |
dte | timestamp with time zone | | not null |
val | integer | | not null |
Partition key: RANGE (dte)
Number of partitions: 6 (Use \d+ to list them.)I'd prefer *not* to see a table and its partitions all intermixed in the
same display (especially with nothing indicating which are partitions) -
as this will make for unwieldy long lists when tables have many
partitions. Also it would be good if the 'main' partitioned table and its
'partitions' showed up as a different type in some way.
I note the they do in pg_class:part=# SELECT relname,relkind,relispartition FROM pg_class WHERE relname
LIKE 'date_fact%';
relname | relkind | relispartition
----------------------+---------+----------------
date_fact | p | f
date_fact_201705 | r | t
date_fact_201706 | r | t
date_fact_20170601 | r | t
date_fact_2017060100 | r | t
date_fact_201707 | r | t
date_fact_rest | r | t
(7 rows)...so it looks to be possible to hide the partitions from the main display
and/or mark them as such. Now I realize that making this comment now that
beta is out is a bit annoying - apologies, but I think seeing a huge list
of 'tables' is going to make \d frustrating for folk doing partitioning.
Someone complained about this awhile back [1]/messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com. And then it came up again
[2]: /messages/by-id/20170406070227.GA2741046@tornado.leadboat.com
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.
Thanks,
Amit
[1]: /messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com
/messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com
[2]: /messages/by-id/20170406070227.GA2741046@tornado.leadboat.com
/messages/by-id/20170406070227.GA2741046@tornado.leadboat.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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').
regards
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 July 2017 at 08:54, Michael Banck <michael.banck@credativ.de> 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.
+1 to remove partitions from \d display
With 1000 partitions that would just be annoying
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jul 7, 2017 at 3:54 AM, Michael Banck <michael.banck@credativ.de> wrote:
+1.
Or maybe just 'partition' is enough if 'partition table' would widen the
column output unnecessarily.
Internally to the source code, the parent is called a "partitioned
table" and the child is called a "partition". I think we should not
use the term "partition table" because I think it could create
confusion as to which of those two things we're talking about. It
would be reasonable to write "partition" rather than "table" for
partitions, though. We'd probably also need "partition index" (for
indexes on partition) and "foreign partition" (for foreign tables that
are partitions).
I don't have a strong view on whether partitions should be hidden by
default, although I lean slightly against it (say, -0.25). But if we
do decide to hide them by default, then I definitely want an
easy-to-use modifier that overrides that behavior, like being able to
type \d! or whatever to have them included after all.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
I don't have a strong view on whether partitions should be hidden by
default, although I lean slightly against it (say, -0.25). But if we
do decide to hide them by default, then I definitely want an
easy-to-use modifier that overrides that behavior, like being able to
type \d! or whatever to have them included after all.
AIUI the user is responsible for DDL on partitions, like say creating
indexes for them? Seems like hiding them is a bad idea given that.
Also, we need to be careful about calling them something very separate
from "table", because that would rouse the need to have duplicate syntax
for every sort of ALTER TABLE and suchlike command that we want to have
be usable with partitions. I think we've largely gone the wrong direction
in that respect with respect to foreign tables and matviews.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 July 2017 at 13:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't have a strong view on whether partitions should be hidden by
default, although I lean slightly against it (say, -0.25). But if we
do decide to hide them by default, then I definitely want an
easy-to-use modifier that overrides that behavior, like being able to
type \d! or whatever to have them included after all.AIUI the user is responsible for DDL on partitions, like say creating
indexes for them? Seems like hiding them is a bad idea given that.
Also, we need to be careful about calling them something very separate
from "table", because that would rouse the need to have duplicate syntax
for every sort of ALTER TABLE and suchlike command that we want to have
be usable with partitions. I think we've largely gone the wrong direction
in that respect with respect to foreign tables and matviews.
Hmm, "hiding" would not be an accurate description of the proposal. I
would characterize it more as removing extraneous information, since
for a partitioned table seeing 1000 records all with roughly the same
name isn't helpful output from \d
\d would show tables but not partitions
\d <tablename> would show partitions exist and how many
\d+ would show partition details
So the information would be available, just at different levels of
detail, just as we have now for other things.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jul 7, 2017 at 8:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't have a strong view on whether partitions should be hidden by
default, although I lean slightly against it (say, -0.25). But if we
do decide to hide them by default, then I definitely want an
easy-to-use modifier that overrides that behavior, like being able to
type \d! or whatever to have them included after all.AIUI the user is responsible for DDL on partitions, like say creating
indexes for them? Seems like hiding them is a bad idea given that.
Also, we need to be careful about calling them something very separate
from "table", because that would rouse the need to have duplicate syntax
for every sort of ALTER TABLE and suchlike command that we want to have
be usable with partitions. I think we've largely gone the wrong direction
in that respect with respect to foreign tables and matviews.
Well, I'm not sure what other direction we could have taken there, and
I don't think it follows that just because it's labeled differently in
\d output it has to have different SQL syntax.
On the core question of whether they should be hidden, I think the
answer is that it depends on the situation. As Simon says, if people
use partitioning with large numbers of partitions, listing many
nearly-identical partition names clutters up the list to an extent
that makes life quite difficult; I've encountered this as a real
usability problem on actual systems. On the other hand, people with
more modest numbers of partitions (say, 10) might well find it more
convenient to see those names included, because they're legitimate
targets for commands like COMMENT and DROP TABLE and lots of other
things, and somebody might very well find it convenient to be able to
get that with \d rather than \d+ parent_table_name.
As I say, I don't feel hugely strongly about the default behavior, but
I do feel strongly that the idea that only one of the two proposed
behavior is useful is entirely incorrect.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote:
Hi Mark,
On 2017/07/07 9:02, Mark Kirkwood wrote:
I've been trying out the new partitioning in version 10. Firstly, I must
say this is excellent - so much nicer than the old inheritance based method!Thanks. :)
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)
Would showing relispartition=tru tables only in \d+ fix this?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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).
Cheers
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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]/messages/by-id/CA+TgmoYNPHFjY+ObFF9=TbX+T6ez1FAU+smGuXeoiOMasDc-0g@mail.gmail.com). 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]: /messages/by-id/CA+TgmoYNPHFjY+ObFF9=TbX+T6ez1FAU+smGuXeoiOMasDc-0g@mail.gmail.com
/messages/by-id/CA+TgmoYNPHFjY+ObFF9=TbX+T6ez1FAU+smGuXeoiOMasDc-0g@mail.gmail.com
Attachments:
0001-Show-foreign-partition-as-Type-in-d-output.patchtext/plain; charset=UTF-8; name=0001-Show-foreign-partition-as-Type-in-d-output.patchDownload
From c73da2fcfc81ffa351f96be000ae5d262d828ae1 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@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
0002-Exclude-partitions-by-default-from-the-the-psql-d-li.patchtext/plain; charset=UTF-8; name=0002-Exclude-partitions-by-default-from-the-the-psql-d-li.patchDownload
From 14974f272eaa5e9e6a0ae0862d6d32ca02196efb Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@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
On 8 July 2017 at 00:03, David Fetter <david@fetter.org> wrote:
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote:
Hi Mark,
On 2017/07/07 9:02, Mark Kirkwood wrote:
I've been trying out the new partitioning in version 10. Firstly, I
must
say this is excellent - so much nicer than the old inheritance based
method!
Thanks. :)
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)Would showing relispartition=tru tables only in \d+ fix this?
<http://www.postgresql.org/mailpref/pgsql-hackers>
I think so.
I'd like to add a flag of some kind to \d column output that marks a table
as having partitions, but I can't think of anything narrow enough and still
useful.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 2017/07/10 15:32, Craig Ringer wrote:
On 8 July 2017 at 00:03, David Fetter <david@fetter.org> wrote:
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote:
Hi Mark,
On 2017/07/07 9:02, Mark Kirkwood wrote:
I've been trying out the new partitioning in version 10. Firstly, I
must
say this is excellent - so much nicer than the old inheritance based
method!
Thanks. :)
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)Would showing relispartition=tru tables only in \d+ fix this?
<http://www.postgresql.org/mailpref/pgsql-hackers>I think so.
I posted a patch upthread which makes \d hide partitions (relispartition =
true relations) and include them if the newly proposed '!' modifier is
specified. The '+' modifier is being used to show additional detail of
relations chosen to be listed at all, so it seemed like a bad idea to
extend its meaning to also dictate whether partitions are to be listed.
We have a separate 'S' modifier to ask to list system objects (which are,
by default hidden), so it made sense to me to add yet another modifier
(aforementioned '!') for partitions.
I'd like to add a flag of some kind to \d column output that marks a table
as having partitions, but I can't think of anything narrow enough and still
useful.
Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions. But we've avoided using that term ("partitioned
table") in the error messages and such, so wouldn't perhaps be a good idea
to do that here. But I wonder if we (also) want to distinguish
partitioned tables from regular tables? I understood that there is some
desire for partitions be distinguished when they are listed in the output,
either by default or by using a modifier.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jul 10, 2017 at 04:15:28PM +0900, Amit Langote wrote:
On 2017/07/10 15:32, Craig Ringer wrote:
On 8 July 2017 at 00:03, David Fetter <david@fetter.org> wrote:
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote:
Hi Mark,
On 2017/07/07 9:02, Mark Kirkwood wrote:
I've been trying out the new partitioning in version 10. Firstly, I
must
say this is excellent - so much nicer than the old inheritance based
method!
Thanks. :)
My only niggle is the display of partitioned tables via \d etc. e.g:
part=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | date_fact | table | postgres
public | date_fact_201705 | table | postgres
public | date_fact_201706 | table | postgres
public | date_fact_20170601 | table | postgres
public | date_fact_2017060100 | table | postgres
public | date_fact_201707 | table | postgres
public | date_fact_rest | table | postgres
(7 rows)Would showing relispartition=tru tables only in \d+ fix this?
<http://www.postgresql.org/mailpref/pgsql-hackers>I think so.
I posted a patch upthread which makes \d hide partitions (relispartition =
true relations) and include them if the newly proposed '!' modifier is
specified. The '+' modifier is being used to show additional detail of
relations chosen to be listed at all, so it seemed like a bad idea to
extend its meaning to also dictate whether partitions are to be listed.
We have a separate 'S' modifier to ask to list system objects (which are,
by default hidden), so it made sense to me to add yet another modifier
(aforementioned '!') for partitions.
We have already made '+' signal "more detail, unspecified," for a lot
of different cases. If partitions are just "more detail" about a
table, which is the direction we've decided to go, it makes sense to
list them under the rubric of '+' rather than inventing yet another
hunk of syntax to psql's already confusing \ commands.
I'd like to add a flag of some kind to \d column output that marks a table
as having partitions, but I can't think of anything narrow enough and still
useful.Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions. But we've avoided using that term ("partitioned
table") in the error messages and such, so wouldn't perhaps be a good idea
to do that here. But I wonder if we (also) want to distinguish
partitioned tables from regular tables? I understood that there is some
desire for partitions be distinguished when they are listed in the output,
either by default or by using a modifier.
+1 for showing that they're a different beast.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
I posted a patch upthread which makes \d hide partitions (relispartition =
true relations) and include them if the newly proposed '!' modifier is
specified. The '+' modifier is being used to show additional detail of
relations chosen to be listed at all, so it seemed like a bad idea to
extend its meaning to also dictate whether partitions are to be listed.
+1. That'd be a mess.
Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.
I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:I posted a patch upthread which makes \d hide partitions
(relispartition = true relations) and include them if the newly
proposed '!' modifier is specified. The '+' modifier is being
used to show additional detail of relations chosen to be listed at
all, so it seemed like a bad idea to extend its meaning to also
dictate whether partitions are to be listed.+1. That'd be a mess.
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?
Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of
Type "partitioned table", we wouldn't need a separate flag for
marking a table as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.
So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10 July 2017 at 23:46, David Fetter <david@fetter.org> wrote:
On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:I posted a patch upthread which makes \d hide partitions
(relispartition = true relations) and include them if the newly
proposed '!' modifier is specified. The '+' modifier is being
used to show additional detail of relations chosen to be listed at
all, so it seemed like a bad idea to extend its meaning to also
dictate whether partitions are to be listed.+1. That'd be a mess.
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?
Fwiw as, I believe, the first person to make this complaint I would be
fine with + listing all partitions. Imho adding an orthogonal "!"
would be too much mental overhead for the user.
If you want something different perhaps we can invent ++ for "even
more information" and list partitions only if two plusses are
provided. (I don't think the other way around makes sense since you
might need a way to list permissions and comments without listing
every partition if you're on a system with an unmanageable number of
partitions but you never absolutely need a way to list partitions
without the comments and permissions). At least that doesn't require
the user to learn a new flag and how it interacts with everything
else.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/11 7:33, Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.
I meant to speak of RELKIND_PARTITIONED_TABLE tables as having partitions
(although it could be a partition itself too). If based on the relkind,
we had shown their type as "partitioned table" (not just "table"), then we
wouldn't need a separate flag/column in the \d output to distinguish
partitioned tables as being different from regular tables, as Craig seemed
to be proposing.
Since we are going the route of showing relispartition = true relations as
of different type in the \d listing (as "partition"/"foreign partition"),
we might as well go and spell RELKIND_PARTITIONED_TABLE tables as
"partitioned table". But, I'm afraid that it would be a much bigger
change if we don't want to restrict this terminology change to \d listing;
error messages don't bother about distinguishing "partitions"
(relispartition = true) or "partitioned tables"
(RELKIND_PARTITIONED_TABLE), for instance.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Also, there seems to be at least some preference
for excluding partitions by default from the \d listing.
As another user of partitions I'll chime in and say that would be very
nice! On the other hand, with pre-10 partitions you do see all the
child tables with `\d`, so showing declarative partitions seems more
consistent with the old functionality.
On the third hand with pre-10 partitions I can put the child tables
into a separate schema to de-clutter `\d`, but I don't see any way to
do that with declarative partitions. Since there is no workaround it
makes it a bit more important for partitions not to be so noisy.
Paul
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/11 10:34, Paul A Jungwirth wrote:
Also, there seems to be at least some preference
for excluding partitions by default from the \d listing.As another user of partitions I'll chime in and say that would be very
nice! On the other hand, with pre-10 partitions you do see all the
child tables with `\d`, so showing declarative partitions seems more
consistent with the old functionality.
That's one way of looking at it. :)
On the third hand with pre-10 partitions I can put the child tables
into a separate schema to de-clutter `\d`, but I don't see any way to
do that with declarative partitions. Since there is no workaround it
makes it a bit more important for partitions not to be so noisy.
You can do that with declarative partitions:
create table foo (a int) partition by list (a);
create schema foo_parts;
create table foo_parts.foo1 partition of foo for values in (1);
create table foo_parts.foo2 partition of foo for values in (2);
\d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | amit
(1 row)
set search_path to foo_parts;
\d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+-------
foo_parts | foo1 | table | amit
foo_parts | foo2 | table | amit
(2 rows)
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.
Maybe this discussion is easier if we differentiate "list tables" (\dt,
or \d without a pattern) from "describe table" (\d with a name pattern).
It seems to me that the "describe" command should list partitions --
perhaps only when the + flag is given. However, the "list tables"
command \dt should definitely IMO not list partitions. Maybe \dt should
have some flag indicating whether each table is partitioned.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter <david@fetter.org> wrote:
On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:I posted a patch upthread which makes \d hide partitions
(relispartition = true relations) and include them if the newly
proposed '!' modifier is specified. The '+' modifier is being
used to show additional detail of relations chosen to be listed at
all, so it seemed like a bad idea to extend its meaning to also
dictate whether partitions are to be listed.+1. That'd be a mess.
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of
Type "partitioned table", we wouldn't need a separate flag for
marking a table as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?
We have patches proposed to address some of those concerns at [1]/messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
[1]: /messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/11 18:57, Ashutosh Bapat wrote:
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter <david@fetter.org> wrote:
So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?We have patches proposed to address some of those concerns at [1]
[1] /messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
ISTM, David is talking about the "list tables" (bare \d without any
pattern) case. That is, listing partitioned tables as of type
"partitioned table" instead of "table" as we currently do. The linked
patch, OTOH, is for "describe table" (\d <object_name_pattern>) case.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/11 13:34, Alvaro Herrera wrote:
Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.Maybe this discussion is easier if we differentiate "list tables" (\dt,
or \d without a pattern) from "describe table" (\d with a name pattern).
I think this discussion has mostly focused on "list tables" so far.
It seems to me that the "describe" command should list partitions --
perhaps only when the + flag is given.
That's what happens today.
However, the "list tables"
command \dt should definitely IMO not list partitions.
Do you mean never? Even if a modifier is specified? In the patch I
proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
partitions, but \d or \dt won't. That is, partitions are hidden by default.
Maybe \dt should
have some flag indicating whether each table is partitioned.
So it seems most of us are in favor for showing partitioned tables as
"partitioned table" instead of "table" in the table listing.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
On 2017/07/11 18:57, Ashutosh Bapat wrote:
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter <david@fetter.org> wrote:
So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?We have patches proposed to address some of those concerns at [1]
[1] /messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
ISTM, David is talking about the "list tables" (bare \d without any
pattern) case. That is, listing partitioned tables as of type
"partitioned table" instead of "table" as we currently do. The linked
patch, OTOH, is for "describe table" (\d <object_name_pattern>) case.
Right, the patches don't exactly do what David is suggesting, but
those I believe have code to annotate the tables with "has partitions"
and also the number of partitions (I guess). Although, that thread has
died some time ago, so my memory can be vague.
Do you see that those patches can be used in current discussion in any way?
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/12 12:47, Ashutosh Bapat wrote:
On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:On 2017/07/11 18:57, Ashutosh Bapat wrote:
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter <david@fetter.org> wrote:
So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?We have patches proposed to address some of those concerns at [1]
[1] /messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
ISTM, David is talking about the "list tables" (bare \d without any
pattern) case. That is, listing partitioned tables as of type
"partitioned table" instead of "table" as we currently do. The linked
patch, OTOH, is for "describe table" (\d <object_name_pattern>) case.Right, the patches don't exactly do what David is suggesting, but
those I believe have code to annotate the tables with "has partitions"
and also the number of partitions (I guess). Although, that thread has
died some time ago, so my memory can be vague.Do you see that those patches can be used in current discussion in any way?
It wouldn't really be a bad idea to put that patch here, because there's
no special reason for it to be in the CF for PG 11, if we are talking here
about changing \d command outputs anyway.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/12 13:09, Amit Langote wrote:
On 2017/07/12 12:47, Ashutosh Bapat wrote:
Do you see that those patches can be used in current discussion in any way?
It wouldn't really be a bad idea to put that patch here, because there's
no special reason for it to be in the CF for PG 11, if we are talking here
about changing \d command outputs anyway.
So, here are 4 patches (including the 2 patches that Ashutosh linked to
upthread):
0001: Show relispartition=true relations as "(foreign) partition" and
RELKIND_PARTITIONED_TABLE relations that are not themselves
partitions as "partitioned table"
0002: Hide relispartition=true relations (partitions) by default in the
\d listing (that is, \d without a name pattern); to enable
displaying partitions, add a modifier '++'
0003: In \d+ partitioned_table output (describe partitioned table showing
individual partitions), show if the individual partitions are
partitioned themselves if it actually does have partitions
currently
0004: In \d+ partitioned_table output, do not skip the portion of the
output showing information about partitions if there are currently
no partitions defined; instead show "Number of partitions: 0"
Regarding 0001, while it shows "partition" and "partitioned table" in the
Type column of \d listing, \d name_pattern will still show Table
"schemaname.tablename". For example:
\d
List of relations
Schema | Name | Type | Owner
--------+-------+-------------------+-------
public | xyz | partitioned table | amit
public | xyz1 | partition | amit
public | xyz2 | partition | amit
public | xyz3 | partition | amit
public | xyz31 | partition | amit
(5 rows)
\d xyz*
Table "public.xyz"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Partition key: LIST (a)
Number of partitions: 3 (Use \d+ to list them.)
Table "public.xyz1"
<snip>
Table "public.xyz2"
<snip>
Table "public.xyz3"
<snip>
Table "public.xyz31"
<snip>
...which might seem kind of odd. Do we want to show xyz1 as "Partition
public.xyz1", for example?
Thanks,
Amit
Attachments:
0001-Show-partitions-and-partitioned-tables-as-such-in-d-.patchtext/plain; charset=UTF-8; name=0001-Show-partitions-and-partitioned-tables-as-such-in-d-.patchDownload
From ceacc566ab7ac2ffe56a47435a53a12ebafdffe5 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 10 Jul 2017 13:25:20 +0900
Subject: [PATCH 1/4] Show partitions and partitioned tables as such in \d
listing
---
src/bin/psql/describe.c | 51 ++++++++++++++++++++++++++++++++++++++++---------
1 file changed, 42 insertions(+), 9 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6833eced5..4613490f56 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3321,27 +3321,60 @@ 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"),
+ gettext_noop("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("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
0002-Exclude-partitions-by-default-from-the-the-psql-d-li.patchtext/plain; charset=UTF-8; name=0002-Exclude-partitions-by-default-from-the-the-psql-d-li.patchDownload
From 32e3a030fcc3642c8af3dfae0c40869f8377299b Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 10 Jul 2017 13:57:47 +0900
Subject: [PATCH 2/4] 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 | 13 +++++++++----
src/bin/psql/describe.c | 12 ++++++++++--
src/bin/psql/describe.h | 4 ++--
src/bin/psql/help.c | 12 ++++++------
5 files changed, 36 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c592edac60..8bb2f5bb0c 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..07a4e7d0ac 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,6 +712,7 @@ 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 = strstr(cmd, "++") ? true : false;
switch (cmd[1])
{
@@ -718,10 +720,12 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case '+':
case 'S':
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 +799,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 4613490f56..86adfb71b7 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;
@@ -3444,6 +3449,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..d0603f6436 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
0003-Indicate-whether-a-partition-is-itself-partitioned-i.patchtext/plain; charset=UTF-8; name=0003-Indicate-whether-a-partition-is-itself-partitioned-i.patchDownload
From adbe3b01da876ea506dbffedc7188c3ebfd46e42 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Wed, 12 Jul 2017 15:24:07 +0900
Subject: [PATCH 3/4] Indicate whether a partition is itself partitioned in \d+
output
That's done by appending a " has partitions" string to the line
describing an individual partition.
Authors: Amit Langote, Ashutosh Bapat
---
src/bin/psql/describe.c | 37 +++++++++++++++++++++++++-----------
src/test/regress/expected/insert.out | 15 +++++++++++++++
src/test/regress/sql/insert.sql | 4 ++++
3 files changed, 45 insertions(+), 11 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 86adfb71b7..faf69b95c6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2838,7 +2838,9 @@ describeOneTableDetails(const char *schemaname,
/* print child tables (with additional info if partitions) */
if (pset.sversion >= 100000)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)"
+ "SELECT c.oid::pg_catalog.regclass,"
+ " pg_get_expr(c.relpartbound, c.oid),"
+ " c.relkind"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND"
" i.inhparent = '%s' AND"
@@ -2863,21 +2865,25 @@ describeOneTableDetails(const char *schemaname,
if (!verbose)
{
+ const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
+ ? _("child tables")
+ : _("partitions");
+
/* print the number of child tables, if any */
if (tuples > 0)
{
- if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
- printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
- else
- printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
+ printfPQExpBuffer(&buf,
+ _("Number of %s: %d (Use \\d+ to list them.)"),
+ ct, tuples);
printTableAddFooter(&cont, buf.data);
}
}
else
{
/* display the list of child tables */
- const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ?
- _("Child tables") : _("Partitions");
+ const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
+ ? _("Child tables")
+ : _("Partitions");
int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
for (i = 0; i < tuples; i++)
@@ -2893,12 +2899,21 @@ describeOneTableDetails(const char *schemaname,
}
else
{
+ char *partitioned_note;
+
+ if (*PQgetvalue(result, i, 2) == RELKIND_PARTITIONED_TABLE)
+ partitioned_note = " has partitions";
+ else
+ partitioned_note = "";
+
if (i == 0)
- printfPQExpBuffer(&buf, "%s: %s %s",
- ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
+ printfPQExpBuffer(&buf, "%s: %s %s%s",
+ ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
+ partitioned_note);
else
- printfPQExpBuffer(&buf, "%*s %s %s",
- ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
+ printfPQExpBuffer(&buf, "%*s %s %s%s",
+ ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
+ partitioned_note);
}
if (i < tuples - 1)
appendPQExpBufferChar(&buf, ',');
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index d1153f410b..210a158521 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -314,6 +314,21 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
part_null | | 1 | 1
(9 rows)
+-- test \d+ output on a table which has both partitioned and unpartitioned
+-- partitions
+\d+ list_parted
+ Table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | | | plain | |
+Partition key: LIST (lower(a))
+Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'),
+ part_cc_dd FOR VALUES IN ('cc', 'dd'),
+ part_ee_ff FOR VALUES IN ('ee', 'ff') has partitions,
+ part_gg FOR VALUES IN ('gg') has partitions,
+ part_null FOR VALUES IN (NULL)
+
-- cleanup
drop table range_parted, list_parted;
-- more tests for certain multi-level partitioning scenarios
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 83c3ad8f53..3c67692ace 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -185,6 +185,10 @@ insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a);
insert into list_parted (b) values (1);
select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1;
+-- test \d+ output on a table which has both partitioned and unpartitioned
+-- partitions
+\d+ list_parted
+
-- cleanup
drop table range_parted, list_parted;
--
2.11.0
0004-Fix-d-output-for-yet-empty-partitioned-tables.patchtext/plain; charset=UTF-8; name=0004-Fix-d-output-for-yet-empty-partitioned-tables.patchDownload
From d42522fb19d74a4154d3310a667a82ca888e0bf9 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Wed, 12 Jul 2017 15:37:29 +0900
Subject: [PATCH 4/4] Fix \d+ output for yet empty partitioned tables
Currently it only shows the partition key line, but says nothing
about partitions. Instead add a line saying it has 0 partitions.
Authors: Amit Langote, Ashutosh Bapat
---
src/bin/psql/describe.c | 16 +++++++++++++++-
src/test/regress/expected/create_table.out | 13 ++++++++-----
src/test/regress/expected/foreign_data.out | 3 +++
src/test/regress/sql/create_table.sql | 2 +-
4 files changed, 27 insertions(+), 7 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index faf69b95c6..28b0f2b62a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2863,7 +2863,20 @@ describeOneTableDetails(const char *schemaname,
else
tuples = PQntuples(result);
- if (!verbose)
+ /*
+ * For a partitioned table with no partitions, always print the number
+ * of partitions as zero, even when verbose output is expected.
+ * Otherwise, we will not print "Partitions" section for a partitioned
+ * table without any partitions.
+ */
+ if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE && tuples == 0)
+ {
+
+ /* print the number of child tables, if any */
+ printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples);
+ printTableAddFooter(&cont, buf.data);
+ }
+ else if (!verbose)
{
const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
? _("child tables")
@@ -2921,6 +2934,7 @@ describeOneTableDetails(const char *schemaname,
printTableAddFooter(&cont, buf.data);
}
}
+
PQclear(result);
/* Table type */
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index b6f794e1c2..f6c6a514fa 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -428,13 +428,15 @@ ERROR: cannot inherit from partitioned table "partitioned2"
c | text | | |
d | text | | |
Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
+Number of partitions: 0
-\d partitioned2
- Table "public.partitioned2"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- a | integer | | |
+\d+ partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
Partition key: LIST ((a + 1))
+Number of partitions: 0
DROP TABLE partitioned, partitioned2;
--
@@ -773,5 +775,6 @@ SELECT obj_description('parted_col_comment'::regclass);
a | integer | | | | plain | | Partition key
b | text | | | | extended | |
Partition key: LIST (a)
+Number of partitions: 0
DROP TABLE parted_col_comment;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 7f2f529393..51da5e91e2 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1879,6 +1879,7 @@ DROP FOREIGN TABLE pt2_1;
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
+Number of partitions: 0
CREATE FOREIGN TABLE pt2_1 (
c1 integer NOT NULL,
@@ -1963,6 +1964,7 @@ ALTER TABLE pt2 ALTER c2 SET NOT NULL;
c2 | text | | not null | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
+Number of partitions: 0
\d+ pt2_1
Foreign table "public.pt2_1"
@@ -1992,6 +1994,7 @@ ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
Partition key: LIST (c1)
Check constraints:
"pt2chk1" CHECK (c1 > 0)
+Number of partitions: 0
\d+ pt2_1
Foreign table "public.pt2_1"
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index cb7aa5bbc6..80106dd7d9 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -421,7 +421,7 @@ CREATE TABLE fail () INHERITS (partitioned2);
-- Partition key in describe output
\d partitioned
-\d partitioned2
+\d+ partitioned2
DROP TABLE partitioned, partitioned2;
--
2.11.0
On Wed, Jul 12, 2017 at 9:39 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
On 2017/07/12 12:47, Ashutosh Bapat wrote:
On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:On 2017/07/11 18:57, Ashutosh Bapat wrote:
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter <david@fetter.org> wrote:
So whatever we land on needs to mention partition_of and
has_partitions. Is that latter just its immediate partitions?
Recursion all the way down? Somewhere in between?We have patches proposed to address some of those concerns at [1]
[1] /messages/by-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com
ISTM, David is talking about the "list tables" (bare \d without any
pattern) case. That is, listing partitioned tables as of type
"partitioned table" instead of "table" as we currently do. The linked
patch, OTOH, is for "describe table" (\d <object_name_pattern>) case.Right, the patches don't exactly do what David is suggesting, but
those I believe have code to annotate the tables with "has partitions"
and also the number of partitions (I guess). Although, that thread has
died some time ago, so my memory can be vague.Do you see that those patches can be used in current discussion in any way?
It wouldn't really be a bad idea to put that patch here, because there's
no special reason for it to be in the CF for PG 11, if we are talking here
about changing \d command outputs anyway.
Thanks.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Amit Langote wrote:
On 2017/07/11 13:34, Alvaro Herrera wrote:
Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.Maybe this discussion is easier if we differentiate "list tables" (\dt,
or \d without a pattern) from "describe table" (\d with a name pattern).I think this discussion has mostly focused on "list tables" so far.
Yes, which I think is a mistake, because for some things you definitely
need a list of partitions of the table in question. And "describe
table" can fulfill that role perfectly well, ISTM.
It seems to me that the "describe" command should list partitions --
perhaps only when the + flag is given.That's what happens today.
So no further changes needed there -- good.
However, the "list tables"
command \dt should definitely IMO not list partitions.Do you mean never? Even if a modifier is specified? In the patch I
proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
partitions, but \d or \dt won't. That is, partitions are hidden by default.
I don't think there is any need for a single list of all partition of
all tables -- is there? I can't think of anything, but then I haven't
been exposed very much to this feature yet. For now, I lean towards "never".
(A different consideration is the use case of listing relation
relfrozenxid/relminmxid ages, but that use case is already not fulfilled
by psql metacommands so you still need custom catalog queries).
I don't think \d! works terribly well as a mental model, but maybe
that's just me.
Maybe \dt should
have some flag indicating whether each table is partitioned.So it seems most of us are in favor for showing partitioned tables as
"partitioned table" instead of "table" in the table listing.
Yeah, that sounds good to me.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12 July 2017 at 15:58, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Amit Langote wrote:
On 2017/07/11 13:34, Alvaro Herrera wrote:
However, the "list tables"
command \dt should definitely IMO not list partitions.Do you mean never? Even if a modifier is specified? In the patch I
proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
partitions, but \d or \dt won't. That is, partitions are hidden by default.I don't think there is any need for a single list of all partition of
all tables -- is there? I can't think of anything, but then I haven't
been exposed very much to this feature yet. For now, I lean towards "never".
So just focusing on the listing issue for now...
I tend to agree with some of the upstream comments that a bare \d
should list everything, including partitions, because partitions are
still tables that you might want to do DML or DDL on.
Also, if you look at what we already have, \d lists all types of
relations, and then there are 2-letter commands \dE, \di, \dm, \ds,
\dt and \dv that list just specific kinds of relations, for example
\dE lists foreign tables, and \dt lists local tables, specifically
excluding foreign tables.
So ISTM that the most logical extension of that is:
\d - list all relations, including partitions
\dt - list only tables that are not foreign tables or partitions
of other tables
(that's not quite an exact extension of the existing logic, because of
course it's partitioned tables that have the different relkind, not
the partitions, but the above seems like the most useful behaviour)
With this, I don't think there's any need for any additional
modifiers, like ! or ++.
I also agree that there probably isn't much need for a list that
*only* includes partitions, but if someone comes up with a convincing
use case, then we could add another 2-letter command for that.
I don't think \d! works terribly well as a mental model, but maybe
that's just me.
Yeah, I agree. It just looks ugly somehow.
So it seems most of us are in favor for showing partitioned tables as
"partitioned table" instead of "table" in the table listing.Yeah, that sounds good to me.
+1
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12 July 2017 at 23:23, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
I also agree that there probably isn't much need for a list that
*only* includes partitions, but if someone comes up with a convincing
use case, then we could add another 2-letter command for that.
Actually, I just thought of a round-about sort of use case:
The various 2-letter commands \dE, \dt, etc... are designed to work
together, so you can do things like \dEt or \dtE to list all local and
foreign tables, whilst excluding views, sequences, etc. So, if for the
sake of argument, \dP were made to list partitions, then you'd be able
to do things like \dEPt to list all the various kinds of tables,
including partitions, whilst excluding views, etc.
That seems somewhat more elegant and flexible than \d++ or \d! or whatever.
Of course, you'd have to decide whether a foreign partition came under
\dE, \dP, both or something else. I'm not sure that we should eat
another letter of the alphabet just for that case, because there
aren't many left, and I don't think any will be natural mnemonics like
the others.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/12 23:58, Alvaro Herrera wrote:
Amit Langote wrote:
On 2017/07/11 13:34, Alvaro Herrera wrote:
Robert Haas wrote:
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type
"partitioned table", we wouldn't need a separate flag for marking a table
as having partitions.I think that is false. Whether something is partitioned and whether
it is a partition are independent concerns.Maybe this discussion is easier if we differentiate "list tables" (\dt,
or \d without a pattern) from "describe table" (\d with a name pattern).I think this discussion has mostly focused on "list tables" so far.
Yes, which I think is a mistake, because for some things you definitely
need a list of partitions of the table in question. And "describe
table" can fulfill that role perfectly well, ISTM.
For a partitioned table, "describe table" (aka \d name_pattern) lists its
partitions showing for each partition its name and the partition bound.
"list tables/view/indexes/..." (aka \d[tvi...]) shows information about
the listed objects that one might want to see for partitions (such as the
schema, owner, size, description) and "describe table" doesn't provide
that about partitions as just mentioned. So, it should be possible to
list partitions in some way.
However, the "list tables"
command \dt should definitely IMO not list partitions.Do you mean never? Even if a modifier is specified? In the patch I
proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
partitions, but \d or \dt won't. That is, partitions are hidden by default.I don't think there is any need for a single list of all partition of
all tables -- is there? I can't think of anything, but then I haven't
been exposed very much to this feature yet. For now, I lean towards "never".(A different consideration is the use case of listing relation
relfrozenxid/relminmxid ages, but that use case is already not fulfilled
by psql metacommands so you still need custom catalog queries).
As I mentioned above, if we decide to hide partitions except when
"describing" the parent table, one would need custom queries even to see
schema, owner, etc. for partitions.
I don't think \d! works terribly well as a mental model, but maybe
that's just me.
It seems you're not alone. Anyway, I'm starting to like Dean's advice [1]/messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
on this matter.
Thanks,
Amit
[1]: /messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
/messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/07/13 7:23, Dean Rasheed wrote:
On 12 July 2017 at 15:58, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Amit Langote wrote:
On 2017/07/11 13:34, Alvaro Herrera wrote:
However, the "list tables"
command \dt should definitely IMO not list partitions.Do you mean never? Even if a modifier is specified? In the patch I
proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
partitions, but \d or \dt won't. That is, partitions are hidden by default.I don't think there is any need for a single list of all partition of
all tables -- is there? I can't think of anything, but then I haven't
been exposed very much to this feature yet. For now, I lean towards "never".So just focusing on the listing issue for now...
I tend to agree with some of the upstream comments that a bare \d
should list everything, including partitions, because partitions are
still tables that you might want to do DML or DDL on.Also, if you look at what we already have, \d lists all types of
relations, and then there are 2-letter commands \dE, \di, \dm, \ds,
\dt and \dv that list just specific kinds of relations, for example
\dE lists foreign tables, and \dt lists local tables, specifically
excluding foreign tables.So ISTM that the most logical extension of that is:
\d - list all relations, including partitions
\d does leave out indexes, but that seems okay. I think it might be okay
to show partitions after all. If we do so, do we indicate somehow that
they are partitions of some table? Maybe an additional column "Partition"
with values "yes" or "no" that occurs right next to the Type column.
Output would look something like below:
\d
List of relations
Schema | Name | Type | Partition | Owner
--------+-----------+-------------------+-----------+-------
public | foo | table | no | amit
public | foo_a_seq | sequence | no | amit
public | xyz | partitioned table | no | amit
public | xyz1 | table | yes | amit
public | xyz2 | table | yes | amit
public | xyz3 | partitioned table | yes | amit
public | xyz4 | foreign table | yes | amit
(7 rows)
\dt - list only tables that are not foreign tables or partitions
of other tables
Note that that list will include partitioned tables.
(that's not quite an exact extension of the existing logic, because of
course it's partitioned tables that have the different relkind, not
the partitions, but the above seems like the most useful behaviour)
We allow creating regular tables, partitioned tables, and foreign tables
as partitions. Being a partition is really independent from the
considerations with which these 2-letter commands are designed, that is,
the second letters map one-to-one with relkinds (again, an exception made
when showing both regular tables and partitioned table with \dt.)
If we establish a rule that each such 2-letter command will only show the
tables of the corresponding relkind that are not partitions, that is, only
those for which relispartition=false will be shown, then we should find an
extension/modifier such that for each command it enables listing
partitions as well.
Perhaps the idea you mentioned at [1]/messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com of using letter 'P' for that purpose
could work. As you described, \dtP or \dPt shows tables (partitioned or
not) including those that are partitions. Bare \d will mean \dPtvmsE.
I also agree that there probably isn't much need for a list that
*only* includes partitions, but if someone comes up with a convincing
use case, then we could add another 2-letter command for that.
I too can't imagine needing to see only partitions.
Thanks,
Amit
[1]: /messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
/messages/by-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jul 10, 2017 at 5:46 PM, David Fetter <david@fetter.org> wrote:
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?
Since \det lists foreign tables, \det! would list foreign tables even
if they are partitions. Plain \det would show only the ones that are
not partitions.
\dT! wouldn't be meaningful, since \dT lists data types and data types
can't be partitions. If you're trying to conjure up a rule that every
\d<something> command must accept the same set of modifiers, a quick
look at the output of \? and a little experimentation will quickly
show you that neither S nor + apply to all command types, so I see no
reason why that would need to be true for a new modifier either.
TBH, I think we should just leave this well enough alone. We're
post-beta2 now, there's no clear consensus on what to do here, and
there will be very little opportunity for users to give us feedback if
we stick a change into an August beta3 before a September final
release.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote:
On Mon, Jul 10, 2017 at 5:46 PM, David Fetter <david@fetter.org> wrote:
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?Since \det lists foreign tables, \det! would list foreign tables even
if they are partitions. Plain \det would show only the ones that are
not partitions.\dT! wouldn't be meaningful, since \dT lists data types and data types
can't be partitions. If you're trying to conjure up a rule that every
\d<something> command must accept the same set of modifiers, a quick
look at the output of \? and a little experimentation will quickly
show you that neither S nor + apply to all command types, so I see no
reason why that would need to be true for a new modifier either.TBH, I think we should just leave this well enough alone. We're
post-beta2 now, there's no clear consensus on what to do here, and
there will be very little opportunity for users to give us feedback if
we stick a change into an August beta3 before a September final
release.
I think a new modifier would be too rushed at this stage, but there's
no reason to throw out the progress on \d vs \dt.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 16/07/17 05:24, David Fetter wrote:
On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote:
On Mon, Jul 10, 2017 at 5:46 PM, David Fetter <david@fetter.org> wrote:
With utmost respect, it's less messy than adding '!' to the already
way too random and mysterious syntax of psql's \ commands. What
should '\det!' mean? What about '\dT!'?Since \det lists foreign tables, \det! would list foreign tables even
if they are partitions. Plain \det would show only the ones that are
not partitions.\dT! wouldn't be meaningful, since \dT lists data types and data types
can't be partitions. If you're trying to conjure up a rule that every
\d<something> command must accept the same set of modifiers, a quick
look at the output of \? and a little experimentation will quickly
show you that neither S nor + apply to all command types, so I see no
reason why that would need to be true for a new modifier either.TBH, I think we should just leave this well enough alone. We're
post-beta2 now, there's no clear consensus on what to do here, and
there will be very little opportunity for users to give us feedback if
we stick a change into an August beta3 before a September final
release.I think a new modifier would be too rushed at this stage, but there's
no reason to throw out the progress on \d vs \dt.
+1
And similarly, there seemed to be a reasonably clear push to label the
'partitions' as such.
regards
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 07/07/2017 02:02 AM, Mark Kirkwood wrote:
I'd prefer *not* to see a table and its partitions all intermixed in the
same display (especially with nothing indicating which are partitions) -
as this will make for unwieldy long lists when tables have many
partitions. Also it would be good if the 'main' partitioned table and
its 'partitions' showed up as a different type in some way.
I've just read through this thread, and I'm wondering why we can't just
have something like \set SHOW_PARTITIONS true or something, and that
would default to false.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jul 18, 2017 at 2:26 AM, Vik Fearing
<vik.fearing@2ndquadrant.com> wrote:
On 07/07/2017 02:02 AM, Mark Kirkwood wrote:
I'd prefer *not* to see a table and its partitions all intermixed in the
same display (especially with nothing indicating which are partitions) -
as this will make for unwieldy long lists when tables have many
partitions. Also it would be good if the 'main' partitioned table and
its 'partitions' showed up as a different type in some way.I've just read through this thread, and I'm wondering why we can't just
have something like \set SHOW_PARTITIONS true or something, and that
would default to false.
We could, and that would have the advantage of letting people set a
default. On the other hand, if you want to override the default
behavior just once, adding a modifier character is a lot less typing
than issuing \set, retyping your command, and issuing \set again to
change it back. So I don't know which is better.
My main point is that it's too late to be making changes upon which we
do not have a clear consensus. I reject the argument that v11 will be
too late to make this change. Now that we have partitioning, I
believe there will be zillions of things that need to be done to
improve it further; several of those things already have proposed
patches; this can be another one of those things. If we rush
something in now and it turns out that it isn't well-liked, we may
well end up with one behavior for v<10, another behavior for v=10, and
a third behavior for v>10. Better to wait and make the change later
when we have a few more data points.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers