Re: dropping partitioned tables without CASCADE

On Mon, Mar 6, 2017 at 10:55 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 6 March 2017 at 04:00, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> On Mon, Mar 6, 2017 at 8:35 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> On 6 March 2017 at 00:51, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>> On 2017/03/05 16:20, Simon Riggs wrote:
>>>>> I notice also that
>>>>> \d+ <tablename>
>>>>> does not show which partitions have subpartitions.
>>>>
>>>> Do you mean showing just whether a partition is itself partitioned or
>>>> showing its partitions and so on (because those partitions may themselves
>>>> be partitioned)? Maybe, we could do the former.
>>>
>>> I think \d+ should show the full information, in some form.
>>
>> For a multi-level inheritance hierarchy, we don't show children which
>> are further inherited. Same behaviour has been carried over to
>> partitioning. I don't say that that's good or bad.
>>
>> Given the recursive structure of partitioned tables, it looks readable
>> and manageable to print only the direct partitions in \d+. May be we
>> want to indicate the partitions that are further partitioned. If user
>> wants information about partitioned partitions, s/he can execute \d+
>> on the partition, repeating this process to any desired level. This
>> would work well in the interactive mode, keeping the output of \d+
>> manageable. Further someone writing a script to consume \d+ output of
>> a multi-level partitioned table, can code the above process in a
>> script.
>>
>> Thinking about how to display partition which are further partitioned,
>> there are two options. Assume a partitioned table t1 with partitions
>> t1p1, which is further partitioned and t1p2. One could display \d+ t1
>> as
>>
>> \d+ t1
>> Table "public.t1"
>> Column | Type | Collation | Nullable | Default | Storage | Stats
>> target | Description
>> --------+---------+-----------+----------+---------+---------+--------------+-------------
>> a | integer | | not null | | plain | |
>> Partition key: RANGE (a)
>> Partitions: t1p1 FOR VALUES FROM (0) TO (100), HAS PARTITIONS
>> t1p2 FOR VALUES FROM (100) TO (200)
>>
>> OR
>>
>> \d+ t1
>> Table "public.t1"
>> Column | Type | Collation | Nullable | Default | Storage | Stats
>> target | Description
>> --------+---------+-----------+----------+---------+---------+--------------+-------------
>> a | integer | | not null | | plain | |
>> Partition key: RANGE (a)
>> Partitions: t1p1 FOR VALUES FROM (0) TO (100), PARTITION BY LIST(a)
>> t1p2 FOR VALUES FROM (100) TO (200)
>>
>> To me the first option looks fine.
>
> +1

Just to confirm, you want the output to look like this
>> \d+ t1
>> Table "public.t1"
>> Column | Type | Collation | Nullable | Default | Storage | Stats
>> target | Description
>> --------+---------+-----------+----------+---------+---------+--------------+-------------
>> a | integer | | not null | | plain | |
>> Partition key: RANGE (a)
>> Partitions: t1p1 FOR VALUES FROM (0) TO (100), HAS PARTITIONS
>> t1p2 FOR VALUES FROM (100) TO (200)

>
> lowercase please

Except for HAS PARTITIONS, everything is part of today's output. Given
the current output, HAS PARTITIONS should be in upper case.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company