Postgres Pro
Downloads
Home   >   mailing lists

Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4 - Mailing list pgadmin-hackers

From Akshay Joshi
Subject Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Date
Msg-id CANxoLDcqudMZ5j-30EcFEL9KpQxyvrMWo0mVrWdg0p6_8e7peQ@mail.gmail.com
Whole thread Raw
In response to Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4  (Dave Page <dpage@pgadmin.org>)
Responses Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
List pgadmin-hackers
Tree view
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. 
   


On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi <akshay.joshi@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 <dpage@pgadmin.org> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <akshay.joshi@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 <reckhardt@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 




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





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

pgadmin-hackers by date:

Previous
From: Khushboo Vashi
Date:
Subject: Re: [pgadmin-hackers] [pgAdmin4][Patch]: Fixed RM #2315 : Sorting bysize is broken
Next
From: Murtuza Zabuawala
Date:
Subject: [pgadmin-hackers] [pgAdmin4][PATCH] To fix the issue with Backup in other langauge