Re: Declarative partitioning in pgAdmin4

On Thu, May 18, 2017 at 11: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.
>

Certainly looks like it to me. We may want to tweak some things based on
the work Shirley is doing, but I think we're on the right path.

Good work!

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

--
Dave Page
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake