Re: Declarative partitioning in pgAdmin4

On Thu, Apr 27, 2017 at 12:01 PM, Akshay Joshi <
akshay(dot)joshi(at)enterprisedb(dot)com> wrote:

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

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 <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-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246
> <+91%2097678%2088246>*
>

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

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