Re: Declarative partitioning in pgAdmin4

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-9517Mobile: +91 976-788-8246*
>

--
*Akshay Joshi*
*Principal Software Engineer *

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