Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dI9v8-00087Y-T4 for pgadmin-hackers@arkaria.postgresql.org; Tue, 06 Jun 2017 08:32:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dI9v8-0007Dq-AM for pgadmin-hackers@arkaria.postgresql.org; Tue, 06 Jun 2017 08:32:58 +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 1dI9v7-0007Dc-F2 for pgadmin-hackers@postgresql.org; Tue, 06 Jun 2017 08:32:57 +0000 Received: from mail-it0-x229.google.com ([2607:f8b0:4001:c0b::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dI9uy-0004aX-63 for pgadmin-hackers@postgresql.org; Tue, 06 Jun 2017 08:32:56 +0000 Received: by mail-it0-x229.google.com with SMTP id m47so101937897iti.0 for ; Tue, 06 Jun 2017 01:32:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin-org.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=nMXBoESWAocCDi16fPU56dDMnAA+CE+SLJgTa86ajNA=; b=nbZZ+6LVv6OJIvU6G3cv2jde+8njI4Iz/YeJmfcY1QYigCXcijf8MtmM9AjbYgW7z5 AQfR3L+oKqbbUbDFxut1fx1jClrxHuQ0Hjh4ekr3H9s0uzQo/J2H+BcNfDiDnJIiPCel riC114tLuyOlTEdkG0WbfGRuO7F2rhJWpm8c0UOIcwQPIAFZDIBTCaX8ozdu6GxukvB6 4wIAdJ1nolSrsoi/H628kAtgJXvlASDxmJRnjOGzWmlPoA+R0XbEkEloYI0GTHF54es+ /5Xobu1eYYKLI0GtQiZp4PVfFPjsYyJItuxvkJPoylcm9f211ex94qZu+7CHblUHAQDd 8RDw== 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=nMXBoESWAocCDi16fPU56dDMnAA+CE+SLJgTa86ajNA=; b=id7HWOgicXeceFy/y5XM9vW8jjL0Yal1tDU7pN4lS7pwEz7IgMaOJDsAVnVHchw/9i HgxCojSq1tmA77ozojwjgY5COpZcHY69+gl8hhG6u8ixbd7Uu/ElQWUafeChf1O3wn9k OGmJI7a0fdTF4FpIm420ceEapOQa0oOkIzTd1LqQPTCjwoAsnT/Pgiu2O111FhZqsPj0 Hb9e1lnixPypWkOEpzS3UiE6czaU53DHgPpZBiRF3Hj8/SQFqRVvQWNCl1s61Gh+1cYW Y1zKEPh7Uet3oOxpU3jmNFkBdbfnQaOPHQCF+Zjj+TwsfPR0Sfftk3GAUsTNAPQ40f42 HP2A== X-Gm-Message-State: AODbwcC2ZwZ5FQhIvPngLWZYxCrXTvshADQe29LR5LmZQ2r5KNnG5HSV Sd9eAqUVfijzMOj57EaLhxsQaJGhVEN+ X-Received: by 10.36.31.74 with SMTP id d71mr15529582itd.85.1496737965900; Tue, 06 Jun 2017 01:32:45 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.147 with HTTP; Tue, 6 Jun 2017 01:32:44 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Tue, 6 Jun 2017 09:32:44 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Robert Eckhardt Cc: Shirley Wang , Akshay Joshi , pgadmin-hackers Content-Type: multipart/alternative; boundary="001a1145ecc6c79c7d05514676c5" 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 --001a1145ecc6c79c7d05514676c5 Content-Type: text/plain; charset="UTF-8" On Mon, Jun 5, 2017 at 5: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. > Yes, that does need more thought. > > 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. > > 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 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 >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --001a1145ecc6c79c7d05514676c5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, Jun 5, 2017 at 5:17 PM, Robert Eckhardt <<= a href=3D"mailto:reckhardt@pivotal.io" target=3D"_blank">reckhardt@pivotal.= io> wrote:
On = Mon, Jun 5, 2017 at 11:45 AM, Dave Page <dpage@pgadmin.org> = wrote:


T= he former is what I was bleating about when I said we needed to expose part= itions to the user. The latter isn't relevant - declarative partitionin= g in Postgres doesn't use inheritance.

The former is certainly the most interesting= .=C2=A0 We do need to expose the partitions but only exposing them individu= ally might be a bit overwhelming. What we found was that the number of part= itions users have, (given existing means of leveraging partitions) vary fro= m ~100 up to 10k. Basically what we were thinking about was how we can crea= te 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-i= sh) way for users to identify the one or more partitions that need to be mo= dified.=C2=A0

Yes, = that does need more thought.
=C2=A0

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

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

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

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 partition= s 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 ac= t on them all at once.=C2=A0

<= /div>
Right.
=C2=A0

=
-- Rob




=C2=A0
=

So... it sounds like we'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 workfl= ow that might be addressed outside of the create table with partition workf= low 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.joshi@enterprisedb.com>= ; wrote:
Hi All= =C2=A0

Following are the further implementation updates = to support Declarative Partitioning:
  • Show all the existin= g partitions of the parent table in Partitions tab (Refer Existing_Partitio= ns.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) =C2=A0=C2=A0
That's looking good= to me :-)

=C2=A0


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&= gt; wrote:

=C2=A0 =C2=A0Taking average of two columns is just an example/represe= ntation of expression, there is no use case of that. As I am also in learni= ng 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_logg=
ed_on timestamptz not null
    )PA=
RTITION BY RAN=
GE ( lower( left( username,<=
/span> 1 ) )=
 );
    CREATE TABLE us=
ers_0
        part=
ition of users=
 (id, primary key<=
/span> (id),=
 unique (username))
        for values=
 from <=
span style=3D"color:rgb(102,204,102)">('a') to ('g&=
#39;);
    CREATE TABLE users_1
        partition of users (id, primary key (id=
), u=
nique (username))
        for values from ('g') to (unbounded);
  • =C2= =A0Partition based on country's sale for each month of an year.
CREATE TABLE publi= c.sales
(
=C2=A0 =C2=A0 country text NOT NULL,
=C2=A0 =C2=A0 sales= bigint NOT NULL,
=C2= =A0 =C2=A0 saledate date
) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)), (extract(MONTH FROM saledate)))
=

CREATE TABLE publi= c.sale_usa_2017_jan PARTITION OF sales
=
=C2=A0 = =C2=A0 FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 0= 2);
CREATE TABLE public.sale_i= ndia_2017_jan PARTI= TION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('india', 2017, 01) = TO ('india', 2017, 02);
CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM (&#= 39;uk', 2017, 01) TO ('uk', 2017, 02);

INSERT INTO s= ales VALUES ('india', 10000, '2017-1-15');
INSERT INTO s= ales VALUES ('uk', 20000, '2017-1-08');
=
INSERT INTO sale= s VALUES ('usa', 30000, '2017-1-10');

<= /div>
=C2=A0 =C2=A0Apart from above there ma= y be N number of use cases that depends on specific requirement of user.=C2= =A0

Thank you for the exa= mple, you are absolutely correct and we were confused.=C2=A0

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

I&#= 39;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.=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




--
Dave Page
Blog: = http://pgsnake.bl= ogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com<= br>The Enterprise PostgreSQL Company
--001a1145ecc6c79c7d05514676c5--