Re: Declarative partitioning in pgAdmin4

Hi Dave

On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave(dot)page(at)enterprisedb(dot)com>
wrote:

>
>
> On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>
>> Hi
>>
>> On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Hi
>>>
>>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <
>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi All
>>>>
>>>> On Wed, May 3, 2017 at 5:35 PM, Dave Page <dpage(at)pgadmin(dot)org> 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?
>

Meeting with Shirley, which wasn't schedule last Friday as I was on
leave.

>
> --
> Dave Page
> VP, Chief Architect, Tools & Installers
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>

--
*Akshay Joshi*
*Principal Software Engineer *

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