Quantcast

Re: Declarative partitioning in pgAdmin4

Next Topic
 
classic Classic list List threaded Threaded
36 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
Hi

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

On Wed, Apr 26, 2017 at 8:20 AM, Akshay Joshi <[hidden email]> 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.
 
The above implementation may take more time, so it might possible that we may not be able to finish it by 14th May (deadline).

It would be nice to have it by then, but the true deadline will be a later beta (TBD, but probably beta 2 which is sufficiently far off).

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Shirley Wang
Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <[hidden email]> 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 <[hidden email]> 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.

 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi


On Wed, Apr 26, 2017 at 11:06 PM, Shirley Wang <[hidden email]> wrote:
Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <[hidden email]> 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 <[hidden email]> 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.

    Sure, it would be great. We will require some R&D about which feature/controls(Inheritance, Constraints, Indexes, Triggers and may be more) works with partitioning and which we will have to disabled if user will create partition table.  

 



--
Akshay Joshi
Principal Software Engineer 


Phone: +91 20-3058-9517
Mobile: +91 976-788-8246
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
In reply to this post by Shirley Wang


On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang <[hidden email]> wrote:
Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
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 <[hidden email]> wrote:


On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang <[hidden email]> wrote:
Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <[hidden email]> 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 <[hidden email]> 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


--
Sent via pgadmin-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

Partition Table.png (173K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Robert Eckhardt


On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshi <[hidden email]> 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. 
-- Rob
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
In reply to this post by Akshay Joshi


On Thu, Apr 27, 2017 at 12:01 PM, Akshay Joshi <[hidden email]> 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.

That's pretty much what I had in mind, yes. There are certain object types that would need to be rendered under the partitions themselves instead of the parent though.

 
  

On Thu, Apr 27, 2017 at 1:44 PM, Dave Page <[hidden email]> wrote:


On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang <[hidden email]> wrote:
Hello!

On Wed, Apr 26, 2017 at 4:26 AM Dave Page <[hidden email]> 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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246



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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
In reply to this post by Robert Eckhardt


On Thu, Apr 27, 2017 at 3:18 PM, Robert Eckhardt <[hidden email]> wrote:


On Thu, Apr 27, 2017 at 7:01 AM, Akshay Joshi <[hidden email]> 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.
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)? 


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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Robert Eckhardt
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
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.
  2. 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.
  3. 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.
  4. 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".
  5. 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 <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

Partition_Switch.png (135K) Download Attachment
Partition_Tab.png (94K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Shirley Wang


On Tue, May 2, 2017 at 10:56 AM Dave Page <[hidden email]> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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:
tablepartition-1.png
tablepartition-2.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. 
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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
Hi Shirley 

On Wed, May 3, 2017 at 3:31 AM, Shirley Wang <[hidden email]> wrote:


On Tue, May 2, 2017 at 10:56 AM Dave Page <[hidden email]> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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.

    There are lots of controls yet to design like column(s), number of partitions, partition schemes will be part of "Partitions" Tab. We will need that.

Example:
tablepartition-1.png
tablepartition-2.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. 
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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
In reply to this post by Dave Page-7
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 <[hidden email]> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
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 <[hidden email]> 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 <[hidden email]> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246



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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Shirley Wang
Hi!

On Wed, May 3, 2017 at 8:08 AM Dave Page <[hidden email]> 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.

Either way we implement this feature, we should test the workflow of how people go through table partitioning with users to get validation on whether or not our decisions make sense for them. 
 

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 <[hidden email]> 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
It seems like the operations above detail a potential full feature set for table partitioning which is a good starting point. It would be worthwhile to consider what's the minimum we need to include for the first release of table partitioning. 

As we get live feedback and release frequently, we can add additional features and fix bugs. If we cut the scope of this, we'll be more confident that we can reach the deadline and deliver user value.

We can determine what should be included by plotting these features within a matrix. Typically user value is on one axis and technical complexity on another, although these can change depending on what your team needs. 
2x2-solution_prioritization.jpgWe've found that this matrix is really helpful in answering "What's the smallest thing we can build that solves the most important problems?". We typically do this as a team (engineers, designers, and product managers) once we have enough context about user behavior and technical complexity.

I can facilitate a session where we run through this exercise. It typically takes about an hour.


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
In reply to this post by Dave Page-7
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
Hi

On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <[hidden email]> 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 <[hidden email]> 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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246


--
Sent via pgadmin-hackers mailing list ([hidden email])
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: <a href="tel:+91%2020%203058%209517" value="+912030589517" target="_blank">+91 20-3058-9517
Mobile: <a href="tel:+91%2097678%2088246" value="+919767888246" target="_blank">+91 976-788-8246



--
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-7
Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <[hidden email]> wrote:
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Akshay Joshi
Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <[hidden email]> wrote:
Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <[hidden email]> wrote:
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <[hidden email]> 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.  

Thanks!

--
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Declarative partitioning in pgAdmin4

Dave Page-6


On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <[hidden email]> wrote:
Hi 

On Thu, May 4, 2017 at 4:00 PM, Dave Page <[hidden email]> wrote:
Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <[hidden email]> wrote:
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <[hidden email]> 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
12
Next Thread
Loading...