Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dICEJ-0007sS-L9 for pgadmin-hackers@arkaria.postgresql.org; Tue, 06 Jun 2017 11:00:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dICEJ-0007zY-16 for pgadmin-hackers@arkaria.postgresql.org; Tue, 06 Jun 2017 11:00:55 +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 1dICEI-0007zP-Cn for pgadmin-hackers@postgresql.org; Tue, 06 Jun 2017 11:00:54 +0000 Received: from mail-vk0-x233.google.com ([2607:f8b0:400c:c05::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dICEA-0007UC-VU for pgadmin-hackers@postgresql.org; Tue, 06 Jun 2017 11:00:53 +0000 Received: by mail-vk0-x233.google.com with SMTP id p85so79643828vkd.3 for ; Tue, 06 Jun 2017 04:00:44 -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=Zyv4N5EfvUJM8jl2ksX3VHYgwIL9TQcyhLeSXeyeWFg=; b=OpsI6PZGrTzX6xQPW+/LG9hYtqJl+Oehh4DBHuDRaf/oj/R+GoP/CcuTXqB0KldeXH zsGUBUjKkiycrZGyheHE6spjXsUBYzQPvVCtHyTqakzRT8qQohz5pcqcWYuR2AgytxSa IqX70I2v/rJJAgfgLegMfExyCjDB9VRT/xFjvc8YxpmFVs4NnNGsEZ1lQLiTh6Isezd/ P6yg2siP+fNY1tlYwo8iIQBTFdSNzsRFT+PZyD6DXsmSJzqTh3MUeAq6u5DGZvRG3bxf t+Y3ARdALoai1hOv0DGlspgnrvrkRf9sgxJWzuiTi/UN7EctfQtkdZe7a6h7DuGFrhg6 ReUA== 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=Zyv4N5EfvUJM8jl2ksX3VHYgwIL9TQcyhLeSXeyeWFg=; b=mY6TJPzPaj+5HIwoSfHvhvrzixMdFooQZBjWfKP0qCcS+tEz1wc8Y6G/HsGv4eWeU1 2ygnpQ0PFnYWwF1+unuq4jZT0yWYXFG1VHzmRqdnDHOdK42NnXn3cPzDmnZuYRIUxHSz nmHtZl8lf2pCnedd6VoNFcc7K0WogU9eqzrnMu2nbxqwlngeCxMMqfxEbo/C1rC/4XcT E5CAeUh1ZTAae+On3tijKsxQalIZpvNBqV7Di9xejb9zk8z0IhowUTB4zjppbrAv3eUQ OHCarVbzwscb6VfJThEFOJ/+6jc2Aqq5O9uWpVAyJmpyif3Edw0QoCaS3t1RubqDzz2K nquQ== X-Gm-Message-State: AODbwcCqeUZmN45qfr8PgHsGURryMGHevxJlXjlvMByLyQl/Nzt05s3U GepT3QqkImAPQUecxLdb2tYTWGiZybvh X-Received: by 10.31.79.65 with SMTP id d62mr12513263vkb.54.1496746843343; Tue, 06 Jun 2017 04:00:43 -0700 (PDT) MIME-Version: 1.0 Received: by 10.176.4.82 with HTTP; Tue, 6 Jun 2017 04:00:42 -0700 (PDT) In-Reply-To: References: From: Robert Eckhardt Date: Tue, 6 Jun 2017 07:00:42 -0400 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: Shirley Wang , Akshay Joshi , pgadmin-hackers Content-Type: multipart/alternative; boundary="001a114dfc36eaa1c70551488785" X-Pg-Spam-Score: -2.6 (--) 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 --001a114dfc36eaa1c70551488785 Content-Type: text/plain; charset="UTF-8" On Tue, Jun 6, 2017 at 4:32 AM, Dave Page 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 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 <+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 > --001a114dfc36eaa1c70551488785 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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

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

<= div>You're thinking Greenplum :-). There is no roll up in PostgreSQL, u= nless 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 s= pecific and consistent pattern described by users leveraging time based ran= ge partitions in Postgres. I'm not sure if that same use case will be s= upported with partitioning as implemented in Postgres 10 but it is a Postgr= es pattern.=C2=A0

-- Rob
=C2=A0
=C2=A0

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

Right.
=C2=A0

-- Rob




=C2=A0

So... it sound= s like we're on the right lines :-)
=C2=A0
<= blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px= #ccc solid;padding-left:1ex">

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.=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)
  • Abi= lity to create N partitions and detach existing partitions. Refer (Create_D= etach_Partition.png), in this example I have det= ach two existing partition and create two new partitions.
  • Added &qu= ot;Detach Partition" menu to partitions node only and user will be abl= e to detach from there as well. Refer (Detach.png) =C2=A0=C2=A0
That&#= 39;s looking good to me :-)

=C2=A0

<= div class=3D"m_8776742507335498506m_8850063569488123921m_-26094971842657630= 28m_-8370408843946770147m_-2795212686585790560m_6013502630113650474h5">
=
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= :

=C2=A0 =C2= =A0Taking average of two columns is just an example/representation of expre= ssion, 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 base=
d on first letter of their username
    CREAT=
E 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'<=
span style=3D"color:rgb(102,204,102)">) to =
(unbounded);
  • =C2=A0Partition based on country's sale for ea= ch month of an year.
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 country text NOT = NULL,
=C2=A0 =C2=A0 sales bigint NOT NULL,
=C2=A0 =C2=A0 saledate date
) PAR= TITION BY RANGE (country, (extract (YEAR FROM saledate)), (extract(MONTH FROM saledate)))

CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
<= /div>
=C2=A0 = =C2=A0 FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 0= 2);
CREATE TABLE public.sale_india_2017_jan PARTITION OF sales
=
=C2=A0 =C2=A0 FO= R 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 ('uk', 2017, 01) TO ('uk', 2017, 02);

INSE= RT INTO sales VALUES ('india', 10000, '2017-1-15');
INSE= RT INTO sales VALUES ('uk', 20000, '2017-1-08');
INSERT = INTO sales VALUES ('usa', 30000, '2017-1-10');
=C2=A0 =C2=A0Apart from above= there may be N number of use cases that depends on specific requirement of= user.=C2=A0

Thank you fo= r the example, you are absolutely correct and we were confused.=C2=A0
=

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

I'm not sure where you are with respect to implementing the UI bu= t I'd love to float some ideas and mock ups past you.=C2=A0
<= br>
-- Rob



-= -
Ak= shay Joshi
Principal Software Engineer=C2=A0
=

<= /font>

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

=



--
Dave Page

Blog: http://pgsnake.blogs= pot.com
Twitter: @pgsnake

=
Ent= erpriseDB UK: htt= p://www.enterprisedb.com
The Enterprise PostgreSQL Company



--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pg= snake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL C= ompany




--
Dave Page
Blog: http://pgsnake.blogspot.com
Twi= tter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Pos= tgreSQL Company

--001a114dfc36eaa1c70551488785--