Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

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

Phone: +91 20-3058-9517
Mobile: +91 976-788-8246

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

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company