Re: Declarative partitioning in pgAdmin4

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.

>
>
> On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>
>> Hi Dave
>>
>> As per my understanding below operations required
>>
>> Parent:
>>
>> - View table data.
>> - View stats.
>> - Create regular/partitioned table
>> - Create N number of partitions.
>> - Drop/ Drop cascade, Truncate.
>> - Attach/Detach Partitions.
>> - Not able to create constraints excluding check constraint.
>>
>> Child:
>>
>> - View Table Data.
>> - View stats.
>> - View partition scheme in SQL pane
>> - Create primary/foreign/.. key constraint.
>> - Drop/ Drop cascade, Truncate
>>
>>
>> On Tue, May 2, 2017 at 8:25 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Hi
>>>
>>> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <
>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi All
>>>>
>>>> To implement Declarative Partitioning in existing Table dialog
>>>> below changes should be implemented:
>>>>
>>>> 1. *Icon: *As we have separate icon for view and materialised view,
>>>> we should have for partition table. I didn't find any in font awesome.
>>>>
>>>> They are really different object types though (even having their own
>>> collections), which isn't the case here. I'm not against having a slightly
>>> modified icon, but I don't think it's necessary. Note that the object icons
>>> come from pgAdmin III, and were custom designed for us. They aren't in font
>>> awesome etc. We'd need to tweak one of the existing ones.
>>>
>>>>
>>>> 1. *Inheritance*:
>>>> - A partition cannot have any parents other than the partitioned
>>>> table it is a partition of, nor can a regular table inherit from a
>>>> partitioned table making the latter its parent. That means partitioned
>>>> tables and partitions do not participate in inheritance with regular tables.
>>>> - When user creates regular table then Inherited from table(s)
>>>> control should not display partitioned table.
>>>> 2. *Constraints*:
>>>> - Primary/Foreign/Unique/Exclusion constraints are not supported
>>>> on partitioned table. In that case respective controls should be disabled
>>>> for partitioned table.
>>>> - We will have to check which constraints are applicable on
>>>> partitions(of partitioned table) still some R&D require. Can someone help
>>>> me here.
>>>> - For regular tables in Foreign Key constraints tab References
>>>> control should not list partition tables.
>>>> - Check constraints : cannot add NO INHERIT constraint to
>>>> partitioned table, so that control is disabled for partition table.
>>>> 3. *Advanced Tab*:
>>>> - Relation works with partition table theirs is an error if
>>>> "With indexes?" is set to Yes, so we need to disabled that for partition
>>>> table.
>>>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill
>>>> factor" and "Of type".
>>>> 4. *Parameter Tab*:
>>>> - Gives error (unrecognized parameter "autovacuum_enabled") for
>>>> all parameters of Table Tab and working fine for "Toast Table"
>>>> it's working.
>>>>
>>>> Can you detail what operations someone would likely want (or need) to
>>> perform on the parent/child tables; e.g.
>>>
>>> Parent:
>>>
>>> - View stats
>>> - View data
>>> - Truncate
>>> - View/create columns
>>> - Bulk-create indexes
>>> - Bulk-create foreign keys
>>>
>>> Child:
>>>
>>> - View stats
>>> - View data
>>> - Truncate
>>> - Create indexes
>>> - Create foreign keys
>>>
>>>
>>>
>>>> Apart from above we will have to do following:
>>>>
>>>> - Required switch control to specify whether it is a regular table
>>>> or partitioned table. I have added it on General tab. Please refer
>>>> Partition_Switch.png
>>>> - Will have to add new tab "Partition" which will have one select2
>>>> control to define its Range partition or List partition. Refer
>>>> Partition_Tab.png
>>>>
>>>> "Partitions"?
>>>
>>>>
>>>> - Design following controls in *Partition* tab:
>>>> - How to add columns in case of Range/List partition? LIST
>>>> partition key supports only one column. For RANGE user can specify multiple
>>>> columns.
>>>> - How to specify expression, COLLATE while adding columns
>>>> for partition.
>>>> - We need subnode control so that user will add number of
>>>> partition with there values of the main table. Need lot of R&D for this.
>>>> - We will have to provide "Create partition", "Attach Partition"
>>>> and "Detech partition" context menu options on Partitions
>>>> collection node.
>>>>
>>>> OK.
>>>
>>> Thanks! This is a complex one :-(
>>>
>>>
>>>> Let me know if I forgot something to add that we may need to
>>>> handle/implement.
>>>>
>>>> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
>>>> wrote:
>>>>
>>>>> The issues we consistently face:
>>>>>>>
>>>>>>> - The huge (often thousands sometimes tens of thousands) number
>>>>>>> of partitions makes rendering all of the partitions painfully slow and
>>>>>>> frequently not useful.
>>>>>>>
>>>>>>> Perhaps, though I doubt that number would be common in Postgres. The
>>>>>> problem though, is that there are both stats and sub-objects (indexes and
>>>>>> triggers for example) that are part of the child partitions, not the parent
>>>>>> - and they may differ from partition to partition.
>>>>>>
>>>>>
>>>>> Certainly there differences in Postgres and Greenplum and this might
>>>>> very well be one of those places.
>>>>>
>>>>>
>>>>>> I don't see that we have any choice but to display them so users can
>>>>>> work with them.
>>>>>>
>>>>>
>>>>> We don't want to hide them, I do think we want to make accessing them
>>>>> a useful experience. If we rephrase this statement as "How might we display
>>>>> partitioned tables so that users are able to work with and modify the
>>>>> pieces they need?", this opens us up to different opportunities in how we
>>>>> display them.
>>>>>
>>>>> Even with a simple case of 90 days of data partitioned by day, a drop
>>>>> down showing 90 tables that are all mostly the same is a little
>>>>> overwhelming.
>>>>>
>>>>>
>>>>>>
>>>>>>> - When end users are interested in looking at their partitions
>>>>>>> they frequently don't want all of them displayed mindlessly
>>>>>>> - They are looking at a subset of partitions
>>>>>>> - Partitions are typically grouped around their inheritance
>>>>>>> properties.
>>>>>>>
>>>>>>> How might you propose grouping them (based on the way they work in
>>>>>> Postgres)?
>>>>>>
>>>>>
>>>>> Honestly I'm not sure. We didn't really start thinking about this
>>>>> until the other day so we are starting to look into the pains that
>>>>> Greenplum customers have. Sharing that pain we discover back to the pgAdmin
>>>>> community and seeing if it makes sense from a Postgres perspective. After
>>>>> that I need to dive into the Postgres implementation.
>>>>>
>>>>> -- Rob
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> *Akshay Joshi*
>>>> *Principal Software Engineer *
>>>>
>>>>
>>>>
>>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91
>>>> 976-788-8246 <+91%2097678%2088246>*
>>>>
>>>>
>>>> --
>>>> Sent via pgadmin-hackers mailing list (pgadmin-hackers(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>>
>>>>
>>>
>>>
>>> --
>>> 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
>

--
*Akshay Joshi*
*Principal Software Engineer *

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