Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dHujN-0002xD-KN for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 16:19:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dHujN-00048V-6w for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 16:19:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dHuj7-0003Gh-Ex for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 16:19:33 +0000 Received: from mail-ua0-x22d.google.com ([2607:f8b0:400c:c08::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dHuj1-0002f1-Vy for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 16:19:32 +0000 Received: by mail-ua0-x22d.google.com with SMTP id y4so78615892uay.2 for ; Mon, 05 Jun 2017 09:19:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=wX0LpUSHRzd7HvuJ/77KdoSVFaGOTAJqCJheSLEYo4g=; b=ZIfoGlBLa+de6m/adAEuw0YJqFHUy3kPpHPcZzAHV0YOBZjf6fHi2YBho7Pwvd8ATc wi4nePQ/jy54KenhXFJsQpGFavPAdbpNC2n+YKaahv/Mvg5TaVFQmDM2bHnfohhfG9wO xBnS3XcKO9Sgav/6pbn6cQxYQeV6fkf05SNajiyIIc6JxiJ+mJYNp4NT6vW1yJ+gIGu6 WI1idCvWRHvT39Z23VrTufZNdOXoaTMSdYjL6qm+MGcJgnBNpViR0Gon8NASjBzrdDnJ 33l48sSzqgu8zixq1kvdQwL7EzQOxwzvUu4DhW6bnQU1GOS5wmLTUa/m552qKzE5wPDr rDsg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=wX0LpUSHRzd7HvuJ/77KdoSVFaGOTAJqCJheSLEYo4g=; b=T6LJt7zcKc1fD2PrBvtNXQDhhygaSryysJOe1nu08j3keqWXidGvp4aqzy3UeqtA3q /PothaFCQU0XyTRQPV3pxTwgPeaZb5X/DyimZLli5UoP/hFGKqztL9/q43r7+t+85lKt PV1WTQ5x7S9zvAcHuXW2UrQDezw1Sz4/ZLK365uceEoStIVe2MlTrew16LpU1gLf/t3o WgclWp4+ihTvK57KQcWHjpsIF/17zbO7PMc9cpdTsnNs42A0ABu1apnk94iYm0VgMe2g UL3SR3Tpt6H0KpCw/GKfaQ3NxfOGGsTAYBOk8qVHfJ3Us+asy/ey1UD38S3N6frlFgN2 5cow== X-Gm-Message-State: AODbwcApT9oWksqN+bdQEL9eb2o9ianmEJki90/Qi7DsU/oXIgEa38Wm DVECHMeDqth0IV9h8dOrbRmT2Bgualbt X-Received: by 10.176.84.221 with SMTP id q29mr11673058uaa.103.1496679565351; Mon, 05 Jun 2017 09:19:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.176.4.82 with HTTP; Mon, 5 Jun 2017 09:19:24 -0700 (PDT) In-Reply-To: References: From: Robert Eckhardt Date: Mon, 5 Jun 2017 12:19:24 -0400 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: Shirley Wang , Akshay Joshi , pgadmin-hackers Content-Type: multipart/alternative; boundary="94eb2c1b10ced653a1055138ddd1" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgadmin-hackers Precedence: bulk Sender: pgadmin-hackers-owner@postgresql.org --94eb2c1b10ced653a1055138ddd1 Content-Type: text/plain; charset="UTF-8" I guess what I didn't say is YES. What Akshay and Ashesh are building is going to absolutely be fundamental to any workflows being defined in these interviews. -- Rob On Mon, Jun 5, 2017 at 12:17 PM, Robert Eckhardt wrote: > On Mon, Jun 5, 2017 at 11:45 AM, Dave Page wrote: > >> >> >> The former is what I was bleating about when I said we needed to expose >> partitions to the user. The latter isn't relevant - declarative >> partitioning in Postgres doesn't use inheritance. >> > > The former is certainly the most interesting. We do need to expose the > partitions but only exposing them individually might be a bit overwhelming. > What we found was that the number of partitions users have, (given existing > means of leveraging partitions) vary from ~100 up to 10k. Basically what we > were thinking about was how we can create a workflow/interface that allows > users to modify one or more children at once. Furthermore, it would be nice > if we could figure out an easy (easy-ish) way for users to identify the one > or more partitions that need to be modified. > > For roll up this pattern seems obvious, identify the n partitions you > need/want to combine and then run a job to combine them. > > 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. > > -- 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 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 >>>> > 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 <+91%2020%203058%209517>Mobile: +91 >>>>> 976-788-8246 <+91%2097678%2088246>* >>>>> >>>>> >>>>> -- >>>>> 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 >> > > --94eb2c1b10ced653a1055138ddd1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I guess what I didn't say is YES. What Akshay and Ashe= sh are building is going to absolutely be fundamental to any workflows bein= g defined in these interviews.=C2=A0

-- Rob
<= div class=3D"gmail_extra">
On Mon, Jun 5, 201= 7 at 12:17 PM, Robert Eckhardt <reckhardt@pivotal.io> wro= te:
On Mon, Jun 5, 2017 at 1= 1:45 AM, Dave Page <dpage@pgadmin.org> wrote:


The former is what I w= as bleating about when I said we needed to expose partitions to the user. T= he latter isn't relevant - declarative partitioning in Postgres doesn&#= 39;t use inheritance.

The former is certainly the most interesting.=C2=A0 We do need to= expose the partitions but only exposing them individually might be a bit o= verwhelming. What we found was that the number of partitions users have, (g= iven existing means of leveraging partitions) vary from ~100 up to 10k. Bas= ically what we were thinking about was how we can create a workflow/interfa= ce that allows users to modify one or more children at once. Furthermore, i= t would be nice if we could figure out an easy (easy-ish) way for users to = identify the one or more partitions that need to be modified.=C2=A0

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

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

-- Rob
=




=C2=A0

So... it sounds like we= 9;re on the right lines :-)
=C2=A0

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 crea= te table with partition workflow we're working on currently.=C2=A0





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.jos= hi@enterprisedb.com> wrote:
Hi All=C2=A0

Following are the fu= rther 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 part= itions 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 partitio= ns node only and user will be able to detach from there as well. Refer (Det= ach.png) =C2=A0=C2=A0
<= /div>
That's looking good to me :-)

=C2=A0


On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt <rec= khardt@pivotal.io> wrote:
<= div dir=3D"ltr">

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

=C2=A0 =C2=A0Taking average of two colum= ns 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 t= hink of:
  • =
Partitions based on first letter of their username
    CREATE TABLE users (
        id =
            serial no=
t null<=
span style=3D"color:rgb(102,204,102)">,
        user=
name       text not null,
        password       text,
        created_on     timestampt=
z not <=
span style=3D"color:rgb(153,51,51);font-weight:bold">null,
        last_logged_on timestamptz n=
ot null
    )PARTITION BY RANGE ( lower( left(<=
/span> 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
    <=
font face=3D"arial, helvetica, sans-serif">    partition of users (id, <=
span style=3D"color:rgb(153,51,51);font-weight:bold">primary key (id), unique (username<=
span style=3D"color:rgb(102,204,102)">))
      =
  for values from ('g') to (unbounded);<=
/blockquote>
  • =C2=A0Partition based on country's sale for = each month of an year.
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 country text NO= T NULL,
=C2=A0 =C2=A0 sales bi= gint NOT NULL,
=C2=A0 =C2=A0 saledate date
<= div class=3D"gmail_quote">
) PARTITION BY RANGE (country, (extract (YEA= R FROM saledate)), (extrac= t(MONTH FROM saledate)))

CREATE TABLE public.sale_usa_2017_jan PART= ITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('usa', 2017, 01) T= O ('usa', 2017, 02);
=
CREATE TABLE public.sale_india_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM (&#= 39;india', 2017, 01) TO ('india', 2017, 02);
<= /blockquote>
CREATE TABLE pu= blic.sale_uk_2017_jan PARTITION OF sales
=
=C2=A0 = =C2=A0 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&= #39;);
INSERT INTO sales VALUES ('usa', 30000, '2017-1-10= 9;);

=C2=A0 =C2= =A0Apart from above there may be N number of use cases that depends on spec= ific requirement of user.=C2=A0

Thank you for the example, you are absolutely correct and we were co= nfused.=C2=A0

Given our new found understanding do= you mind if we iterate a bit on the UI/UX?=C2=A0 What we were suggesting w= ith the daily/monthly/yearly drop down was a specific example of an express= ion. Given that fact that doesn't seem to be required in an MVP, howeve= r, I do think a more interactive experience between the definition of the c= hild partitions and the creation of the partitions would be optimal.=C2=A0<= /div>

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

-- Rob



-= -
Akshay Joshi=
Pri= ncipal Software Engineer=C2=A0

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-ha= ckers

=



--
D= ave Page

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

<= /div>
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Co= mpany



-- <= br>
Dave Page
Blo= g: http://pgsnake= .blogspot.com
Twitter: @pgsnake

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


--94eb2c1b10ced653a1055138ddd1--