Re: Declarative partitioning in pgAdmin4

On Tue, May 23, 2017 at 2:59 AM, Shirley Wang <swang(at)pivotal(dot)io> wrote:

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

I personally think it is not that difficult to implement expressions we
just need to get the value given by user and create proper SQL to create
the table. For example User will create one partitioned table with three
columns a,b and c. from partitions key control user will select first row
as column (a) and second row as expression ((a+b)/2) so we will have to
create table with "*PARTITION BY RANGE (a, ((a+b)/2)*". I think we should
consider this as well.

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

--
*Akshay Joshi*
*Principal Software Engineer *

*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*