Re: Declarative partitioning in pgAdmin4

Also, I forgot to mention, these mockups are in Figma:
https://www.figma.com/file/RnmYyytaZdjsDHQWrMw5U0co/Create-partition-table?node-id=16%3A54

Feel free to play around and change things :)

On Thu, May 18, 2017 at 11:36 AM 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]
>
> *03 user defines type of partition and ranges*
> [image: define partition.png]
> *04 After hitting submit, browser is updated with new table and
> partitions, and user is taken to SQL tab. (What is this step for?)*
> [image: SQL- range partition (1).png]
>
> You mentioned Postgres users need to go into individual partitions
> frequently and its common to have different indexes for each partition.
> However, I wonder if the naming convention created will provide enough
> context for people to remember which partition has the specific properties
> they're looking for. If we imagine a scenario where a user has more than 30
> or 40 partitions, and they need to look for one partition, it might be
> quite time consuming for people to find the right one.
>
> We're trying to find Postgres users to test this flow with, especially
> since the success of this design relies on users knowing how to interact
> with the partitions in their browser.
>
> On Thu, May 18, 2017 at 6:41 AM Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>
>> Hi All
>>
>> I have started implementation for Declarative Partitioning in pgAdmin4.
>> Following are the tasks that I have implemented till now:
>>
>> - Show partitioned table and it's partitions under the parent table.
>> Refer Partitioned_Table.png
>> - To implement above I have created 'partitions' collection node and
>> 'partition' node under table node which is nothing but table node itself.
>> To reduce redundant/duplicate code I have made following changes:
>> - Create new file "*utils.py*" under tables folder. Create a new
>> class BaseTableView(PGChildNodeView): derived from PGChildNodeView.
>> TableView and PartitionsView (new class for partition table) is
>> derived from BaseTableView.
>> - Move the common logic like dependencies, dependents, reversed
>> engineered sql, statistics, reset statistics in BaseTableView
>> class functions and then call that function from derived class
>> like BaseTableView.get_table_dependencies(self, tid)
>> - Will move more generic logic as we progress on this task.
>> - Updated supported nodes list in DataGrid(View Data),
>> Backup, Maintenance, Restore to show context menu for partitions.
>> - Make sure dependencies, dependents, statistics, truncate,
>> delete/drop and Reset Statistics works with partitions.
>> - Updated jinja template to show correct reversed engineered sql for
>> partitioned table. Please refer the "List_with_expression.png" for
>> List partition and "Range_with_column_expression.png" for Range
>> partition.
>> - Updated jinja template to show correct sql for partitions of parent
>> table. Please refer "SQL_Range_Partitions.png" and "SQL
>> _List_Partitions.png". Some R&D is still require for other syntax too.
>>
>> Please let me know above looks good and am I going in right direction.
>>
>>
>> On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <
>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>
>>> Hi Dave
>>>
>>> On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave(dot)page(at)enterprisedb(dot)com>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <
>>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <
>>>>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>>>>
>>>>>>> Hi All
>>>>>>>
>>>>>>> On Wed, May 3, 2017 at 5:35 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>>>
>>>>>>>> Great, thanks.
>>>>>>>>
>>>>>>>> I think it's clear that we need to display the child partitions in
>>>>>>>> the treeview. I don't see any other sensible way of enabling those
>>>>>>>> operations without an extremely contrived dialogue design.
>>>>>>>>
>>>>>>>> Please now document how those features will be implemented; e.g,
>>>>>>>> for each one:
>>>>>>>>
>>>>>>>> - View table data: Parent and partition context menu.
>>>>>>>> - Attach/detach partitions: Parent properties dialogue
>>>>>>>> ...
>>>>>>>>
>>>>>>>> That will then give us a list of places we'll need to (re)design
>>>>>>>> dialogues and menus etc. for.
>>>>>>>>
>>>>>>>
>>>>>>> As per my knowledge on Partitioning, I think we will have to
>>>>>>> implement following things in parent and child:
>>>>>>>
>>>>>>> Parent:
>>>>>>>
>>>>>>> 1. View Table data : No need to change any logic, it's working.
>>>>>>> 2. Correct jinja template to show correct SQL in SQL pane.
>>>>>>> 3. Create partitioned table -
>>>>>>> - Add one switch control ("Partitioned Table?") in General
>>>>>>> tab of Table dialog.
>>>>>>> - Add new tab "Partitions".
>>>>>>> - Add one select2 control (Partition Type :Range/List) in
>>>>>>> "Partitions" tab.
>>>>>>> - Create one subnode control to specify number of key columns
>>>>>>> with expressions. For List partition only one row will be there + button
>>>>>>> will be disabled, and for Range partition + button will be enabled. Here is
>>>>>>> the syntax as per documentation [ PARTITION BY { RANGE | LIST
>>>>>>> } ( { *column_name* | ( *expression* ) } [ COLLATE *collation* ]
>>>>>>> [ *opclass* ] [, ... ] ) ]. *Design discussion required here
>>>>>>> for how user will specify expression, collate and opclass*.
>>>>>>>
>>>>>>> 4. 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 discussion required here for how user will specify
>>>>>>> all the above combinations.*
>>>>>>> 5. Properties dialog "Partitions" Tab:
>>>>>>> - Partition Type control must be disabled.
>>>>>>> - User will be able to create/modify existing partitions.
>>>>>>> User won't be able to delete partitions as there are two modes Detach/Drop
>>>>>>> and we will have separate menu for it.
>>>>>>> 6. Drop/ Drop cascade, Truncate: No need to change any logic,
>>>>>>> it's working.
>>>>>>> 7. Attach Partitions: Create context menu on partitioned table.
>>>>>>> When user clicks, open one dialog with some controls to provide
>>>>>>> table(to be attach) and partition_bound_spec*. Design discussion
>>>>>>> required here.*
>>>>>>> 8. Not able to create constraints excluding check constraint:
>>>>>>> We will have to disable context menu, remove child nodes from browser tree
>>>>>>> for constraints and disable controls from the dialog.
>>>>>>>
>>>>>>> Child:
>>>>>>>
>>>>>>> 1. View Table Data: Add context menu.
>>>>>>> 2. Detach partition: Create context menu, when user click popped
>>>>>>> up confirmation message box.
>>>>>>> 3. View partition scheme in SQL pane: Changes required in jinja
>>>>>>> template.
>>>>>>> 4. Create primary/foreign/.. key constraint: No need to change
>>>>>>> any logic on GUI, but may need to change queries to fetch the partitioned
>>>>>>> tables.
>>>>>>> 5. Drop/ Drop cascade, Truncate: No need to change any logic.
>>>>>>>
>>>>>>> Apart from above it may be possible that I miss something, so we
>>>>>>> need to cover that too.
>>>>>>>
>>>>>>
>>>>>> OK, good. So now, let's break that down into a list of tasks, that we
>>>>>> can prioritise with Shirley. The initial list should be prioritised based
>>>>>> on your understanding I think, given the following criteria:
>>>>>>
>>>>>> - Changes that prevent pgAdmin breaking
>>>>>> - Changes that prevent pgAdmin showing incorrect data/info
>>>>>> - Changes that enable pgAdmin to show correct info
>>>>>> - Changes that add functionality for creating/dropping partitioned
>>>>>> tables as one unit
>>>>>> - Changes that add functionality for modifying individual partitions
>>>>>> independently
>>>>>>
>>>>>> Please document the requirements and initial plan on the pgAdmin
>>>>>> Redmine Wiki.
>>>>>>
>>>>>
>>>>> I have updated Redmine Wiki page regarding what needs to be
>>>>> implemented for partitioning. Can we discuss prioritisation of the task
>>>>> based on above criteria in the our meeting. Meanwhile I have started
>>>>> working on showing correct SQL for partitioned table.
>>>>>
>>>>
>>>> Which meeting?
>>>>
>>>
>>> Meeting with Shirley, which wasn't schedule last Friday as I was on
>>> leave.
>>>
>>>>
>>>> --
>>>> Dave Page
>>>> VP, Chief Architect, Tools & Installers
>>>> EnterpriseDB: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>>
>>>> Blog: http://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>
>>>
>>>
>>> --
>>> *Akshay Joshi*
>>> *Principal Software Engineer *
>>>
>>>
>>>
>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91
>>> 976-788-8246 <+91%2097678%2088246>*
>>>
>>
>>
>>
>> --
>>
> *Akshay Joshi*
>> *Principal Software Engineer *
>>
>>
>>
>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246
>> <+91%2097678%2088246>*
>>
>