Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

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

Hello!

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

Hi

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

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

Hi Dave

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

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

I really think it should look like this:

- Tables

  - t1

    - Columns

    - Constraints

    - Partitions

      - p1

        - Sub Objects (whatever they may be)

        ...

      - p2

      ...

  - t2

  ...

   

 

Below is the design that we can implement: 

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

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

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

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

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

OK.

 

Apart from above we will have to figure out following:

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

OK.

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

- Users need to see partitioned tables when expanding parent table

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

 

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

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

- Schema

  - Functions

  - Partitioned Tables

  - Tables

  - Views

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

 

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

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

 

Plus some technical concerns:

- Making code changes in table is complex and error prone

- How to move partitions from one node to another

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

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

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

--

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

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