Re: Declarative partitioning in pgAdmin4

On Mon, May 22, 2017 at 5:28 AM Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> Hi All
>
> As continuation I have worked on "Partition" Tab and added some controls
> where user will be able to specify the key columns and create N number of
> partitions:
>
> - Added Partition Type combo box where user will define the
> Type(Range/List)
> - "Partition Keys" subnode control where there is combo box to specify
> it is column or expression. If it is column then on expanding subnode
> control all the columns specified to create main table will be listed here.
> User won't be able to select multiple columns here. If it is expression
> then user will be able to specify correct expression with correct
> parenthesis. In case of List partition only one row will be allowed.
>
> To get out an MVP, perhaps we should focus on just establishing a
partition for a single range and list, then figure out how an expression
would work. It seems like the majority of use cases for using a range
partition would be by date so let's get that functionality working and
worry about expressions once we have live feedback to minimize risk (excess
time spent developing features with many unknowns about user behavior)

>
> - "Partitions" control is used to create N number of partitions. In
> case of Range partition there are three columns "Name", "Value From" and
> "Value To". If partition key is combination of multiple columns or
> combination of column and expression then user will have to provide (,)
> comma separated values. Refer "*Range_Partition.png*". In case of List
> partition there are two columns "Name" and "Values In" and user will have
> to provide (,) separated list for "Values In" column. Refer "
> *List_Partition.png*".
>
> Here's a sketch of what a single range would look like:

[image: partition-Range1.jpg][image: partition-Range2.jpg]

> *Note*: Apart from above there are following that needs to be taken care:
>
> - Once user will define the partition keys and partitions based on the
> columns define for main table and then user will rename/delete any column
> from "Columns" Tab we will warn user about renaming/deleting any column
> will reset all the rows define under partitions tab. This is just because
> we can rename/delete the column from partition keys but not sure how we can
> remove/rename it from the expression defined by the user.
>
> That's fine, as long as they are still able to do so after a warning.

>
> - *Major challenge* here is while creating partitions(second subnode
> control in attached image) according to the documentation user will be able
> to create constraints( Primary, Foreign, Unique...). How user will be able
> to add constraints from GUI? Do we need to implement a new control where
> one subnode control contains the whole "Constraints" Tab or any other
> solution.
>
>
>
We can probably leverage the same table. We're investigating constraints as
well.

Shirley & Rob

>