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