Re: Declarative partitioning in pgAdmin4

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.

Thanks!

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

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