Re: Declarative partitioning in pgAdmin4

Hi Shirley

On Wed, May 3, 2017 at 3:31 AM, Shirley Wang <swang(at)pivotal(dot)io> wrote:

>
>
> On Tue, May 2, 2017 at 10:56 AM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <
>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>
>>> Hi All
>>>
>>> To implement Declarative Partitioning in existing Table dialog
>>> below changes should be implemented:
>>>
>>> 1. *Icon: *As we have separate icon for view and materialised view,
>>> we should have for partition table. I didn't find any in font awesome.
>>>
>>> They are really different object types though (even having their own
>> collections), which isn't the case here. I'm not against having a slightly
>> modified icon, but I don't think it's necessary. Note that the object icons
>> come from pgAdmin III, and were custom designed for us. They aren't in font
>> awesome etc. We'd need to tweak one of the existing ones.
>>
>>>
>>> 1. *Inheritance*:
>>> - A partition cannot have any parents other than the partitioned
>>> table it is a partition of, nor can a regular table inherit from a
>>> partitioned table making the latter its parent. That means partitioned
>>> tables and partitions do not participate in inheritance with regular tables.
>>> - When user creates regular table then Inherited from table(s)
>>> control should not display partitioned table.
>>> 2. *Constraints*:
>>> - Primary/Foreign/Unique/Exclusion constraints are not supported
>>> on partitioned table. In that case respective controls should be disabled
>>> for partitioned table.
>>> - We will have to check which constraints are applicable on
>>> partitions(of partitioned table) still some R&D require. Can someone help
>>> me here.
>>> - For regular tables in Foreign Key constraints tab References
>>> control should not list partition tables.
>>> - Check constraints : cannot add NO INHERIT constraint to
>>> partitioned table, so that control is disabled for partition table.
>>> 3. *Advanced Tab*:
>>> - Relation works with partition table theirs is an error if "With
>>> indexes?" is set to Yes, so we need to disabled that for partition table.
>>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill
>>> factor" and "Of type".
>>> 4. *Parameter Tab*:
>>> - Gives error (unrecognized parameter "autovacuum_enabled") for
>>> all parameters of Table Tab and working fine for "Toast Table"
>>> it's working.
>>>
>>> Can you detail what operations someone would likely want (or need) to
>> perform on the parent/child tables; e.g.
>>
>> Parent:
>>
>> - View stats
>> - View data
>> - Truncate
>> - View/create columns
>> - Bulk-create indexes
>> - Bulk-create foreign keys
>>
>> Child:
>>
>> - View stats
>> - View data
>> - Truncate
>> - Create indexes
>> - Create foreign keys
>>
>>
>>
>>> Apart from above we will have to do following:
>>>
>>> - Required switch control to specify whether it is a regular table
>>> or partitioned table. I have added it on General tab. Please refer
>>> Partition_Switch.png
>>> - Will have to add new tab "Partition" which will have one select2
>>> control to define its Range partition or List partition. Refer
>>> Partition_Tab.png
>>>
>>> "Partitions"?
>>
>
> Is there a reason why 'Partition' needs to open in a new tab? If there's
> only one field, we should include it on the same page since the tabs don't
> dictate necessary steps in a sequential order. Users will be able to find
> what they need without navigating to another part of the dialog.
>

There are lots of controls yet to design like column(s), number of
partitions, partition schemes will be part of "Partitions" Tab. We will
need that.

>
> Example:
> [image: tablepartition-1.png]
> [image: tablepartition-2.png]
>
>
>>> - Design following controls in *Partition* tab:
>>> - How to add columns in case of Range/List partition? LIST
>>> partition key supports only one column. For RANGE user can specify multiple
>>> columns.
>>> - How to specify expression, COLLATE while adding columns
>>> for partition.
>>> - We need subnode control so that user will add number of
>>> partition with there values of the main table. Need lot of R&D for this.
>>> - We will have to provide "Create partition", "Attach Partition" and
>>> "Detech partition" context menu options on Partitions collection
>>> node.
>>>
>>> OK.
>>
>> Thanks! This is a complex one :-(
>>
>>
>>> Let me know if I forgot something to add that we may need to
>>> handle/implement.
>>>
>>> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
>>> wrote:
>>>
>>>> The issues we consistently face:
>>>>>>
>>>>>> - The huge (often thousands sometimes tens of thousands) number
>>>>>> of partitions makes rendering all of the partitions painfully slow and
>>>>>> frequently not useful.
>>>>>>
>>>>>> Perhaps, though I doubt that number would be common in Postgres. The
>>>>> problem though, is that there are both stats and sub-objects (indexes and
>>>>> triggers for example) that are part of the child partitions, not the parent
>>>>> - and they may differ from partition to partition.
>>>>>
>>>>
>>>> Certainly there differences in Postgres and Greenplum and this might
>>>> very well be one of those places.
>>>>
>>>>
>>>>> I don't see that we have any choice but to display them so users can
>>>>> work with them.
>>>>>
>>>>
>>>> We don't want to hide them, I do think we want to make accessing them a
>>>> useful experience. If we rephrase this statement as "How might we display
>>>> partitioned tables so that users are able to work with and modify the
>>>> pieces they need?", this opens us up to different opportunities in how we
>>>> display them.
>>>>
>>>> Even with a simple case of 90 days of data partitioned by day, a drop
>>>> down showing 90 tables that are all mostly the same is a little
>>>> overwhelming.
>>>>
>>>>
>>>>>
>>>>>> - When end users are interested in looking at their partitions
>>>>>> they frequently don't want all of them displayed mindlessly
>>>>>> - They are looking at a subset of partitions
>>>>>> - Partitions are typically grouped around their inheritance
>>>>>> properties.
>>>>>>
>>>>>> How might you propose grouping them (based on the way they work in
>>>>> Postgres)?
>>>>>
>>>>
>>>> Honestly I'm not sure. We didn't really start thinking about this until
>>>> the other day so we are starting to look into the pains that Greenplum
>>>> customers have. Sharing that pain we discover back to the pgAdmin community
>>>> and seeing if it makes sense from a Postgres perspective. After that I
>>>> need to dive into the Postgres implementation.
>>>>
>>>> -- Rob
>>>>
>>>
>>>
>>>
>>> --
>>> *Akshay Joshi*
>>> *Principal Software Engineer *
>>>
>>>
>>>
>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91
>>> 976-788-8246 <+91%2097678%2088246>*
>>>
>>>
>>> --
>>> Sent via pgadmin-hackers mailing list (pgadmin-hackers(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>
>>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>

--
*Akshay Joshi*
*Principal Software Engineer *

*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*