Re: [HACKERS] New partitioning - some feedback

[Robert Haas]

On Tue, Jul 18, 2017 at 2:26 AM, Vik Fearing
 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

Re: [HACKERS] New partitioning - some feedback

[Vik Fearing]

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

Re: [HACKERS] New partitioning - some feedback

[Mark Kirkwood]

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  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 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

Re: [HACKERS] New partitioning - some feedback

[David Fetter]

On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote:
> On Mon, Jul 10, 2017 at 5:46 PM, David Fetter  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 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  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

Re: [HACKERS] New partitioning - some feedback

[Robert Haas]

On Mon, Jul 10, 2017 at 5:46 PM, David Fetter  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 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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/13 7:23, Dean Rasheed wrote:
> On 12 July 2017 at 15:58, Alvaro Herrera  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] 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]
https://www.postgresql.org/message-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg%40mail.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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

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
  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]
on this matter.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/caezatcwcfftsbkycvyquzoosxkikqjpi_gdjz_vl6rcx8il...@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

Re: [HACKERS] New partitioning - some feedback

[Dean Rasheed]

On 12 July 2017 at 23:23, Dean Rasheed  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

Re: [HACKERS] New partitioning - some feedback

[Dean Rasheed]

On 12 July 2017 at 15:58, Alvaro Herrera  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

Re: [HACKERS] New partitioning - some feedback

[Alvaro Herrera]

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
> >>  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 Herrerahttps://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

Re: [HACKERS] New partitioning - some feedback

[Ashutosh Bapat]

On Wed, Jul 12, 2017 at 9:39 AM, Amit Langote
 wrote:
> On 2017/07/12 12:47, Ashutosh Bapat wrote:
>> On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
>>  wrote:
>>> On 2017/07/11 18:57, Ashutosh Bapat wrote:
 On Tue, Jul 11, 2017 at 4:16 AM, David Fetter  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] 
 https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=yvdd7mrx3knf_spb5dqzojgj...@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 ) 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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

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"

Table "public.xyz2"

Table "public.xyz3"

Table "public.xyz31"


...which might seem kind of odd.  Do we want to show xyz1 as "Partition
public.xyz1", for example?

Thanks,
Amit
From ceacc566ab7ac2ffe56a47435a53a12ebafdffe5 Mon Sep 17 00:00:00 2001
From: amit 
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(,
  "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 >= 10)
+   appendPQExpBuffer(,
+ " 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"),
+
+

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/12 12:47, Ashutosh Bapat wrote:
> On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
>  wrote:
>> On 2017/07/11 18:57, Ashutosh Bapat wrote:
>>> On Tue, Jul 11, 2017 at 4:16 AM, David Fetter  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] 
>>> https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=yvdd7mrx3knf_spb5dqzojgj...@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 ) 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

Re: [HACKERS] New partitioning - some feedback

[Ashutosh Bapat]

On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote
 wrote:
> On 2017/07/11 18:57, Ashutosh Bapat wrote:
>> On Tue, Jul 11, 2017 at 4:16 AM, David Fetter  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] 
>> https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=yvdd7mrx3knf_spb5dqzojgj...@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 ) 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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/11 13:34, Alvaro Herrera wrote:
> Robert Haas wrote:
>> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
>>  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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/11 18:57, Ashutosh Bapat wrote:
> On Tue, Jul 11, 2017 at 4:16 AM, David Fetter  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] 
> https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=yvdd7mrx3knf_spb5dqzojgj...@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 ) 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

Re: [HACKERS] New partitioning - some feedback

[Ashutosh Bapat]

On Tue, Jul 11, 2017 at 4:16 AM, David Fetter  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
>>  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]

[1] 
https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=yvdd7mrx3knf_spb5dqzojgj...@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

Re: [HACKERS] New partitioning - some feedback

[Alvaro Herrera]

Robert Haas wrote:
> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
>  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 Herrerahttps://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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

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

Re: [HACKERS] New partitioning - some feedback

[Paul A Jungwirth]

> 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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/11 7:33, Robert Haas wrote:
> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote  
> 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

Re: [HACKERS] New partitioning - some feedback

[Greg Stark]

On 10 July 2017 at 23:46, David Fetter  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
>>  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

Re: [HACKERS] New partitioning - some feedback

[David Fetter]

On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote:
> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
>  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  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

Re: [HACKERS] New partitioning - some feedback

[Robert Haas]

On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote
 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

Re: [HACKERS] New partitioning - some feedback

[David Fetter]

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  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?
> >> 
> >>
> > 
> > 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  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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/10 15:32, Craig Ringer wrote:
> On 8 July 2017 at 00:03, David Fetter  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?
>> 
>>
> 
> 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

Re: [HACKERS] New partitioning - some feedback

[Craig Ringer]

On 8 July 2017 at 00:03, David Fetter  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?
> 
>

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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

On 2017/07/08 14:12, Mark Kirkwood wrote:
> On 07/07/17 19:54, Michael Banck wrote:
>> On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote:
>>> On 07/07/17 13:29, Amit Langote wrote:
 Someone complained about this awhile back [1].  And then it came up again
 [2], where Noah appeared to take a stance that partitions should be
 visible in views / output of commands that list "tables".

 Although I too tend to prefer not filling up the \d output space by
 listing partitions (pg_class.relispartition = true relations), there
 wasn't perhaps enough push for creating a patch for that.  If some
 committer is willing to consider such a patch, I can make one.
>>>
>>> Yeah, me too (clearly). However if the consensus is that all these
>>> partition
>>> tables *must* be shown in \d output, then I'd be happy if they were
>>> identified as such rather than just 'table' (e.g 'partition table').
>> +1.
>>
>> Or maybe just 'partition' is enough if 'partition table' would widen the
>> column output unnecessarily.
> 
> Yeah, that is probably better (and 'partition table' is potentially
> confusing as Robert pointed out).

So, it seems at least that showing "partition" as the Type of tables that
are actually partitions is preferable.  I created a patch (attached 0001)
that implements that.  It makes \d show any relations that have
relispartition = true as of type "partition" or "foreign partition".  With
the patch:

create table p (a int) partition by list (a);

-- regular table as partition
create table p1 partition of p for values in (1)

-- foreign table as partition
create foreign data wrapper dummy;
create server dummy foreign data wrapper dummy;
create foreign table p2 partition of p for values in (2) server dummy;

-- partitioned table as partition
create table p3 partition of p for values in (3) partition by list (a);

\d
 List of relations
 Schema | Name |   Type| Owner
+--+---+---
 public | p| table | amit
 public | p1   | partition | amit
 public | p2   | foreign partition | amit
 public | p3   | partition | amit
(4 rows)

Also, there seems to be at least some preference for excluding partitions
by default from the \d listing.  Attached 0002 implements that.  To enable
showing partitions, the patch adds a new modifier '!' to \d (picked '!'
from Robert's email on this thread [1]).  With the patch:

\d
   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 public | p| table | amit
(1 row)

\d!
 List of relations
 Schema | Name |   Type| Owner
+--+---+---
 public | p| table | amit
 public | p1   | partition | amit
 public | p2   | foreign partition | amit
 public | p3   | partition | amit
(4 rows)

\d!+
 List of relations
 Schema | Name |   Type| Owner |  Size   | Description
+--+---+---+-+-
 public | p| table | amit  | 0 bytes |
 public | p1   | partition | amit  | 0 bytes |
 public | p2   | foreign partition | amit  | 0 bytes |
 public | p3   | partition | amit  | 0 bytes |
(4 rows)

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CA%2BTgmoYNPHFjY%2BObFF9%3DTbX%2BT6ez1FAU%2BsmGuXeoiOMasDc-0g%40mail.gmail.com
From c73da2fcfc81ffa351f96be000ae5d262d828ae1 Mon Sep 17 00:00:00 2001
From: amit 
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(,
  "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 >= 10)
+   appendPQExpBuffer(,
+ " WHEN " 
CppAsString2(RELKIND_RELATION) " THEN"
+ "   CASE c.relispartition"
+  

Re: [HACKERS] New partitioning - some feedback

[Mark Kirkwood]

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

Re: [HACKERS] New partitioning - some feedback

[David Fetter]

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  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

Re: [HACKERS] New partitioning - some feedback

[Robert Haas]

On Fri, Jul 7, 2017 at 8:20 AM, Tom Lane  wrote:
> Robert Haas  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

Re: [HACKERS] New partitioning - some feedback

[Simon Riggs]

On 7 July 2017 at 13:20, Tom Lane  wrote:
> Robert Haas  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  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 Riggshttp://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

Re: [HACKERS] New partitioning - some feedback

[Tom Lane]

Robert Haas  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

Re: [HACKERS] New partitioning - some feedback

[Robert Haas]

On Fri, Jul 7, 2017 at 3:54 AM, Michael Banck  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

Re: [HACKERS] New partitioning - some feedback

[Simon Riggs]

On 7 July 2017 at 08: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.

+1 to remove partitions from \d display

With 1000 partitions that would just be annoying

-- 
Simon Riggshttp://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

Re: [HACKERS] New partitioning - some feedback

[Michael Banck]

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.ba...@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

Re: [HACKERS] New partitioning - some feedback

[Mark Kirkwood]

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

Re: [HACKERS] New partitioning - some feedback

[Amit Langote]

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_ 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].  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.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAM-w4HOZ5fPS7GoCTTrW42q01%2BwPrOWFCnr9H0iDyVTZP2H1CA%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/20170406070227.GA2741046%40tornado.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

[HACKERS] New partitioning - some feedback

[Mark Kirkwood]

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_ 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