Re: [pgadmin-hackers] 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.joshi@enterprisedb.com> wrote:

Hi Dave 

On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:

On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <dpage@pgadmin.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
Mobile: +91 976-788-8246

--

Akshay Joshi

Principal Software Engineer 

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