Re: Declarative partitioning in pgAdmin4

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