Re: Declarative partitioning in pgAdmin4

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