Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

On Mon, May 22, 2017 at 5:28 AM Akshay Joshi <akshay.joshi@enterprisedb.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:

 

 

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