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. > > > On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi < > akshay.jo...@enterprisedb.com> wrote: > >> Hi Dave >> >> As per my understanding below operations required >> >> Parent: >> >> - View table data. >> - View stats. >> - Create regular/partitioned table >> - Create N number of partitions. >> - Drop/ Drop cascade, Truncate. >> - Attach/Detach Partitions. >> - Not able to create constraints excluding check constraint. >> >> Child: >> >> - View Table Data. >> - View stats. >> - View partition scheme in SQL pane >> - Create primary/foreign/.. key constraint. >> - Drop/ Drop cascade, Truncate >> >> >> On Tue, May 2, 2017 at 8:25 PM, Dave Page <dp...@pgadmin.org> wrote: >> >>> Hi >>> >>> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < >>> akshay.jo...@enterprisedb.com> wrote: >>> >>>> Hi All >>>> >>>> To implement Declarative Partitioning in existing Table dialog >>>> below changes should be implemented: >>>> >>>> 1. *Icon: *As we have separate icon for view and materialised view, >>>> we should have for partition table. I didn't find any in font awesome. >>>> >>>> They are really different object types though (even having their own >>> collections), which isn't the case here. I'm not against having a slightly >>> modified icon, but I don't think it's necessary. Note that the object icons >>> come from pgAdmin III, and were custom designed for us. They aren't in font >>> awesome etc. We'd need to tweak one of the existing ones. >>> >>>> >>>> 1. *Inheritance*: >>>> - A partition cannot have any parents other than the partitioned >>>> table it is a partition of, nor can a regular table inherit from a >>>> partitioned table making the latter its parent. That means >>>> partitioned >>>> tables and partitions do not participate in inheritance with regular >>>> tables. >>>> - When user creates regular table then Inherited from table(s) >>>> control should not display partitioned table. >>>> 2. *Constraints*: >>>> - Primary/Foreign/Unique/Exclusion constraints are not supported >>>> on partitioned table. In that case respective controls should be >>>> disabled >>>> for partitioned table. >>>> - We will have to check which constraints are applicable on >>>> partitions(of partitioned table) still some R&D require. Can someone >>>> help >>>> me here. >>>> - For regular tables in Foreign Key constraints tab References >>>> control should not list partition tables. >>>> - Check constraints : cannot add NO INHERIT constraint to >>>> partitioned table, so that control is disabled for partition table. >>>> 3. *Advanced Tab*: >>>> - Relation works with partition table theirs is an error if >>>> "With indexes?" is set to Yes, so we need to disabled that for >>>> partition >>>> table. >>>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >>>> factor" and "Of type". >>>> 4. *Parameter Tab*: >>>> - Gives error (unrecognized parameter "autovacuum_enabled") for >>>> all parameters of Table Tab and working fine for "Toast Table" >>>> it's working. >>>> >>>> Can you detail what operations someone would likely want (or need) to >>> perform on the parent/child tables; e.g. >>> >>> Parent: >>> >>> - View stats >>> - View data >>> - Truncate >>> - View/create columns >>> - Bulk-create indexes >>> - Bulk-create foreign keys >>> >>> Child: >>> >>> - View stats >>> - View data >>> - Truncate >>> - Create indexes >>> - Create foreign keys >>> >>> >>> >>>> Apart from above we will have to do following: >>>> >>>> - Required switch control to specify whether it is a regular table >>>> or partitioned table. I have added it on General tab. Please refer >>>> Partition_Switch.png >>>> - Will have to add new tab "Partition" which will have one select2 >>>> control to define its Range partition or List partition. Refer >>>> Partition_Tab.png >>>> >>>> "Partitions"? >>> >>>> >>>> - Design following controls in *Partition* tab: >>>> - How to add columns in case of Range/List partition? LIST >>>> partition key supports only one column. For RANGE user can specify >>>> multiple >>>> columns. >>>> - How to specify expression, COLLATE while adding columns >>>> for partition. >>>> - We need subnode control so that user will add number of >>>> partition with there values of the main table. Need lot of R&D for >>>> this. >>>> - We will have to provide "Create partition", "Attach Partition" >>>> and "Detech partition" context menu options on Partitions >>>> collection node. >>>> >>>> OK. >>> >>> Thanks! This is a complex one :-( >>> >>> >>>> Let me know if I forgot something to add that we may need to >>>> handle/implement. >>>> >>>> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt <reckha...@pivotal.io> >>>> wrote: >>>> >>>>> The issues we consistently face: >>>>>>> >>>>>>> - The huge (often thousands sometimes tens of thousands) number >>>>>>> of partitions makes rendering all of the partitions painfully slow >>>>>>> and >>>>>>> frequently not useful. >>>>>>> >>>>>>> Perhaps, though I doubt that number would be common in Postgres. The >>>>>> problem though, is that there are both stats and sub-objects (indexes and >>>>>> triggers for example) that are part of the child partitions, not the >>>>>> parent >>>>>> - and they may differ from partition to partition. >>>>>> >>>>> >>>>> Certainly there differences in Postgres and Greenplum and this might >>>>> very well be one of those places. >>>>> >>>>> >>>>>> I don't see that we have any choice but to display them so users can >>>>>> work with them. >>>>>> >>>>> >>>>> We don't want to hide them, I do think we want to make accessing them >>>>> a useful experience. If we rephrase this statement as "How might we >>>>> display >>>>> partitioned tables so that users are able to work with and modify the >>>>> pieces they need?", this opens us up to different opportunities in how we >>>>> display them. >>>>> >>>>> Even with a simple case of 90 days of data partitioned by day, a drop >>>>> down showing 90 tables that are all mostly the same is a little >>>>> overwhelming. >>>>> >>>>> >>>>>> >>>>>>> - When end users are interested in looking at their partitions >>>>>>> they frequently don't want all of them displayed mindlessly >>>>>>> - They are looking at a subset of partitions >>>>>>> - Partitions are typically grouped around their inheritance >>>>>>> properties. >>>>>>> >>>>>>> How might you propose grouping them (based on the way they work in >>>>>> Postgres)? >>>>>> >>>>> >>>>> Honestly I'm not sure. We didn't really start thinking about this >>>>> until the other day so we are starting to look into the pains that >>>>> Greenplum customers have. Sharing that pain we discover back to the >>>>> pgAdmin >>>>> community and seeing if it makes sense from a Postgres perspective. After >>>>> that I need to dive into the Postgres implementation. >>>>> >>>>> -- Rob >>>>> >>>> >>>> >>>> >>>> -- >>>> *Akshay Joshi* >>>> *Principal Software Engineer * >>>> >>>> >>>> >>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>>> 976-788-8246 <+91%2097678%2088246>* >>>> >>>> >>>> -- >>>> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgadmin-hackers >>>> >>>> >>> >>> >>> -- >>> Dave Page >>> Blog: http://pgsnake.blogspot.com >>> Twitter: @pgsnake >>> >>> EnterpriseDB UK: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> >> >> >> -- >> *Akshay Joshi* >> *Principal Software Engineer * >> >> >> >> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 >> <+91%2097678%2088246>* >> > > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*