Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d8oHD-0006m3-MJ for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:37:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d8oHB-0004ob-Sv for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:37:05 +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 1d8oGx-0004PT-3D for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:36:51 +0000 Received: from mail-pf0-x22d.google.com ([2607:f8b0:400e:c00::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d8oGs-00031b-Q1 for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:36:50 +0000 Received: by mail-pf0-x22d.google.com with SMTP id n23so9099877pfb.2 for ; Thu, 11 May 2017 06:36:46 -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=dF49//ReJ5W3lEcZHQOVU3kGmLsAN0JTCqluy1fP4Jk=; b=GI4C7nRCp0mpfGkAH2iFML62Q0V5MaSSAtfDx8cKThy3YP6H4Uo9/j9h0F4jtJ8aUt RXQbd7ldHMR8gp0bXkfwazBXYGGewYoiEkSYvL125CBNMvbfuVoqic/ZYw8jcZLSzWM2 V8N/d/6lpAU/iFCJKZvWrt0k9mt44Cf9guinGML28wCKopCBHsv4VNoakDVYipysNqca jH/2SidxLeOxt0kQNeZ9noM3EkfAnORmOyxnvs/4o1BqKzsZmCjANNu/bf00sgHH0NuT 3YB+26ogUJgpKVpQ6dzFGrUoShbgqlpVKP/hZkJKgIkPYWDWPm7p2QuGL/fIIiR1UCjE 58qg== 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=dF49//ReJ5W3lEcZHQOVU3kGmLsAN0JTCqluy1fP4Jk=; b=uMqztZJsUs4c24zxevR7tN58Qw8gTAMdoWKIzVh+UjevCNqCmN8zmJM1qyp05Jo2+5 XlKXpQwM3xEASEi/GP97Q9PWFuS7w6HAiNEcxvASK4n/ngH4oYxUm07prEs7cjKKewoA Lf5be9kCP+10lTisoZcSqLOJEHyxIBXNsz96P2AmAUc/gPV0/noZwPFHIkfKqPplzrx/ dNjroyHCiecRJORJdxJA/7XFjSBuNKKAxC8aUFhwi4sTxwxYEPvagBV1aRJTx0AzKYhl a2GLkeVlDe2KTFckAZ5w7r4xl7YKtUboBwBmQy8+KZneP7+7SBiFZ2ELYE9y3L+wPfED 7CyQ== X-Gm-Message-State: AODbwcDsnb2VWJzyfRHPNfPflrj78nic9jcseTVHI+HKPQZWjTP71MPx fErirMr9gmZhjQyxRj2fzwH+0QuDP9vx X-Received: by 10.84.229.72 with SMTP id d8mr467013pln.159.1494509804818; Thu, 11 May 2017 06:36:44 -0700 (PDT) MIME-Version: 1.0 Received: by 10.100.170.75 with HTTP; Thu, 11 May 2017 06:36:44 -0700 (PDT) In-Reply-To: References: From: Akshay Joshi Date: Thu, 11 May 2017 19:06:44 +0530 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: Shirley Wang , pgadmin-hackers Content-Type: multipart/alternative; boundary="94eb2c180d0a07a6e4054f3faea1" 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 --94eb2c180d0a07a6e4054f3faea1 Content-Type: text/plain; charset="UTF-8" Hi Dave On Thu, May 11, 2017 at 6:54 PM, Dave Page wrote: > > > On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi < > akshay.joshi@enterprisedb.com> wrote: > >> Hi >> >> On Thu, May 4, 2017 at 4:00 PM, Dave Page wrote: >> >>> Hi >>> >>> On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi < >>> akshay.joshi@enterprisedb.com> wrote: >>> >>>> Hi All >>>> >>>> On Wed, May 3, 2017 at 5:35 PM, Dave Page wrote: >>>> >>>>> Great, thanks. >>>>> >>>>> I think it's clear that we need to display the child partitions in the >>>>> treeview. I don't see any other sensible way of enabling those operations >>>>> without an extremely contrived dialogue design. >>>>> >>>>> Please now document how those features will be implemented; e.g, for >>>>> each one: >>>>> >>>>> - View table data: Parent and partition context menu. >>>>> - Attach/detach partitions: Parent properties dialogue >>>>> ... >>>>> >>>>> That will then give us a list of places we'll need to (re)design >>>>> dialogues and menus etc. for. >>>>> >>>> >>>> As per my knowledge on Partitioning, I think we will have to >>>> implement following things in parent and child: >>>> >>>> Parent: >>>> >>>> 1. View Table data : No need to change any logic, it's working. >>>> 2. Correct jinja template to show correct SQL in SQL pane. >>>> 3. Create partitioned table - >>>> - Add one switch control ("Partitioned Table?") in General tab >>>> of Table dialog. >>>> - Add new tab "Partitions". >>>> - Add one select2 control (Partition Type :Range/List) in >>>> "Partitions" tab. >>>> - Create one subnode control to specify number of key columns >>>> with expressions. For List partition only one row will be there + button >>>> will be disabled, and for Range partition + button will be enabled. Here is >>>> the syntax as per documentation [ PARTITION BY { RANGE | LIST } >>>> ( { *column_name* | ( *expression* ) } [ COLLATE *collation* ] [ >>>> *opclass* ] [, ... ] ) ]. *Design discussion required here for >>>> how user will specify expression, collate and opclass*. >>>> 4. Create N number of partitions: >>>> - Design one control (subnode control) so that user will add N >>>> number of partitions. Here is the syntax as per documentation CREATE >>>> TABLE *table_name PARTITION OF parent_table [ ( { >>>> column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | >>>> table_constraint } [, ... ] ) ] FOR VALUES partition_bound_spec * >>>> *partition_bound_spec* is: >>>> >>>> { IN ( { *bound_literal* | NULL } [, ...] ) | >>>> FROM ( { *bound_literal* | UNBOUNDED } [, ...] ) TO ( { *bound_literal* | UNBOUNDED } [, ...] ) } >>>> >>>> - *Design discussion required here for how user will specify all >>>> the above combinations.* >>>> 5. Properties dialog "Partitions" Tab: >>>> - Partition Type control must be disabled. >>>> - User will be able to create/modify existing partitions. User >>>> won't be able to delete partitions as there are two modes Detach/Drop and >>>> we will have separate menu for it. >>>> 6. Drop/ Drop cascade, Truncate: No need to change any logic, it's >>>> working. >>>> 7. Attach Partitions: Create context menu on partitioned table. >>>> When user clicks, open one dialog with some controls to provide >>>> table(to be attach) and partition_bound_spec*. Design discussion >>>> required here.* >>>> 8. Not able to create constraints excluding check constraint: We >>>> will have to disable context menu, remove child nodes from browser tree for >>>> constraints and disable controls from the dialog. >>>> >>>> Child: >>>> >>>> 1. View Table Data: Add context menu. >>>> 2. Detach partition: Create context menu, when user click popped up >>>> confirmation message box. >>>> 3. View partition scheme in SQL pane: Changes required in jinja >>>> template. >>>> 4. Create primary/foreign/.. key constraint: No need to change any >>>> logic on GUI, but may need to change queries to fetch the partitioned >>>> tables. >>>> 5. Drop/ Drop cascade, Truncate: No need to change any logic. >>>> >>>> Apart from above it may be possible that I miss something, so we >>>> need to cover that too. >>>> >>> >>> OK, good. So now, let's break that down into a list of tasks, that we >>> can prioritise with Shirley. The initial list should be prioritised based >>> on your understanding I think, given the following criteria: >>> >>> - Changes that prevent pgAdmin breaking >>> - Changes that prevent pgAdmin showing incorrect data/info >>> - Changes that enable pgAdmin to show correct info >>> - Changes that add functionality for creating/dropping partitioned >>> tables as one unit >>> - Changes that add functionality for modifying individual partitions >>> independently >>> >>> Please document the requirements and initial plan on the pgAdmin Redmine >>> Wiki. >>> >> >> I have updated Redmine Wiki page regarding what needs to be >> implemented for partitioning. Can we discuss prioritisation of the task >> based on above criteria in the our meeting. Meanwhile I have started >> working on showing correct SQL for partitioned table. >> > > Which meeting? > Meeting with Shirley, which wasn't schedule last Friday as I was on leave. > > -- > Dave Page > VP, Chief Architect, Tools & Installers > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* --94eb2c180d0a07a6e4054f3faea1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave=C2=A0

On Thu, May 11, 2017 at 6:54 PM, Dave Page &= lt;dave.pag= e@enterprisedb.com> wrote:
=


On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <= span dir=3D"ltr"><akshay.joshi@enterprisedb.com> wrote:
Hi=C2=A0

On Thu, May 4, 2017 at 4:00 PM, Dave Page <dpage@pgadmin.org>= ; wrote:
Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay J= oshi <akshay.joshi@enterprisedb.com> wrote:=
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave = Page <dpage@pgadmin.org> wrote:
Great, thanks.

I think it's = clear that we need to display the child partitions in the treeview. I don&#= 39;t see any other sensible way of enabling those operations without an ext= remely contrived dialogue design.

Please now docum= ent how those features will be implemented; e.g, for each one:
- View table data: Parent and partition context menu.
- Attach/detach partitions: Parent properties dialogue
...

That will then give us a list of places we'll need= to (re)design dialogues and menus etc. for.
<= br>
=C2=A0 =C2=A0 As per my knowledge on Partitioning, I t= hink we will have to implement following things in parent and child:
<= div>=C2=A0 =C2=A0=C2=A0
=C2=A0 =C2= =A0Parent:
  1. View Table data : =C2=A0No need to change any logic, it's worki= ng.
  2. Correct jinja template to show correct SQL in SQL pane.=C2=A0
  3. Create partitioned table -=C2=A0
    • Add = one switch control ("Partitioned Table?") in General tab of Table= dialog.
    • Add new tab &= quot;Partitions".=C2=A0
    • Add one select2 control (Partition Typ= e :Range/List) in "Partitions" tab.
    • Create one subnode control to specify number of k= ey columns with expressions. For List partition only one row will be there = + button will be disabled, and for Range partition + button will be enabled= . Here is the syntax as per documentation=C2=A0[ PARTITION BY { RANG= E | LIST } ( {=C2=A0column_name=C2=A0| (=C2=A0expression=C2=A0) } [ COLLATE=C2=A0collation= =C2=A0] [=C2=A0opclass=C2=A0] [, ..= . ] ) ].=C2=A0Design discussion required here for how user will s= pecify expression, collate and opclas= s.=C2=A0=C2=A0 =C2=A0
  4. Create N number of partitions:=C2=A0
    • Design one control (subnode control) so that user will add N numb= er of partitions. Here is the syntax as per documentation=C2=A0CREA= TE =C2=A0TABLE =C2=A0table_name=C2=A0PARTITION OF parent_table [ ( =C2=A0 { column_name [ WI= TH OPTIONS ] [ column_constraint [ ... ] ] =C2=A0 =C2=A0 | table_constraint= } =C2=A0 =C2=A0 [, ... ] ) ] FOR VALUES partition_bound_spec=C2=A0partition_bound_spec=C2=A0is:
      { IN ( { bound_literal=
 | NULL } [, ...] ) |
  FROM ( { bound_literal<=
/em> | UNBOUNDED } [, ...] ) TO ( { bound_literal | UNBOUNDED } [, ...] ) }
    • Design disc= ussion required here for how user will specify all the above combinations.<= /b>
  5. Properties = dialog "Partitions"= Tab:
    • Partition Type = control must be disabled.=C2=A0
    • User will be able to create/modify existing partitions. User won'= ;t be able to delete partitions as there are two modes Detach/Drop and we w= ill have separate menu for it.=C2=A0
  6. Drop/ Drop cascade, Truncate: No need to change any logic,= it's working.
  7. Attach Partitions: = =C2=A0Create context menu on partitioned table. When user clicks, open one = dialog with some=C2=A0controls to=C2=A0provide table(to be a= ttach) and partition_bound_spec.=C2=A0Design=C2=A0discussion r= equired here.
  8. Not able to create constraints excluding check co= nstraint: =C2=A0We will have to disable context menu, remove child nodes fr= om browser tree for constraints and disable controls from the dialog.=C2=A0
=C2=A0 =C2=A0 Child= :
  1. Vie= w Table Data: Add context menu.=C2=A0
  2. De= tach partition: Create context menu, when user click popped up confirmation= message box. =C2=A0
  3. View partition sche= me in SQL pane: Changes requi= red in jinja template. =C2=A0=
  4. Create primary/foreign/.. key constrain= t: No need to change any logic on GUI, but may need to change queries to fe= tch the partitioned tables.=C2=A0
  5. Dr= op/ Drop cascade, Truncate: No need to change any logic.
=C2=A0 = =C2=A0 Apart from above it may be possible that I miss something, so we nee= d to cover that too.=C2=A0

OK, good. So now, let's break that down into = a list of tasks, that we can prioritise with Shirley. The initial list shou= ld be prioritised based on your understanding I think, given the following = criteria:

- Changes that prevent pgAdmin breaking<= /div>
- Changes that prevent pgAdmin showing incorrect data/info
<= div>- Changes that enable pgAdmin to show correct info
- Changes = that add functionality for creating/dropping partitioned tables as one unit=
- Changes that add functionality for modifying individual partit= ions independently

Please document the re= quirements and initial plan on the pgAdmin Redmine Wiki.
<= /blockquote>

=C2=A0 =C2=A0 =C2=A0I have upda= ted Redmine Wiki page regarding what needs to be implemented for partitioni= ng. Can we discuss prioritisation of the task based on above criteria in th= e our meeting. Meanwhile I have started working on showing correct SQL for = partitioned table. =C2=A0

Which meeting?=C2=A0

=C2=A0 =C2=A0Meeting with Shirley, which wasn't sc= hedule last Friday as I was on leave. =C2=A0 =C2=A0

--
Dave Page
VP, Chief A= rchitect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Ent= erprise PostgreSQL Company

Blog: http://pgsnake.blogsp= ot.com
Twitter: @pgsnake



--
Akshay Joshi


<= b>Phone: +91 20-3058-9517
Mobile: +91 976-788= -8246
--94eb2c180d0a07a6e4054f3faea1--