Re: Declarative partitioning in pgAdmin4

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.
2. *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.
3. *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.
4. *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".
5. *Parameter Tab*:
- Gives error (unrecognized parameter "autovacuum_enabled") for all
parameters of Table Tab and working fine for "Toast Table" it's
working.

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
- 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.

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