Re: Declarative partitioning in pgAdmin4

On Thu, May 18, 2017 at 11:22 PM, Shirley Wang <swang(at)pivotal(dot)io> wrote:

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

Not able to open the above URL. It's 404 when I opened it.

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

--
*Akshay Joshi*
*Principal Software Engineer *

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