Re: Declarative partitioning in pgAdmin4

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.

>
> -- 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