Re: Declarative partitioning in pgAdmin4

Hi Dave

As per discussion I have changed the logic of showing partitioned table in
browser tree. Attached is the screenshot.
Let me know your thoughts.

On Thu, Apr 27, 2017 at 1:44 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

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

--
*Akshay Joshi*
*Principal Software Engineer *

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