Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

Поиск

Список

Период

Сортировка

От	Akshay Joshi
Тема	Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Дата	19 мая 2017 г. 12:17:02
Msg-id	CANxoLDfN_RvNc0AsVCtrDC-03L53crHzE8JZjmxna3f08KWVqw@mail.gmail.com обсуждение исходный текст
Ответ на	Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4 (Shirley Wang <swang@pivotal.io>)
Список	pgadmin-hackers

Дерево обсуждения

Hi Shirley

On Thu, May 18, 2017 at 9:06 PM, Shirley Wang <swang@pivotal.io> wrote:

Hi Akshay!

Is this the workflow that you think users are going to engage in given what you're building? Anne mentioned you're in the process of figuring out what's required for defining the partitions, so you'll notice pink boxes with text in areas where that might happen.

The modules that appear for partitioning are based on the ones we saw a few weeks ago, let me know if that has changed in any way.

01 user creates a table, if one doesn't already exist

02 user selects 'yes' for partitioning

Step 1 and 2 are correct.

03 user defines type of partition and ranges

We will create a new tabs "Partitions" and 'Partition Type' combo will go on that tab along with following controls:

User should be able to specify Key Column(s) (Based on partition type) to create partitioned(parent) table. Some of the examples of list and range partitions are as below:
- CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
- CREATE TABLE prt1_e (a int, b int, c varchar) PARTITION BY RANGE(((a + b)/2));
- CREATE TABLE prt1_m (a int, b int, c varchar) PARTITION BY RANGE(a, ((a + b)/2));
- CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
- CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A'));
User should be able to 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(GUI) discussion required for above two, which control should we used so that user will be easily able to create N number of partitions.

04 After hitting submit, browser is updated with new table and partitions, and user is taken to SQL tab. (What is this step for?)

When user click on "OK" button of create table dialog, browser tree will be updated, but i am not sure SQL tab comes in focus, no need of that.

You mentioned Postgres users need to go into individual partitions frequently and its common to have different indexes for each partition. However, I wonder if the naming convention created will provide enough context for people to remember which partition has the specific properties they're looking for. If we imagine a scenario where a user has more than 30 or 40 partitions, and they need to look for one partition, it might be quite time consuming for people to find the right one.

User will provide the name of his/her choice while creating partitions from 'Create Table' dialog, so it's upto the user.

We're trying to find Postgres users to test this flow with, especially since the success of this design relies on users knowing how to interact with the partitions in their browser.
On Thu, May 18, 2017 at 6:41 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi All

I have started implementation for Declarative Partitioning in pgAdmin4. Following are the tasks that I have implemented till now:
Show partitioned table and it's partitions under the parent table. Refer Partitioned_Table.png
To implement above I have created 'partitions' collection node and 'partition' node under table node which is nothing but table node itself. To reduce redundant/duplicate code I have made following changes:
Create new file "utils.py" under tables folder. Create a new class BaseTableView(PGChildNodeView): derived from PGChildNodeView. TableView and PartitionsView (new class for partition table) is derived from BaseTableView.
Move the common logic like dependencies, dependents, reversed engineered sql, statistics, reset statistics in BaseTableView class functions and then call that function from derived class like BaseTableView.get_table_dependencies(self, tid)
Will move more generic logic as we progress on this task.
Updated supported nodes list in DataGrid(View Data), Backup, Maintenance, Restore to show context menu for partitions.
Make sure dependencies, dependents, statistics, truncate, delete/drop and Reset Statistics works with partitions.
Updated jinja template to show correct reversed engineered sql for partitioned table. Please refer the "List_with_expression.png" for List partition and "Range_with_column_expression.png" for Range partition.
Updated jinja template to show correct sql for partitions of parent table. Please refer "SQL_Range_Partitions.png" and "SQL_List_Partitions.png". Some R&D is still require for other syntax too.
Please let me know above looks good and am I going in right direction.
On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi Dave
On Thu, May 11, 2017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:
On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi
On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org> wrote:
Hi
On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi All
On Wed, May 3, 2017 at 5:35 PM, Dave Page <dpage@pgadmin.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:
View Table data : No need to change any logic, it's working.
Correct jinja template to show correct SQL in SQL pane.
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.
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.
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.
Drop/ Drop cascade, Truncate: No need to change any logic, it's working.
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.
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:
View Table Data: Add context menu.
Detach partition: Create context menu, when user click popped up confirmation message box.
View partition scheme in SQL pane: Changes required in jinja template.
Create primary/foreign/.. key constraint: No need to change any logic on GUI, but may need to change queries to fetch the partitioned tables.
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-9517
Mobile: +91 976-788-8246
--
Akshay Joshi
Principal Software Engineer

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