Re: Declarative partitioning in pgAdmin4

Great, thanks.

I think it's clear that we need to display the child partitions in the
treeview. I don't see any other sensible way of enabling those operations
without an extremely contrived dialogue design.

Please now document how those features will be implemented; e.g, for each
one:

- View table data: Parent and partition context menu.
- Attach/detach partitions: Parent properties dialogue
...

That will then give us a list of places we'll need to (re)design dialogues
and menus etc. for.

On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> Hi Dave
>
> As per my understanding below operations required
>
> Parent:
>
> - View table data.
> - View stats.
> - Create regular/partitioned table
> - Create N number of partitions.
> - Drop/ Drop cascade, Truncate.
> - Attach/Detach Partitions.
> - Not able to create constraints excluding check constraint.
>
> Child:
>
> - View Table Data.
> - View stats.
> - View partition scheme in SQL pane
> - Create primary/foreign/.. key constraint.
> - Drop/ Drop cascade, Truncate
>
>
> On Tue, May 2, 2017 at 8:25 PM, 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"?
>>
>>>
>>> - 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-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246
> <+91%2097678%2088246>*
>

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company