Re: [HACKERS] New partitioning - some feedback - Mailing list pgsql-hackers

On 2017/07/13 7:23, Dean Rasheed wrote:
> On 12 July 2017 at 15:58, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> Amit Langote wrote:
>>> On 2017/07/11 13:34, Alvaro Herrera wrote:
>>>> However, the "list tables"
>>>> command \dt should definitely IMO not list partitions.
>>>
>>> Do you mean never?  Even if a modifier is specified?  In the patch I
>>> proposed, \d! (or \d+ or \d++, if '!' turns out to be unpopular) will list
>>> partitions, but \d or \dt won't.  That is, partitions are hidden by default.
>>
>> I don't think there is any need for a single list of all partition of
>> all tables -- is there?  I can't think of anything, but then I haven't
>> been exposed very much to this feature yet.  For now, I lean towards "never".
>>
> 
> So just focusing on the listing issue for now...
> 
> I tend to agree with some of the upstream comments that a bare \d
> should list everything, including partitions, because partitions are
> still tables that you might want to do DML or DDL on.
> 
> Also, if you look at what we already have, \d lists all types of
> relations, and then there are 2-letter commands \dE, \di, \dm, \ds,
> \dt and \dv that list just specific kinds of relations, for example
> \dE lists foreign tables, and \dt lists local tables, specifically
> excluding foreign tables.
> 
> So ISTM that the most logical extension of that is:
> 
>   \d - list all relations, including partitions

\d does leave out indexes, but that seems okay.  I think it might be okay
to show partitions after all.  If we do so, do we indicate somehow that
they are partitions of some table?  Maybe an additional column "Partition"
with values "yes" or "no" that occurs right next to the Type column.
Output would look something like below:

\d                    List of 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