Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d8oDP-0006Tw-CZ for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:33:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d8oDO-0001C8-MR for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:33:10 +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 1d8oD9-0000m5-1j for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:32:55 +0000 Received: from mail-qt0-x231.google.com ([2607:f8b0:400d:c0d::231]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d8oD4-0002w5-9n for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:32:54 +0000 Received: by mail-qt0-x231.google.com with SMTP id t26so16952481qtg.0 for ; Thu, 11 May 2017 06:32:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=W4KD9iDSrsLLMJY5sOIDr7I/BM339guNsOryHu50YuE=; b=vXV/Kd4+3dD80BqyK8oda9DAQ0gs1PVtny0Icq83XhUcikTNNakghs2UyewQtoFzoE I+wo1mo5X6C40LlYi28jolgFg7azp0GfTixJ5WoINMglkMvu4x++PVV3mA6sJ0lgIOlC r/I5Nkf84/TrSjpAADDxfQPsAMvgd3tdaEPOX+9oNNMeYdOjM1sjFwz2CHE4ioatnW30 LMdSW0ejGV7ucTFncu50wgaMPpu/K9RuU5tvqZ2Ct4CxKVTMfUgI7HU3f6Uu6mZzhtwV RKaXEEco9xYLajPjM6ukXapaZKm36tttkQPXYgD4BLuZvSTRqNkF4CK5SypTfewC+HMj 6dHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=W4KD9iDSrsLLMJY5sOIDr7I/BM339guNsOryHu50YuE=; b=tkdoOWIWUXQSPKKw3lrc4OyUBFzXX49q9hhek/Ig9P78QaWjK7PSeU7KLC9N24dLDH oNvi94hb9Tyy4qKs5QC6Wc9J/krR+q2QIPXB6sPIitLZ8J8TgHtD3bgC1GOKUlB77UDn WXJ31xERgc4Iv2zE+7gNvBDoPqpKEcjeucaeAD4UkssmPa+Tp/tRiwmrl/2nrpdNaxH6 LbKScm6nzpTne5FIhanZs+/MuCAuuy0NgGX1nqYKCIW8sM1sB2TTwsExKIBYdLJuCNX3 acFaBZTmIIWCCes4tkX1ARpf2GqQZ/a0p7Jyn4zn79BlVAPZ1wPpI1RXW+pRnj2/Vf7U ZMKA== X-Gm-Message-State: AODbwcCchNqAotRRd4AIpf9lqgAOxF4p+55BjgToqM466Z5TbaAzFQ97 PGpJ0PoYjq0UGtUhC8YLM3bXGkIcYA87 X-Received: by 10.200.34.35 with SMTP id o32mr426141qto.67.1494509567945; Thu, 11 May 2017 06:32:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Anne Thomas Date: Thu, 11 May 2017 13:32:37 +0000 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page , Akshay Joshi Cc: Shirley Wang , pgadmin-hackers Content-Type: multipart/alternative; boundary="001a11407816e9462a054f3f9f15" 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 --001a11407816e9462a054f3f9f15 Content-Type: text/plain; charset="UTF-8" Akshay, Dave, I'm filling in for Shirley this week. If you are referring to the weekly meeting tomorrow, I'm planning on joining that and can walk through an exercise to help figure out prioritization. Thanks, Anne On Thu, May 11, 2017 at 9:24 AM 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? > > -- > Dave Page > VP, Chief Architect, Tools & Installers > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > -- Anne Thomas Product Manager Pivotal Labs --001a11407816e9462a054f3f9f15 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Akshay, Dave,

I'm filling in for Sh= irley this week. If you are referring to the weekly meeting tomorrow, I'= ;m planning on joining that and can walk through an exercise to help figure= out prioritization.

Thanks,
Anne
<= /div>
On Thu, May 11, 2017 a= t 9:24 AM Dave Page <dave.= page@enterprisedb.com> wrote:
On= Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.joshi@en= terprisedb.com> wrote:
Hi=C2=A0

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

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@e= nterprisedb.com> wrote:
Hi All

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

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.<= /div>

Please now document how those features will be imp= lemented; e.g, for each one:

- View table data: Pa= rent and partition context menu.
- Attach/detach partitions: Pare= nt properties dialogue
...

That will the= n give us a list of places we'll need to (re)design dialogues and menus= etc. for.

=C2=A0 =C2=A0= As per my knowledge on Partitioning, I think we will have to implement fol= lowing things in parent and child:
=C2=A0 =C2=A0=C2=A0
= =C2=A0 =C2=A0Parent:
<= ol>
  • View Table data : =C2=A0No= need to change any logic, it's working.
  • Correct jinja template to show correct SQL in SQL pane.=C2=A0
  • Create partitioned table -=C2=A0<= /li>
    • Add one switch control ("Partitio= ned Table?") in General tab of Table dialog.
    • Add new tab "Partitions".=C2=A0
      • <= li>Add one select2 control (Partition Type :Range/List) in "Partitions= " tab.
    • Create one subnod= e control to specify number of key columns with expressions. For Lis= t partition only one row will be there + button will be disabled, and for R= ange partition + button will be enabled. Here is the syntax as per document= ation=C2=A0[ PARTITION BY { RANGE | LIST } ( {=C2=A0column_name=C2=A0| (= =C2=A0expression<= /em>=C2=A0) } [ COLLATE=C2=A0collation=C2=A0] [=C2=A0opclass=C2=A0] [, ... ] ) ].=C2=A0Design disc= ussion required here for how user will specify expression, collate and opclass.=C2=A0=C2=A0 =C2=A0
  • Create N number of par= titions:=C2=A0
    • Design one control (subnode control) so that user will add N number of partitions. Here is the synta= x as per documentation=C2=A0CREATE =C2=A0TABLE =C2=A0table_name=C2=A0PARTITION OF par= ent_table [ ( =C2=A0 { column_name [ WITH OPTIONS ] [ column_constraint [ .= .. ] ] =C2=A0 =C2=A0 | table_constraint } =C2=A0 =C2=A0 [, ... ] ) ] FOR VA= LUES 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>
  • 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
  • Drop/ Drop cascade, Truncate: No need to change any logic,= it's working.
  • 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 requir= ed here.
  • Not able to create constraints excluding check constra= int: =C2=A0We will have to disable context menu, remove child nodes from br= owser tree for constraints and disable controls from the dialog.=C2=A0
    • =C2=A0 =C2=A0 Child:
    1. View Tab= le Data: Add context menu.=C2=A0
    2. Detach = partition: Create context menu, when user click popped up confirmation mess= age box. =C2=A0
    3. View partition scheme in= SQL pane: Changes required i= n jinja template. =C2=A0
      4. =
    4. Create primary/foreign/.. key constraint: No= need to change any logic on GUI, but may need to change queries to fetch t= he partitioned tables.=C2=A0
    5. Drop/ D= rop 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 need to co= ver that too.=C2=A0
    <= div>
    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 p= rioritised based on your understanding I think, given the following criteri= a:

    - Changes that prevent pgAdmin breaking
    - Changes that prevent pgAdmin showing incorrect data/info
    - C= hanges that enable pgAdmin to show correct info
    - Changes that ad= d functionality for creating/dropping partitioned tables as one unit
    <= div>- Changes that add functionality for modifying individual partitions in= dependently

    Please document the requireme= nts and initial plan on the pgAdmin Redmine Wiki.

    =C2=A0 =C2=A0 =C2=A0I have updated Red= mine Wiki page regarding what needs to be implemented for partitioning. Can= we discuss prioritisation of the task based on above criteria in the our m= eeting. Meanwhile I have started working on showing correct SQL for partiti= oned table. =C2=A0

    Which meeting?=C2=A0

    --
    Dave Page
    VP, Chief Architect, Tools & Installers
    Enterpris= eDB: http://www.e= nterprisedb.com
    The Enterprise PostgreSQL Company

    Blog: http://pgsnake.blogspo= t.com
    Twitter: @pgsnake
    --
    Anne Thomas
    Product Manager
    Pivotal Labs
    --001a11407816e9462a054f3f9f15--