Re: Declarative partitioning in pgAdmin4

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

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

Please review it. Suggestions/Comments are welcome.

On Fri, May 19, 2017 at 11:03 PM, Shirley Wang <swang(at)pivotal(dot)io> wrote:

> On Fri, May 19, 2017 at 2:17 AM Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>
>> Hi Shirley
>>
>> On Thu, May 18, 2017 at 9:06 PM, Shirley Wang <swang(at)pivotal(dot)io> wrote:
>>
>>> Hi Akshay!
>>>
>>> Is this the workflow that you think users are going to engage in given
>>> what you're building? Anne mentioned you're in the process of figuring out
>>> what's required for defining the partitions, so you'll notice pink boxes
>>> with text in areas where that might happen.
>>>
>>> The modules that appear for partitioning are based on the ones we saw a
>>> few weeks ago, let me know if that has changed in any way.
>>>
>>> *01 user creates a table, if one doesn't already exist*
>>> [image: create table.png]
>>>
>>>
>>> *02 user selects 'yes' for partitioning*[image: add partition.png]
>>>
>>> Step 1 and 2 are correct.
>>
>>
>>
>>> *03 user defines type of partition and ranges*
>>> [image: define partition.png]
>>>
>>
>> We will create a new tabs "Partitions" and 'Partition Type' combo will
>> go on that tab along with following controls:
>>
>> - User should be able to specify Key Column(s) (Based on partition
>> type) to create partitioned(parent) table. Some of the examples of list and
>> range partitions are as below:
>> - CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
>> - CREATE TABLE prt1_e (a int, b int, c varchar) PARTITION BY
>> RANGE(((a + b)/2));
>> - CREATE TABLE prt1_m (a int, b int, c varchar) PARTITION BY
>> RANGE(a, ((a + b)/2));
>> - CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
>> - CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY
>> LIST(ltrim(c, 'A'));
>> - User should be able to create N number of partitions:
>>
>>
>> - Design one control (subnode control) so that user will add N number
>> of partitions. Here is the syntax as per documentation CREATE TABLE
>> table_name PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ]
>> [ column_constraint [ ... ] ] | table_constraint } [, ... ] ) ] FOR
>> VALUES partition_bound_spec partition_bound_spec is:{ IN ( {
>> bound_literal | NULL } [, ...] ) | FROM ( { bound_literal | UNBOUNDED } [,
>> ...] ) TO ( { bound_literal | UNBOUNDED } [, ...] ) }
>>
>>
>>
>> Design(GUI) discussion required for above two, which control should we
>> used so that user will be easily able to create N number of partitions.
>>
>
> Ok. Hopefully we'll get some interviews set up for next week that'll help
> us determine the best workflow for this.
>

--
*Akshay Joshi*
*Principal Software Engineer *

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