Обсуждение: [HACKERS] 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 | | notnull | dte | timestamp with time zone | | not null | val | integer | | notnull | 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
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]. 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
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
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
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
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
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
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
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
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
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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Вложения
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?
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.
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
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
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
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
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
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
> 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
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 relationsSchema | 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 | amitfoo_parts | foo2 | table | amit (2 rows) Thanks, Amit
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
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] [1] https://www.postgresql.org/message-id/CAFjFpRcs5fOSfaAGAjT5C6=YvDD7MRx3knf_SpB5DQZOJgjerA@mail.gmail.com -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
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] https://www.postgresql.org/message-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
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
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] https://www.postgresql.org/message-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
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] https://www.postgresql.org/message-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
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 -- 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 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] https://www.postgresql.org/message-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
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
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
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
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] on this matter. Thanks, Amit [1] https://www.postgresql.org/message-id/CAEZATCWcfFtsbKYcVyqUzoOsxkikQjpi_GdjZ_vL6RcX8iLEsg@mail.gmail.com
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 relationsSchema | Name | Type | Partition | Owner --------+-----------+-------------------+-----------+-------public | foo | table | no | amitpublic| foo_a_seq | sequence | no | amitpublic | xyz | partitioned table | no | amitpublic| xyz1 | table | yes | amitpublic | xyz2 | table | yes | amitpublic| xyz3 | partitioned table | yes | amitpublic | 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
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
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
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
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
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