Re: Declarative partitioning in pgAdmin4

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-9517Mobile: +91 976-788-8246*