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(dot)joshi(at)enterprisedb(dot)com> wrote:

>
>
> On Fri, Jun 16, 2017 at 11:16 PM, Shirley Wang <swang(at)pivotal(dot)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.
>
>>
>> [image: coluns_partitioning.png]
>> 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(dot)joshi(at)enterprisedb(dot)com> wrote:
>>
>>> On Wed, Jun 14, 2017 at 5:39 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>>
>>>>
>>>> On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi <
>>>> ashesh(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> On Wed, Jun 14, 2017 at 1:59 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi <
>>>>>> akshay(dot)joshi(at)enterprisedb(dot)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(at)pivotal(dot)
>>>>>>> io> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org>
>>>>>>>>>>>> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <
>>>>>>>>>>>>> akshay(dot)joshi(at)enterprisedb(dot)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(at)pivotal(dot)io> wrote:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <
>>>>>>>>>>>>>>> akshay(dot)joshi(at)enterprisedb(dot)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 <+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
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> 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 <+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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> 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>*
>>>
>>
>
>
> --
> *Akshay Joshi*
> *Principal Software Engineer *
>
>
>
> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>

--
*Akshay Joshi*
*Principal Software Engineer *

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