Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

Hi All 

Attached is the latest WIP patch. Following task is completed

• User will be able to create partitioned table (Range and List) using columns and expression as partition key.
• User will be able to create N partitions while creating the partitioned table itself.
  
• User will be able to see SQL of all partitions (child tables) along with the partitioned table in SQL pane when select parent node.
• Controls are disabled/hide from table dialog which are not supported by Partitioning.
• User will be able to create/attach/detach N no of partitions from the parent table dialog.
• User will be able to detach partition by selecting and clicking on "Detach Partition" menu. Visible only on partitions.

Following tasks are remaining:

• Refresh Tables/Partitions collection when any new node is created or removed from the collection. For example user will detach/create/attach N no of partitions from parent table in that case we will have to refresh the complete Tables/Partitions collection. Need some suggestions how we can achieve it. 
• Table dialog for child tables(Partitions). (Harshal working on it.)
• Displaying Constraints, Indexes, Rules, Triggers collection/node when expanding partition node. (Harshal working on it.)

Please review/run the latest patch and let me know your thoughts/suggestions on it.

On Mon, Jun 19, 2017 at 11:29 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

On Fri, Jun 16, 2017 at 11:16 PM, Shirley Wang <swang@pivotal.io> wrote:

Looks good. I noticed people clicking back and forth to the columns tab to remember which columns they've created while filling out the Expressions column. It might be better to have a list of the columns and the datatype above the 'Partition Keys' subnode and have columns as a type field rather than a drop down.

   I think we should not duplicate that data as we already have all the information on "Columns" tab and by providing drop down user can select columns from there only. 

Also, I think the fields someone sees after selecting the Key type needs to depend on what they select. Seeing both Column and Expressions type field might lead someone to think they need to fill out both fields.

   We can't, because user can select one column and provide an expression as partition key in this case we will have to show both the columns in subnode control. Anyways when user select columns I have disabled the expression cell and if user selects expression column cell is disabled.  

When is the 'In' column in the Partitions subnode enabled? 

    In case of 'List' Partition. 

For the NoteControl on the bottom, what do 'Mode Control' or 'Attach Mode' refer to? And how can I tell the difference between 'Create Mode' and 'Edit Mode'?

   'Mode control' is a switch control in subnode control that should be "Mode switch control". 'Create Mode' is when user creates the new table by clicking create-> table and 'Edit Mode' is when user open the properties dialog for the existing table. In case of 'Edit Mode' there are two ways user can create/attach partitions. In Attach mode we will identify and list down the suitable tables to be attached. 

On Thu, Jun 15, 2017 at 2:12 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

On Wed, Jun 14, 2017 at 5:39 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

On Wed, Jun 14, 2017 at 1:59 PM, Dave Page <dpage@pgadmin.org> wrote:

On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All 

For further implementation following task needs to be work upon:

• How to parse and show partitions keys. For example user has created below partitioned table 

CREATE TABLE public.sales

(

    country character varying COLLATE pg_catalog."default" NOT NULL,

    sales bigint,

    saledate date

) PARTITION BY RANGE (country, date_part('year'::text, sale date))

When user open the properties dialog I am not able to figure out how to parse keys(displayed in bold in above example) and show them in our control that we used. For the time being I have hide that control in 'Edit' mode (Refer Attach Partition.png)

I assume psql with display that info with \dt or similar? What does it do? Failing that, look at pg_dump?

psql, and pg_dump use 'pg_get_partkeydef' function for reverse engineering, and we too.

They don't need particular key information.

In properties dialog, we need to find out - what individual partition key is? (column/expression).

Let me give an example.

I have a partition table with the following definition (with two partition keys).

CREATE TABLE public.sales

(

    country character varying COLLATE pg_catalog."default" NOT NULL,

    sales bigint,

    saledate date

) PARTITION BY RANGE (country, EXTRACT(year from saledate));

And, the following query will give as: 

SELECT relname, pg_get_partkeydef(oid) FROM pg_catalog.pg_class WHERE relname like 'sal%';

 relname |                 pg_get_partkeydef

---------+----------------------------------------------------

 sales   | RANGE (country, date_part('year'::text, saledate))

Here - we have two option in edit mode.

1. Parse the output of the  'pg_get_partkeydef', and identify all individual keys, and its detailed information (i.e. column/expression)

2. Show that output about the partition keys in static control, and hide the Partition type, partition keys controls.

I prefer the second option, as user can not modify the partition keys/type (RANGE/LIST), and we will not have to write logic to parse the keys from that output.

What do you say?

I agree.

   I have modified the logic as per above suggestion. In create mode we will show "Partition Type" and "Partition Keys", so that user will be able to create partitioned table and in edit mode we will show "Partition Scheme" in NoteControl, as it's been difficult to parse and identify whether it is a column or expression. Please refer Create_Table.png. 

  Apart from that as per discussion with Dave yesterday I have remove the "Attach Partition" control and merge that functionality into "Partitions" control. I have added one switch control with text (Attach/Create). By default this control is disabled in create mode, while in edit mode user can create/attach partitions. When user select create then "Name" is input control and when user selects attach then "Name" is select2 control containing list of all the suitable(with same columns, datatype and oids ) tables to be attached. Refer Edit_Table.png

 I have also added one NoteControl at the bottom which will give information about the Partitions control how to use that. Please correct the string if it looks wrong.        

 

-- Thanks, Ashesh

 

• Support of sub partitioning: To implement sub-partitioning, specify the PARTITION BY clause in the commands used to create individual partitions, for example:

• CREATE TABLE measurement_y2006 PARTITION OF measurement   FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')   PARTITION BY RANGE (peaktemp);

           To achieve above I have made some changes in GUI (Refer Sub Partition.png). 

           Complex and challenging part here is "measurement_y2006" is partition of "measurement" and parent table for other partitions too which user can create later. How we will going to show this in browser tree? 

           One option could be

           Tables

             ->measurement(table)

               ->Partitions

                 ->measurement_y2006(Partition of measurement and parent of p1) 

                   ->Partitions

                     ->p1 

Urgh. But yeah. I think that makes logical sense.

 

• Attach Partitions: To implement attach N partitions I have made some changes in GUI( Refer Attach Partition.png). Attach Partitions control will only be visible in "Edit" mode.

I have only modified the UI changes, there are lots of work needs to be done to complete that.  

I don't think I'd include Attach on the dialog. I think it should be a separate menu option, with a simple dialogue to let the user choose the table to attach.

The reason for that is that Attach is an action not a property. On the Properties panels we expect any changes we make to be the same the next time the dialogue is opened - e.g. if you toggle "Enable Trigger" to disabled and hit OK, then next time you open the dialogue you see the switch in the same position. With Attach, that's not the case - you'll list one or more tables to attach, hit OK, and when you next open the Properties dialogue, those partitions will be listed in the partition list, not the Attach list.

 

Please review the design. Suggestions/Comments are welcome.

  

On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt <reckhardt@pivotal.io> wrote:

On Tue, Jun 6, 2017 at 4:32 AM, Dave Page <dpage@pgadmin.org> wrote:

For roll up this pattern seems obvious, identify the n partitions you need/want to combine and then run a job to combine them. 

You're thinking Greenplum :-). There is no roll up in PostgreSQL, unless you're thinking we should create such a feature in pgAdmin.

Of course, I have no objection to extending what we do in PG to add GP feature support, but let's start with PG.

No not at all. That was a very specific and consistent pattern described by users leveraging time based range partitions in Postgres. I'm not sure if that same use case will be supported with partitioning as implemented in Postgres 10 but it is a Postgres pattern. 

-- Rob

 

 

For other patterns such as creating indexes and such it requires a bit more thought. Generally users described wanting to treat all of the children like a single table (just like Oracle), however, other users described potentially modifying chunks of partitions differently depending on some criterion. This means that users will need to identify the subset they want to optimize and then ideally be able to act on them all at once. 

Right.

 

-- Rob

 

So... it sounds like we're on the right lines :-)

 

For the former, this can be addressed by enabling users to modify one or more child partitions at the same time. For the latter, that is a workflow that might be addressed outside of the create table with partition workflow we're working on currently. 

On Mon, Jun 5, 2017 at 5:21 AM Dave Page <dpage@pgadmin.org> wrote:

On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All 

Following are the further implementation updates to support Declarative Partitioning:

• Show all the existing partitions of the parent table in Partitions tab (Refer Existing_Partitions.png)
• Ability to create N partitions and detach existing partitions. Refer (Create_Detach_Partition.png), in this example I have detach two existing partition and create two new partitions.
• Added "Detach Partition" menu to partitions node only and user will be able to detach from there as well. Refer (Detach.png)   

That's looking good to me :-)

 

On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt <reckhardt@pivotal.io> wrote:

On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

   Taking average of two columns is just an example/representation of expression, there is no use case of that. As I am also in learning phase. Below are some use case that I can think of:

• Partitions based on first letter of their username
  

  CREATE TABLE users (
      id             serial not null,
      username       text not null,
      password       text,
      created_on     timestamptz not null,
      last_logged_on timestamptz not null
  )PARTITION BY RANGE ( lower( left( username, 1 ) ) );
  CREATE TABLE users_0
      partition of users (id, primary key (id), unique (username))
      for values from ('a') to ('g');
  CREATE TABLE users_1
      partition of users (id, primary key (id), unique (username))
      for values from ('g') to (unbounded);

•  Partition based on country's sale for each month of an year.

CREATE TABLE public.sales

(

    country text NOT NULL,

    sales bigint NOT NULL,

    saledate date

) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)), (extract(MONTH FROM saledate)))

CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales

    FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02);

CREATE TABLE public.sale_india_2017_jan PARTITION OF sales

    FOR VALUES FROM ('india', 2017, 01) TO ('india', 2017, 02);

CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales

    FOR VALUES FROM ('uk', 2017, 01) TO ('uk', 2017, 02);

INSERT INTO sales VALUES ('india', 10000, '2017-1-15');

INSERT INTO sales VALUES ('uk', 20000, '2017-1-08');

INSERT INTO sales VALUES ('usa', 30000, '2017-1-10');

   Apart from above there may be N number of use cases that depends on specific requirement of user. 

Thank you for the example, you are absolutely correct and we were confused. 

Given our new found understanding do you mind if we iterate a bit on the UI/UX?  What we were suggesting with the daily/monthly/yearly drop down was a specific example of an expression. Given that fact that doesn't seem to be required in an MVP, however, I do think a more interactive experience between the definition of the child partitions and the creation of the partitions would be optimal. 

I'm not sure where you are with respect to implementing the UI but I'd love to float some ideas and mock ups past you. 

-- Rob

--

Akshay Joshi

Principal Software Engineer 

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

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.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

--

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

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

--

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
Mobile: +91 976-788-8246

--

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

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

--

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
Mobile: +91 976-788-8246

--

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