On Thu, May 18, 2017 at 11:41 AM, Akshay Joshi < akshay.jo...@enterprisedb.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.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.p...@enterprisedb.com> >> wrote: >> >>> >>> >>> On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> >>>> Hi >>>> >>>> On Thu, May 4, 2017 at 4:00 PM, Dave Page <dp...@pgadmin.org> wrote: >>>> >>>>> Hi >>>>> >>>>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < >>>>> akshay.jo...@enterprisedb.com> wrote: >>>>> >>>>>> Hi All >>>>>> >>>>>> On Wed, May 3, 2017 at 5:35 PM, Dave Page <dp...@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 <+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