Re: Declarative partitioning in pgAdmin4

On Wed, Apr 26, 2017 at 6:36 PM, Shirley Wang <swang(at)pivotal(dot)io> wrote:

> Hello!
>
> On Wed, Apr 26, 2017 at 4:26 AM Dave Page <dpage(at)pgadmin(dot)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(dot)joshi(at)enterprisedb(dot)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