Re: Declarative partitioning in pgAdmin4

Akshay, Dave,

I'm filling in for Shirley this week. If you are referring to the weekly
meeting tomorrow, I'm planning on joining that and can walk through an
exercise to help figure out prioritization.

Thanks,
Anne

On Thu, May 11, 2017 at 9:24 AM 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?
>
> --
> Dave Page
> VP, Chief Architect, Tools & Installers
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
--
Anne Thomas
Product Manager
Pivotal Labs