Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dL77G-0005Bl-Cx for pgadmin-hackers@arkaria.postgresql.org; Wed, 14 Jun 2017 12:09:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dL77F-00045U-Pp for pgadmin-hackers@arkaria.postgresql.org; Wed, 14 Jun 2017 12:09:41 +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 1dL76y-0003fi-FG for pgadmin-hackers@postgresql.org; Wed, 14 Jun 2017 12:09:24 +0000 Received: from mail-it0-x231.google.com ([2607:f8b0:4001:c0b::231]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dL76t-0004x4-CN for pgadmin-hackers@postgresql.org; Wed, 14 Jun 2017 12:09:23 +0000 Received: by mail-it0-x231.google.com with SMTP id m62so57701001itc.0 for ; Wed, 14 Jun 2017 05:09:18 -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=G1yLN0JOcP6Y1eTxKzB9pWCt6cwrEo4U9YTIY2Kw0g8=; b=QBKBXDf4L8kSRmsZ7LCSbDxRdJXJhA862Qi+ujze7vKT4cpCWyDpHxAUqdYC0GDMON 53t+0aP1bWR492bzn/KEAHM6UV3tKJutGlFnnWKXbcGjDj1b+Dafqsgfhoe+5u7iRLmR T6eVqn/OQ1J1lzlxXGJ2osGzCxPxHDT7WSGi1iPucCWrGitcCelNV2rjuqN2zX+0N4Dz FEpvEkZ+dHE2ZSP2kMbI8pYUO0Up5glct/Eb4K4x/NnraauG74ZAr5j3hCyQznN1AQ9f 5hPmHyiycCNlX+MK9SnC2ZASidbbABh9Zak3mFcppAz2bdk+ojgiNYZkmmCbmWkMYU+C N5bg== 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=G1yLN0JOcP6Y1eTxKzB9pWCt6cwrEo4U9YTIY2Kw0g8=; b=eFFNiNI4UYFznlLyF1pwo2p1ExHhV5ZofMaWb505shlBVRQ/N/198Cs/Mf+SrbpE7D vlwb6FsD6zuYclP20vKlHNW2/yl4uX8EIZowyUzwu3QmxlkkZf+1Onl4rx6PanjK9ImJ Dh6fbov3oDmp6JX7NUgZ93Gz5ldrgZT65pEaP2QWqUNwdBCcIxndzhHdTKjNJcIJKcCZ 4qjmecxpIGgdriopEQ+hZZKTPROF1eZZJmt12QxM+m7J1lVRC0SAG6gMASEyOwB2zu0d L8JydjmxMgFTWpR/F1+8OwpjxwYVHLXcKn7mnLZC08nXvxCUEhvn6sJDcd6gXdzEn2+D JYdA== X-Gm-Message-State: AKS2vOxx9GVWTK8qbCWRz4E6SvgY4Cr/rBPyowQjqWo5lkir27BGZqGq DYNmhA2eYS/i9djcHY7fnZn5SMftb2LA X-Received: by 10.36.98.141 with SMTP id d135mr906316itc.28.1497442157068; Wed, 14 Jun 2017 05:09:17 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.147 with HTTP; Wed, 14 Jun 2017 05:09:15 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Wed, 14 Jun 2017 13:09:15 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Ashesh Vashi Cc: Akshay Joshi , pgadmin-hackers , Shirley Wang , Robert Eckhardt Content-Type: multipart/alternative; boundary="001a1145b0e0d8252a0551ea6b25" 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 --001a1145b0e0d8252a0551ea6b25 Content-Type: text/plain; charset="UTF-8" On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi wrote: > 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? > I agree. > > -- 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 >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --001a1145b0e0d8252a0551ea6b25 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Jun 14, 2017 at 1:06 PM, Ashesh Vashi <ashesh.vash= i@enterprisedb.com> wrote:
=
On Wed, Jun 14, 2017 at 1:59 PM, Dave Page &= lt;dpage@pgadmin.org= > wrote:


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

For further implementation foll= owing task needs to be work upon:
  • How to parse and show p= artitions keys. For example user has created below partitioned table=C2=A0<= /li>
CREATE TABLE public.sales
(
=C2=A0 =C2= =A0 country character varying COLLATE pg_catalog."default" NOT NU= LL,
=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 <= span id=3D"m_6065641664552352939gmail-m_4514327467440529168m_-8588986124962= 921849gmail-m_-280870434934732704m_-2770180097690768620:3dt.30">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 h= ave hide that control in 'Edit' mode (Refer Attach Partition.png)

I assume psql with di= splay that info with \dt or similar? What does it do? Failing that, look at= pg_dump?
psql, and pg_dump= use 'pg_get_partkey= def' function for reverse engineering, and we too.
They don't need particu= lar key information.

In properties dialog, we need to find out - what individual par= tition key is? (column/expression).

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

CREATE TABLE public.sales
(
<= font face=3D"monospace, monospace" color=3D"#444444">=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
) PARTITI= ON BY RANGE (country, EXTRACT(year from saledate));
And, the following query will give as:=C2=A0
SELECT relname, pg_get_pa= rtkeydef(oid) FROM pg_catalog.pg_class WHERE relname like 'sal%';
<= br>
= =C2=A0relname | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = pg_get_partkeydef
---------+------------------------------------------= ----------
=C2=A0sales =C2=A0 | RANGE (country, date_part('year= 9;::text, saledate))

Here - we ha= ve two option in edit mode.
1. Parse the output of the =C2=A0'= ;pg_get_partkeydef= ', and identify all individual keys, and its detailed inform= ation (i.e. column/expression)
2. Show that output about the part= ition keys in static control, and hide the Partition type, partition keys c= ontrols.

I prefer the second option, as user can n= ot modify the partition keys/type (RANGE/LIST), and we will not have to wri= te logic to parse the keys from that output.

What = do you say?

I agree= .
=C2=A0

-- T= hanks, Ashesh
=C2=A0
  • Support of sub part= itioning:=C2=A0To 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);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0To achieve above I have made some changes in GUI (Refer Sub Pa= rtition.pn= g).=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Comp= lex 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?=C2= =A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0One optio= n could be
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tables
<= div>=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->Part= itions
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0->measurement_y2006(Partition of measurement 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->p1=C2=A0

Urgh. But yeah. I think that makes logical sens= e.
=C2=A0
  • Attach Partitions: T= o implement attach N partitions I have made some changes in GUI( Refer=C2= =A0Attach Partition.png). Attach Partitions control will only be visible in &q= uot;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 should be a separate menu option, with a s= imple 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 nex= t time the dialogue is opened - e.g. if you toggle "Enable Trigger&quo= t; 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= 9;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.
=C2=A0
Please rev= iew the design. Suggestions/Comments are welcome.
=C2=A0=C2=A0
=
On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt= <reck= hardt@pivotal.io> wrote:


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

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

You're thinki= ng Greenplum :-). There is no roll up in PostgreSQL, unless you're thin= king we should create such a feature in pgAdmin.

O= f course, I have no objection to extending what we do in PG to add GP featu= re support, but let's start with PG.

No not at all. That was a very specific and co= nsistent 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.=C2= =A0

-- Rob
=C2=A0
=
=C2=A0

<= /div>
For other patterns such as creating indexes and such it requires = a bit more thought. Generally users described wanting to treat all of the c= hildren like a single table (just like Oracle), however, other users descri= bed potentially modifying chunks of partitions differently depending on som= e criterion. This means that users will need to identify the subset they wa= nt to optimize and then ideally be able to act on them all at once.=C2=A0

Right.
<= div>
=C2=A0

-- Ro= b




=C2=A0

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

F= or the former, this can be addressed by enabling users to modify one or mor= e child partitions at the same time. For the latter, that is a workflow tha= t 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 J= oshi <akshay.joshi@enterprisedb.com> wrote:=
Hi A= ll=C2=A0

Following are the further implementation update= s to support Declarative Partitioning:
  • Show all the exist= ing partitions of the parent table in Partitions tab (Refer Existing_Partit= ions.png= )
  • Ability to create N partitions and detach existing partitions. Re= fer (Create_Detach_Partition.png), in this example I have detach two existing parti= tion and create two new partitions.
  • Added "Detach Partition&qu= ot; menu to partitions node only and user will be able to detach from there= as well. Refer (Detach.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 <reckhardt@pivotal.io> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">


On Wed, May 24, 2017 at 3:35 AM, A= kshay Joshi <akshay.joshi@enterprisedb.com>= wrote:

=C2=A0 =C2=A0Taking average of two columns is just an exampl= e/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:
<= ul>
  • Partitions based on first letter of their username
    CREATE TABLE=
 users (
        id             serial not null,
     =
   username       text not null,
        passwor=
d       text,
        created_on     timestamptz not nu=
ll,
        last_logged_on timestamptz not null<=
br>)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 (i=
d, primary key (id), unique (username)=
)
        for values =
from ('g') to <=
span style=3D"color:rgb(102,204,102)">(    unbounded);
  • =C2=A0Partit= ion based on country's sale for each month of an year.
    • <= /div>
    CREATE TABLE pu= blic.sales
    (
    =C2=A0 =C2=A0 country text NOT NULL,
    =
    <= div>=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)))
    <= div class=3D"gmail_quote">

    CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
    <= div class=3D"gmail_quote">
    =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', 2017, 02);
    CREATE TABLE= public.sale_uk_2017_jan PARTITION OF sales
    =C2=A0 =C2=A0 FOR VALUES F= ROM ('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');

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

    <= /div>
    Thank you for the example, you are absolutely correct and we were= confused.=C2=A0

    Given our new found understanding= do you mind if we iterate a bit on the UI/UX?=C2=A0 What we were suggestin= g with the daily/monthly/yearly drop down was a specific example of an expr= ession. Given that fact that doesn't seem to be required in an MVP, how= ever, I do think a more interactive experience between the definition of th= e 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 but I'd love to float some ideas and mock ups past= you.=C2=A0

    -- Rob



    -= -
    Akshay Joshi
    Principal Software Engineer=C2=A0<= /div>

    =



    --
    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.com
    Twitter: @pgsnake

    <= /div>
    EnterpriseDB UK: http://www.enterprisedb.com
    The Enterp= rise PostgreSQL Company



    --
    Dav= e Page
    Blog: h= ttp://pgsnake.blogspot.com
    Twitter: @pgsnake

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




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

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




    --
    Akshay Joshi
    Principal Software= Engineer=C2=A0

    =



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

    EnterpriseD= B 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
    --001a1145b0e0d8252a0551ea6b25--