Thread: Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <dpage@pgadmin.org> wrote:

Hi

[moving to the pgadmin-hackers mailing list as this a pgAdmin feature]

On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave

Murtuza and I started thinking about "How to add Declarative Partitioning" support in pgAdmin4. We thought instead of showing Partition Table under existing Tables collection, we should add new collection node "Partition Tables". Showing table under the table node recursively will require lots of code changes in table and it's child nodes (column, index, trigger, etc..) which is more complex and error prone. 

Perhaps, but from the user's perspective, there's no reason to list them separately - they are just tables with a different structure from others. We shouldn't confuse the user just because it's more convenient for us.

I really think it should look like this:

- Tables

  - t1

    - Columns

    - Constraints

    - Partitions

      - p1

        - Sub Objects (whatever they may be)

        ...

      - p2

      ...

  - t2

  ...

   

 

Below is the design that we can implement: 

• Create new "Partition Tables" collection node. User will be able to create partition table by clicking "Create -> Partition Table" menu that we will add on collection node. We will share the dialog prototype later once we will have complete understanding of it.

Can you share a mock-up of the dialog? The Figma tool that Shirley shared looks like it'll be good for doing that - I can invite you to the team. 

• Once table is created user will be able to create partitions by clicking "Create -> Partitions" menu will be added on each partitioned table node. We will share the dialog prototype later once we will have complete understanding of it.

I would expect the user to be able to define the partitioning scheme when they create the table; e.g. on a new tab. It shouldn't be a two step process. 

• We will have to show sub nodes like (column, index, trigger, constraints, etc..) on main table while some of the sub nodes won't require for partitions like (column and many more again require some more knowledge on partitioning).

OK.

 

Apart from above we will have to figure out following:

• How to remove partitions(table) from existing tables node as value of relkind column is 'r' for partitions.
• Partitioning scheme to show in SQL pane for partitions.
  
• Some unknown issue/features of Declarative partitioning. 

OK.

Seems like there are a couple of assumptions being made here:

- Users need to see partitioned tables when expanding parent table

- Users need to view partitioned tables in context to their parent table (Dave says yes, Akshay and Murtuza say no)

- Users want to create a partitioned table through the browser (Akshay and Murtuza say yes, Dave says no)

Plus some technical concerns:

- Making code changes in table is complex and error prone

- How to move partitions from one node to another

I think the first assumption is important to validate or invalidate before even thinking about how to implement or addressing technical concerns. We may come to learn that there are solutions that don't require a lot of technical maneuvering, or perhaps learn there's no need for change at all.

Akshay and Murtuza, I'm happy to work with you on doing some research (interviews to discover user needs and pains, creating mockups, getting feedback etc) and coming up with some solutions based on user feedback.

Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <dpage@pgadmin.org> wrote:

Hi

[moving to the pgadmin-hackers mailing list as this a pgAdmin feature]

On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave

Murtuza and I started thinking about "How to add Declarative Partitioning" support in pgAdmin4. We thought instead of showing Partition Table under existing Tables collection, we should add new collection node "Partition Tables". Showing table under the table node recursively will require lots of code changes in table and it's child nodes (column, index, trigger, etc..) which is more complex and error prone. 

Perhaps, but from the user's perspective, there's no reason to list them separately - they are just tables with a different structure from others. We shouldn't confuse the user just because it's more convenient for us.

I really think it should look like this:

- Tables

  - t1

    - Columns

    - Constraints

    - Partitions

      - p1

        - Sub Objects (whatever they may be)

        ...

      - p2

      ...

  - t2

  ...

   

 

Below is the design that we can implement: 

• Create new "Partition Tables" collection node. User will be able to create partition table by clicking "Create -> Partition Table" menu that we will add on collection node. We will share the dialog prototype later once we will have complete understanding of it.

Can you share a mock-up of the dialog? The Figma tool that Shirley shared looks like it'll be good for doing that - I can invite you to the team. 

• Once table is created user will be able to create partitions by clicking "Create -> Partitions" menu will be added on each partitioned table node. We will share the dialog prototype later once we will have complete understanding of it.

I would expect the user to be able to define the partitioning scheme when they create the table; e.g. on a new tab. It shouldn't be a two step process. 

• We will have to show sub nodes like (column, index, trigger, constraints, etc..) on main table while some of the sub nodes won't require for partitions like (column and many more again require some more knowledge on partitioning).

OK.

 

Apart from above we will have to figure out following:

• How to remove partitions(table) from existing tables node as value of relkind column is 'r' for partitions.
• Partitioning scheme to show in SQL pane for partitions.
  
• Some unknown issue/features of Declarative partitioning. 

OK.

Seems like there are a couple of assumptions being made here:

- Users need to see partitioned tables when expanding parent table

- Users need to view partitioned tables in context to their parent table (Dave says yes, Akshay and Murtuza say no)

- Users want to create a partitioned table through the browser (Akshay and Murtuza say yes, Dave says no)

Plus some technical concerns:

- Making code changes in table is complex and error prone

- How to move partitions from one node to another

I think the first assumption is important to validate or invalidate before even thinking about how to implement or addressing technical concerns. We may come to learn that there are solutions that don't require a lot of technical maneuvering, or perhaps learn there's no need for change at all.

Akshay and Murtuza, I'm happy to work with you on doing some research (interviews to discover user needs and pains, creating mockups, getting feedback etc) and coming up with some solutions based on user feedback.

Hi Dave

As per discussion I have changed the logic of showing partitioned table in browser tree. Attached is the screenshot. 

Let me know your thoughts.   

Hi Dave

As per discussion I have changed the logic of showing partitioned table in browser tree. Attached is the screenshot. 

Let me know your thoughts.

  

On Thu, Apr 27, 2017 at 1:44 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang <swang@pivotal.io> wrote:

Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <dpage@pgadmin.org> wrote:

Hi

[moving to the pgadmin-hackers mailing list as this a pgAdmin feature]

On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave

Murtuza and I started thinking about "How to add Declarative Partitioning" support in pgAdmin4. We thought instead of showing Partition Table under existing Tables collection, we should add new collection node "Partition Tables". Showing table under the table node recursively will require lots of code changes in table and it's child nodes (column, index, trigger, etc..) which is more complex and error prone. 

Perhaps, but from the user's perspective, there's no reason to list them separately - they are just tables with a different structure from others. We shouldn't confuse the user just because it's more convenient for us.

I really think it should look like this:

- Tables

  - t1

    - Columns

    - Constraints

    - Partitions

      - p1

        - Sub Objects (whatever they may be)

        ...

      - p2

      ...

  - t2

  ...

   

 

Below is the design that we can implement: 

• Create new "Partition Tables" collection node. User will be able to create partition table by clicking "Create -> Partition Table" menu that we will add on collection node. We will share the dialog prototype later once we will have complete understanding of it.

Can you share a mock-up of the dialog? The Figma tool that Shirley shared looks like it'll be good for doing that - I can invite you to the team. 

• Once table is created user will be able to create partitions by clicking "Create -> Partitions" menu will be added on each partitioned table node. We will share the dialog prototype later once we will have complete understanding of it.

I would expect the user to be able to define the partitioning scheme when they create the table; e.g. on a new tab. It shouldn't be a two step process. 

• We will have to show sub nodes like (column, index, trigger, constraints, etc..) on main table while some of the sub nodes won't require for partitions like (column and many more again require some more knowledge on partitioning).

OK.

 

Apart from above we will have to figure out following:

• How to remove partitions(table) from existing tables node as value of relkind column is 'r' for partitions.
• Partitioning scheme to show in SQL pane for partitions.
  
• Some unknown issue/features of Declarative partitioning. 

OK.

Seems like there are a couple of assumptions being made here:

- Users need to see partitioned tables when expanding parent table

If by "assumption" you mean "fact", then yes :-). Users need to be able to see and manipulate partitions. Whilst some sub-objects are defined on the parent table (e.g. the columns), others are defined on the individual partitions (e.g. triggers, indexes).

 

- Users need to view partitioned tables in context to their parent table (Dave says yes, Akshay and Murtuza say no)

That's not what was said. Akshay and Murtuza were proposing a new collection node, e.g.

- Schema

  - Functions

  - Partitioned Tables

  - Tables

  - Views

I'm saying that that unnecessarily complicates things for the user. The fact that a table happens to use declarative partitioning, doesn't make it a different type of object as far as Postgres is concerned, nor should it for us.

 

- Users want to create a partitioned table through the browser (Akshay and Murtuza say yes, Dave says no)

I didn't say that. I said it shouldn't be a two-part process.

 

Plus some technical concerns:

- Making code changes in table is complex and error prone

- How to move partitions from one node to another

I think the first assumption is important to validate or invalidate before even thinking about how to implement or addressing technical concerns. We may come to learn that there are solutions that don't require a lot of technical maneuvering, or perhaps learn there's no need for change at all.

Akshay and Murtuza, I'm happy to work with you on doing some research (interviews to discover user needs and pains, creating mockups, getting feedback etc) and coming up with some solutions based on user feedback.

How would users come up with feedback, given that the feature doesn't exist in the field yet? 

--

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

On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave

As per discussion I have changed the logic of showing partitioned table in browser tree. Attached is the screenshot. 

Let me know your thoughts.   

Greenplum has had declarative partitioning for quite some time, I haven't spent much time diving into the Postgres implementation specifically, however, we have had some pain and I would suggest a little bit of thought behind this. 

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.

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

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.

I don't see that we have any choice but to display them so users can work with them.

• 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)? 

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.

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. 

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

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

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

On Tue, May 2, 2017 at 10:56 AM 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"? 

Is there a reason why 'Partition' needs to open in a new tab? If there's only one field, we should include it on the same page since the tabs don't dictate necessary steps in a sequential order. Users will be able to find what they need without navigating to another part of the dialog.

Example:

• 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

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

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. 

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

Thanks!

--

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

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

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

     I have updated Redmine Wiki page regarding what needs to be implemented for partitioning. Can we discuss prioritisation of the task based on above criteria in the our meeting. Meanwhile I have started working on showing correct SQL for partitioned table.  

On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

     I have updated Redmine Wiki page regarding what needs to be implemented for partitioning. Can we discuss prioritisation of the task based on above criteria in the our meeting. Meanwhile I have started working on showing correct SQL for partitioned table.  

Which meeting? 

--

Dave Page
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Hi Akshay!

Is this the workflow that you think users are going to engage in given what you're building? Anne mentioned you're in the process of figuring out what's required for defining the partitions, so you'll notice pink boxes with text in areas where that might happen. 

The modules that appear for partitioning are based on the ones we saw a few weeks ago, let me know if that has changed in any way.

01 user creates a table, if one doesn't already exist

02 user selects 'yes' for partitioning

03 user defines type of partition and ranges

04 After hitting submit, browser is updated with new table and partitions, and user is taken to SQL tab. (What is this step for?)

You mentioned Postgres users need to go into individual partitions frequently and its common to have different indexes for each partition. However, I wonder if the naming convention created will provide enough context for people to remember which partition has the specific properties they're looking for. If we imagine a scenario where a user has more than 30 or 40 partitions, and they need to look for one partition, it might be quite time consuming for people to find the right one.

 

We're trying to find Postgres users to test this flow with, especially since the success of this design relies on users knowing how to interact with the partitions in their browser. 

On Thu, May 18, 2017 at 6:41 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All

I have started implementation for Declarative Partitioning in pgAdmin4. Following are the tasks that I have implemented till now:

• Show partitioned table and it's partitions under the parent table. Refer Partitioned_Table.png
  
• To implement above I have created 'partitions' collection node and 'partition' node under table node which is nothing but table node itself. To reduce redundant/duplicate code I have made following changes:
  • Create new file "utils.py" under tables folder. Create a new class BaseTableView(PGChildNodeView): derived from PGChildNodeView. TableView and PartitionsView (new class for partition table) is derived from BaseTableView.
  • Move the common logic like dependencies, dependents, reversed engineered sql, statistics, reset statistics in BaseTableView class functions and then call that function from derived class like BaseTableView.get_table_dependencies(self, tid)
  • Will move more generic logic as we progress on this task.
• Updated supported nodes list in DataGrid(View Data), Backup, Maintenance, Restore to show context menu for partitions.
• Make sure dependencies, dependents, statistics, truncate, delete/drop and Reset Statistics works with partitions.
• Updated jinja template to show correct reversed engineered sql for partitioned table. Please refer the "List_with_expression.png" for List partition and "Range_with_column_expression.png" for Range partition. 
• Updated jinja template to show correct sql for partitions of parent table. Please refer "SQL_Range_Partitions.png" and "SQL_List_Partitions.png". Some R&D is still require for other syntax too.

Please let me know above looks good and am I going in right direction.

On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave 

On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:

On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

     I have updated Redmine Wiki page regarding what needs to be implemented for partitioning. Can we discuss prioritisation of the task based on above criteria in the our meeting. Meanwhile I have started working on showing correct SQL for partitioned table.  

Which meeting? 

   Meeting with Shirley, which wasn't schedule last Friday as I was on leave.    

--

Dave Page
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

--

Akshay Joshi

Principal Software Engineer 

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

--

Akshay Joshi

Principal Software Engineer 

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

Also, I forgot to mention, these mockups are in Figma: https://www.figma.com/file/RnmYyytaZdjsDHQWrMw5U0co/Create-partition-table?node-id=16%3A54

Feel free to play around and change things :)

On Thu, May 18, 2017 at 11:36 AM Shirley Wang <swang@pivotal.io> wrote:

Hi Akshay!

Is this the workflow that you think users are going to engage in given what you're building? Anne mentioned you're in the process of figuring out what's required for defining the partitions, so you'll notice pink boxes with text in areas where that might happen. 

The modules that appear for partitioning are based on the ones we saw a few weeks ago, let me know if that has changed in any way.

01 user creates a table, if one doesn't already exist

02 user selects 'yes' for partitioning

03 user defines type of partition and ranges

04 After hitting submit, browser is updated with new table and partitions, and user is taken to SQL tab. (What is this step for?)

You mentioned Postgres users need to go into individual partitions frequently and its common to have different indexes for each partition. However, I wonder if the naming convention created will provide enough context for people to remember which partition has the specific properties they're looking for. If we imagine a scenario where a user has more than 30 or 40 partitions, and they need to look for one partition, it might be quite time consuming for people to find the right one. 

We're trying to find Postgres users to test this flow with, especially since the success of this design relies on users knowing how to interact with the partitions in their browser. 

On Thu, May 18, 2017 at 6:41 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All

I have started implementation for Declarative Partitioning in pgAdmin4. Following are the tasks that I have implemented till now:

• Show partitioned table and it's partitions under the parent table. Refer Partitioned_Table.png
  
• To implement above I have created 'partitions' collection node and 'partition' node under table node which is nothing but table node itself. To reduce redundant/duplicate code I have made following changes:
  • Create new file "utils.py" under tables folder. Create a new class BaseTableView(PGChildNodeView): derived from PGChildNodeView. TableView and PartitionsView (new class for partition table) is derived from BaseTableView.
  • Move the common logic like dependencies, dependents, reversed engineered sql, statistics, reset statistics in BaseTableView class functions and then call that function from derived class like BaseTableView.get_table_dependencies(self, tid)
  • Will move more generic logic as we progress on this task.
• Updated supported nodes list in DataGrid(View Data), Backup, Maintenance, Restore to show context menu for partitions.
• Make sure dependencies, dependents, statistics, truncate, delete/drop and Reset Statistics works with partitions.
• Updated jinja template to show correct reversed engineered sql for partitioned table. Please refer the "List_with_expression.png" for List partition and "Range_with_column_expression.png" for Range partition. 
• Updated jinja template to show correct sql for partitions of parent table. Please refer "SQL_Range_Partitions.png" and "SQL_List_Partitions.png". Some R&D is still require for other syntax too.

Please let me know above looks good and am I going in right direction.

On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave 

On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:

On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

     I have updated Redmine Wiki page regarding what needs to be implemented for partitioning. Can we discuss prioritisation of the task based on above criteria in the our meeting. Meanwhile I have started working on showing correct SQL for partitioned table.  

Which meeting? 

   Meeting with Shirley, which wasn't schedule last Friday as I was on leave.    

--

Dave Page
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

--

Akshay Joshi

Principal Software Engineer 

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

--

Akshay Joshi

Principal Software Engineer 

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

Hi All

I have started implementation for Declarative Partitioning in pgAdmin4. Following are the tasks that I have implemented till now:

• Show partitioned table and it's partitions under the parent table. Refer Partitioned_Table.png
  
• To implement above I have created 'partitions' collection node and 'partition' node under table node which is nothing but table node itself. To reduce redundant/duplicate code I have made following changes:
  • Create new file "utils.py" under tables folder. Create a new class BaseTableView(PGChildNodeView): derived from PGChildNodeView. TableView and PartitionsView (new class for partition table) is derived from BaseTableView.
  • Move the common logic like dependencies, dependents, reversed engineered sql, statistics, reset statistics in BaseTableView class functions and then call that function from derived class like BaseTableView.get_table_dependencies(self, tid)
  • Will move more generic logic as we progress on this task.
• Updated supported nodes list in DataGrid(View Data), Backup, Maintenance, Restore to show context menu for partitions.
• Make sure dependencies, dependents, statistics, truncate, delete/drop and Reset Statistics works with partitions.
• Updated jinja template to show correct reversed engineered sql for partitioned table. Please refer the "List_with_expression.png" for List partition and "Range_with_column_expression.png" for Range partition. 
• Updated jinja template to show correct sql for partitions of parent table. Please refer "SQL_Range_Partitions.png" and "SQL_List_Partitions.png". Some R&D is still require for other syntax too.

Please let me know above looks good and am I going in right direction.

On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi Dave 

On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:

On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

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. 

OK, good. So now, let's break that down into a list of tasks, that we can prioritise with Shirley. The initial list should be prioritised based on your understanding I think, given the following criteria:

- Changes that prevent pgAdmin breaking

- Changes that prevent pgAdmin showing incorrect data/info

- Changes that enable pgAdmin to show correct info

- Changes that add functionality for creating/dropping partitioned tables as one unit

- Changes that add functionality for modifying individual partitions independently

Please document the requirements and initial plan on the pgAdmin Redmine Wiki.

     I have updated Redmine Wiki page regarding what needs to be implemented for partitioning. Can we discuss prioritisation of the task based on above criteria in the our meeting. Meanwhile I have started working on showing correct SQL for partitioned table.  

Which meeting? 

   Meeting with Shirley, which wasn't schedule last Friday as I was on leave.    

--

Dave Page
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

--

Akshay Joshi

Principal Software Engineer 

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

--

Akshay Joshi

Principal Software Engineer 

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

On Mon, May 22, 2017 at 5:28 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All 

As continuation I have worked on "Partition" Tab and added some controls where user will be able to specify the key columns and create N number of partitions:

• Added Partition Type combo box where user will define the Type(Range/List)
• "Partition Keys" subnode control where there is combo box to specify it is column or expression. If it is column then on expanding subnode control all the columns specified to create main table will be listed here. User won't be able to select multiple columns here. If it is expression then user will be able to specify correct expression with correct parenthesis. In case of List partition only one row will be allowed.

To get out an MVP, perhaps we should focus on just establishing a partition for a single range and list, then figure out how an expression would work. It seems like the majority of use cases for using a range partition would be by date so let's get that functionality working and worry about expressions once we have live feedback to minimize risk (excess time spent developing features with many unknowns about user behavior)

• "Partitions" control is used to create N number of partitions. In case of Range partition there are three columns "Name", "Value From" and "Value To". If partition key is combination of multiple columns or combination of column and expression then user will have to provide (,) comma separated values. Refer "Range_Partition.png". In case of List partition there are two columns "Name" and "Values In" and user will have to provide (,) separated list for "Values In" column. Refer "List_Partition.png".

Here's a sketch of what a single range would look like:

 

 

Note: Apart from above there are following that needs to be taken care:

• Once user will define the partition keys and partitions based on the columns define for main table and then user will rename/delete any column from "Columns" Tab we will warn user about renaming/deleting any column will reset all the rows define under partitions tab. This is just because we can rename/delete the column from partition keys but not sure how we can remove/rename it from the expression defined by the user.

That's fine, as long as they are still able to do so after a warning.

• Major challenge here is while creating partitions(second subnode control in attached image) according to the documentation user will be able to create constraints( Primary, Foreign, Unique...). How user will be able to add constraints from GUI? Do we need to implement a new control where one subnode control contains the whole "Constraints" Tab or any other solution.    
  

We can probably leverage the same table. We're investigating constraints as well.

Shirley & Rob