Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dHoCh-0004ah-VM for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 09:21:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dHoCh-0007qc-He for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 09:21:39 +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 1dHoCS-0007RT-Mv for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 09:21:24 +0000 Received: from mail-it0-x22e.google.com ([2607:f8b0:4001:c0b::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dHoCK-0001sE-5P for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 09:21:24 +0000 Received: by mail-it0-x22e.google.com with SMTP id m47so14585545iti.1 for ; Mon, 05 Jun 2017 02:21:15 -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=OpidHu9a6FH+QFBbm2XlJs0SskVgfzfH8y2n5V+HRNA=; b=MezGXva0VUg7VSHjOuoztzRT9WFDfaxxbtq1iz8aRnBafzCw0g8TIfTC0che/xauaP kzB+gPdj2Dz2C6V8Rf9vWRyM8OgrenmVzsOujdJLOzt7l6c8Gxa8UCevVmHlWmzjHHUB 9ggSFC2m3w4TwjHIlp0b1+EuW6VOEfBiTYmVNDBt9wlzaMB1g0uZgi0sFOyeQhWsuJiA 8EQ3j3USsWWCcrEe9fLYxDjOb4bRkLGAlkLiyW32PQj0EerQZIJLmlwcjimImhe4aEjD UVrKPUlJkmtabzWgFqfmnHq1/hHKYtn0cSm9w8t7IAMLiS36LhzGx3+lFYACAWEMj2bV n+xg== 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=OpidHu9a6FH+QFBbm2XlJs0SskVgfzfH8y2n5V+HRNA=; b=Pvc7RUILtJjrA80kmlaW5ECYTG585Pu427mGigLJSkQpitt2m3FcdanrikcTh1zlBm VCkYPh17SnHUSZvtt4XVGUmyOdThzC/ZcCR3P0xk1/fvGE/SRzo3nsGLRXAKLquGK0rG 11l77Usfdzzd7WrpiT3DVaPf596ygl0jU2BWO2d5+m2PTBXLnH03Bs3LsgHgsmr10Yvn zO12slx+m1UD+J45onDLgzP0fgI7dbhsCu/tnGEUeEKJLF5lTiihJIYF2ITvJAfOOBbs sgYU8A/wkJ5wtqPQXrSwxUdn97Jwfi1adNalapr6BfYJ1ukGzYfBNN3toXfV9QRzTxhc 56sw== X-Gm-Message-State: AODbwcDDQ1QDukXDKhs4XVTlruLBu7r+xdpEKJ7vBnHn51XybIYhWBkx lPzOyWCw3NXnpHv1lDwflfH7ElaR7mfy X-Received: by 10.36.74.3 with SMTP id k3mr11702126itb.28.1496654474060; Mon, 05 Jun 2017 02:21:14 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.147 with HTTP; Mon, 5 Jun 2017 02:21:13 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Mon, 5 Jun 2017 10:21:13 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: pgadmin-hackers , Shirley Wang , Robert Eckhardt Content-Type: multipart/alternative; boundary="001a1144939647388d05513306a7" 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 --001a1144939647388d05513306a7 Content-Type: text/plain; charset="UTF-8" On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi 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 --001a1144939647388d05513306a7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <akshay.joshi= @enterprisedb.com> wrote:
<= div dir=3D"ltr">Hi All=C2=A0

Following are the further i= mplementation updates to support Declarative Partitioning:
    Show all the existing partitions of the parent table in Partitions tab (R= efer Existing_Partitions.png)
  • Ability to create N partitions and detach existing partitions. R= efer (Create_Detach_Partition.png<= /span>), in this example I have detach two existing partition and create tw= o new partitions.
  • Added "Detach Partition" menu to partit= ions node only and user will be able to detach from there as well. Refer (D= etach.png) =C2=A0=C2=A0
That's looking good to me :-)
=

=C2=A0


On Wed, May 24, 2017 at 8:00 PM, Robert Eck= hardt <reckhardt@pivotal.io> wrote:


On Wed, May 2= 4, 2017 at 3:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.co= m> wrote:

<= /div>
=C2=A0 =C2=A0Taking average of two columns is just an exam= ple/representation of expression, there is no use case of that. As I am als= o in learning phase. Below are some use case that I can think of:
  • Partitions based on first letter of their =
username
    CREATE TABLE users <=
span style=3D"color:rgb(102,204,102)">(
        id             serial not null,    user=
name       text not null,
        password       text,
        created_on     timestamptz not null,
    =
        last_logged_o=
n timestamptz not nu=
ll
    )PARTITION BY RANGE ( lower( left( username, 1 ) ) );
    CREATE TABLE users_0
      =
  partition of=
 users (id, primary key (id)=
, unique (username))
        =
for values from ('a'<=
span style=3D"color:rgb(102,204,102)">) to =
('g');
    CREATE<=
/span> TABLE u=
sers_1
        partition =
of users (id, prim=
ary key=
 (id), unique (username))    for values from ('g') to (un=
bounded);
=
  • =C2=A0Partition based on country's sale for each month o= f an year.
    • = CREATE TABLE public.sales
    <= div class=3D"gmail_quote">
    (
    =C2=A0 =C2=A0 country text NOT NULL,
    =C2=A0 =C2=A0 sales bigint NOT NULL,
    =C2=A0 =C2=A0 saledate date
    <= div class=3D"gmail_quote">
    ) PARTITION BY RANGE (country, (extract (YEA= R FROM saledate)), (extract(MONTH FROM saledate)))

    CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
    =C2=A0 =C2=A0 FOR VALUES FROM ('usa= ', 2017, 01) TO ('usa', 2017, 02);
    CREATE TABLE public.sale_= india_2017_jan PARTITION OF sales
    =C2=A0= =C2=A0 FOR VALUES FROM ('india', 2017, 01) TO ('india', 20= 17, 02);
    CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales
    =C2=A0 =C2=A0 FOR VALUES FROM ('uk', 20= 17, 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 (= 9;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 for the example, you are= absolutely correct and we were confused.=C2=A0

    Gi= ven our new found understanding do you mind if we iterate a bit on the UI/U= X?=C2=A0 What we were suggesting with the daily/monthly/yearly drop down wa= s a specific example of an expression. Given that fact that doesn't see= m to be required in an MVP, however, I do think a more interactive experien= ce between the definition of the child partitions and the creation of the p= artitions would be optimal.=C2=A0

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

    -- Rob



    --
    = Akshay Joshi
    Principal Software Engine= er=C2=A0

    =

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

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




    --
    --001a1144939647388d05513306a7--