Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dL74e-00055N-0h for pgadmin-hackers@arkaria.postgresql.org; Wed, 14 Jun 2017 12:07:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dL74d-0003cV-Hg for pgadmin-hackers@arkaria.postgresql.org; Wed, 14 Jun 2017 12:06:59 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dL74L-0003CV-8k for pgadmin-hackers@postgresql.org; Wed, 14 Jun 2017 12:06:41 +0000 Received: from mail-io0-x22c.google.com ([2607:f8b0:4001:c06::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dL74I-0003k0-52 for pgadmin-hackers@postgresql.org; Wed, 14 Jun 2017 12:06:40 +0000 Received: by mail-io0-x22c.google.com with SMTP id y77so91025892ioe.3 for ; Wed, 14 Jun 2017 05:06:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=zvTu2TLpyK3/oKrE7JVT4WCUpgRTdt9u3QfPArV6Cpg=; b=cfg3waniWnwFif2jnMnuzoTKtXiMo75CBr/C/m2hSaiXgF9/scfznwlmlEU6sVNqup q21wQu6ugkb/JNUHZ3bHvtyDfbaBcSiAWj4RsrY7/ZlTzVAGLTjpuQ/NbT3VJapQxvrR NnEBZXoOzx6ykuq4jtXvSDGfZ6nGZzjgner2qYXMxbmWfzaZCATLU41r+qcVn+mILfih ROv6kA3aBccm1hnprQsCEX+YEk43QsKKfMDq26YzR8OtJIvEE0U2yLZH+Wk9kF9S5VCl apYLicAgyOgCiieUBx93tzLzRMKEcRpsKYKh5DIelEcfIsusqiea2zVHDsPMkAFsIaKP 5thQ== 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=zvTu2TLpyK3/oKrE7JVT4WCUpgRTdt9u3QfPArV6Cpg=; b=JYTO6BzA3Dufr0XWVHchl8T88u9mXqLFLIIQZAhFk2LElQMts7e+CEIqVITZpeM5I3 RJQNfS1a5ll0bJ8vNMDG8gDd5YbkpFBTTkNeLK0u7IPFcbzGz0VJVch60HYDWZKE8xB3 iMWv+RioL7DjQIBDgl6nZLWzemUubCQVKvGs13uqHgvxNEqvT7TmMOYJlY1wryaPcTsK c5Lxu7YVajtHMTApCtmVP9mxALbzMq/dvtLL6n+J2AraSrWxkG9Ji8/4XLgVPocReBDz VUX9+bhxTwoe01A8QnKsVqgH22bls9dY16z6rOtgRJdz4yFTqjjWJqQb1fnth9BJFPVM H+4g== X-Gm-Message-State: AKS2vOwQPH6yHyl3JkKGQ7WPmxj8op1PDGQvUjbaoQieTRSzuNAOmX3r Uf5DqEvmYvbIWHtxcTeO98hEAt3h9Eb9254= X-Received: by 10.107.19.17 with SMTP id b17mr272931ioj.51.1497441997111; Wed, 14 Jun 2017 05:06:37 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.133.67 with HTTP; Wed, 14 Jun 2017 05:06:16 -0700 (PDT) In-Reply-To: References: From: Ashesh Vashi Date: Wed, 14 Jun 2017 17:36:16 +0530 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: Akshay Joshi , pgadmin-hackers , Shirley Wang , Robert Eckhardt Content-Type: multipart/alternative; boundary="001a113f72c84f63440551ea62c7" 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 --001a113f72c84f63440551ea62c7 Content-Type: text/plain; charset="UTF-8" On Wed, Jun 14, 2017 at 1:59 PM, Dave Page wrote: > > > On Tue, Jun 13, 2017 at 2:59 PM, Akshay Joshi < > akshay.joshi@enterprisedb.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? -- 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 >> wrote: >> >>> >>> >>> 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 >>>> >>> >>> >> >> >> -- >> *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 > --001a113f72c84f63440551ea62c7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On W= ed, Jun 14, 2017 at 1:59 PM, Dave Page <dpage@pgadmin.org> w= rote:


On Tue, Jun 13= , 2017 at 2:59 PM, Akshay Joshi <akshay.joshi@enterprisedb.com= > wrote:
Hi All=C2=A0

For further impl= ementation following task needs to be work upon:
  • How to p= arse and show partitions keys. For example user has created below partition= ed table=C2=A0
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 country character varying COLLATE pg_catalog."default= " NOT NULL,
=C2=A0 =C2=A0 sales bigint,
=C2=A0 =C2=A0 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(d= isplayed in bold in above example) and show them in our control that we use= d. For the time being I have hide that control in 'Edit' mode (Refe= r Attach Partition.png)<= /blockquote>

I assume psql wit= h display that info with \dt or similar? What does it do? Failing that, loo= k at pg_dump?
psql, and pg_dump us= e '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 partit= ion key is? (column/expression).

Let me give an example.
I have a partit= ion table with the following definition (with two partition keys).

C= REATE TABLE public.sales
(
=C2=A0 =C2=A0 country character varying CO= LLATE pg_catalog."default" NOT NULL,
=C2=A0 =C2=A0 sales bigin= t,
=C2=A0 =C2=A0 saledate date
) PARTITION BY RANGE (country, EXTRACT= (year from saledate));

And, the followi= ng query will give as:=C2=A0
SELECT relname, pg_get_partkeydef(oid) FROM pg_catalog= .pg_class WHERE relname like 'sal%';

=C2=A0relname | =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_get_partkeydef<= /div>
---------+-= ---------------------------------------------------
=C2=A0sales =C2=A0 | RAN= GE (country, date_part('year'::text, saledate))

Here - we have two option in edit mode.
1.= Parse the output of the =C2=A0'pg_get_partkeydef', and identify all indiv= idual keys, and its detailed information (i.e. column/expression)
2. Show that output about the partition keys in static control, and hide t= he Partition type, partition keys controls.

I pref= er 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 out= put.

What do you say?

-- = Thanks, Ashesh
=C2=A0<= /div>
  • Support of sub partitioning:=C2=A0To implement sub-partiti= oning, specify the PARTITION BY clause in the commands used to create indiv= idual partitions, for example:
  • CREATE TABLE measurement_y2006 PARTITION OF measurem=
ent
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktem=
p);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0To achie= ve above I have made some changes in GUI (Refer Sub Partition.png).=C2=A0
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Complex and challenging part here is "me= asurement_y2006" is partition of "measurement" and parent ta= ble for other partitions too which user can create later. How we will going= to show this in browser tree?=C2=A0
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0One option could be
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Tables
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0->measurement(table)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0->Partitions
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->measurement_y2006(Partition of m= easurement and parent of p1)=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->Partitions
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->p= 1=C2=A0

Urgh. But yeah. = I think that makes logical sense.
=C2=A0
  • Attach Partiti= ons: To implement attach N partitions I have made some changes in GUI( = Refer=C2=A0Attach 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. =C2=A0

I don't think I'd include Attach on the dialog. I think it s= hould be a separate menu option, with a simple dialogue to let the user cho= ose the table to attach.

The reason for that is th= at Attach is an action not a property. On the Properties panels we expect a= ny 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 ne= xt 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 at= tach, hit OK, and when you next open the Properties dialogue, those partiti= ons will be listed in the partition list, not the Attach list.
=C2=A0
Please review the design. Suggestions/Comments are welcome.
=
=C2=A0=C2=A0
<= br>
On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt <= ;reckhardt@pivotal.io> wrote:


On Tue, Jun 6, 2017 at 4:32 AM, = Dave Page <dpage@pgadmin.org> wrote:
<= div class=3D"gmail_quote">

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&#= 39;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 spe= cific and consistent pattern described by users leveraging time based range= partitions in Postgres. I'm not sure if that same use case will be sup= ported with partitioning as implemented in Postgres 10 but it is a Postgres= pattern.=C2=A0

-- Rob
<= div>=C2=A0
= =C2=A0

For other patterns such as creating indexes and such it requires a bit m= ore thought. Generally users described wanting to treat all of the children= like a single table (just like Oracle), however, other users described pot= entially modifying chunks of partitions differently depending on some crite= rion. This means that users will need to identify the subset they want to o= ptimize and then ideally be able to act on them all at once.=C2=A0

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 workf= low that might be addressed outside of the create table with partition work= flow we're working on currently.=C2=A0





On Mon, Jun 5, 2017 at 5:21 AM Dave Pa= ge <dpage@pgadmin= .org> wrote:
= On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <akshay.joshi@en= terprisedb.com> wrote:
Hi All=C2=A0

Follow= ing are the further implementation updates to support Declarative Partition= ing:
  • Show all the existing partitions of the parent table= in Partitions tab (Refer Existing_Partitions.png)
  • Ability to create N partitions and detach exi= sting partitions. Refer (Create_Detach_Partition.png), in this example I have detach two existing partit= ion and create two new partitions.
  • Added "Detach Partition&quo= t; menu to partitions node only and user will be able to detach from there = as well. Refer (Detach.png) = =C2=A0=C2=A0
That= 9;s looking good to me :-)

=C2=A0


On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt = <reckhardt@piv= otal.io> wrote:


On Wed, May 24, 2017 = at 3:35 AM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
<= div dir=3D"ltr">

=C2=A0 =C2=A0Taking average of two columns is j= ust 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     ti=
mestamptz not<=
/span> null,
        last_logged_on timestamptz not null
    )=
PARTITION BY R=
ANGE ( lower( left=
( username, 1 ) ) );
    CREATE TABLE users_0
        partition of users (id, primary key (id), unique (username))
        for values from<=
/span> ('a') to ('g');
    CREATE TABLE users_1
        partition of users (=
id, primary key (<=
/span>id), unique (=
username))
        f=
or values from ('g') to (unbounded);
  • =C2=A0Partition based on count= ry's sale for each month of an year.
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 country text NOT NULL,
=
=C2=A0 =C2=A0 sa= les bigint NOT NULL,
=C2=A0 =C2=A0 saledate date
) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)), (extract(MONTH FROM saledate)))
=
<= div>
CREATE TABLE public.sale_usa_2017_j= an PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('u= sa', 2017, 01) TO ('usa', 2017, 02);
<= div class=3D"gmail_extra">
CREATE TABLE publ= ic.sale_india_2017_jan PARTITION OF sales
=
<= div>=C2=A0 =C2=A0 FOR VALUES FROM ('india', 2017, 01) TO ('indi= a', 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);

<= /div>
INSERT INTO sales VALUES ('india', 10000, '2017-1-15');<= /div>
INSERT INTO sales VALUES ('uk', 20000, '2017-1-08'= );
INSERT INTO sales VALUES ('usa', 30000, '2017-1-10&= #39;);

=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


<= b>Phone: +91 20-3058-9517
Mobile: <= a href=3D"tel:+91%2097678%2088246" value=3D"+919767888246" target=3D"_blank= ">+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




--
Dave Page

Blog: http://pgsnake.blogspot.comTwitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company
<= br>

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQ= L Company



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

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




--
Akshay Joshi
Principa= l Software Engineer=C2=A0

<= /span>




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

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

--001a113f72c84f63440551ea62c7--