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