Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d6E1t-0002AA-8Y for pgadmin-hackers@arkaria.postgresql.org; Thu, 04 May 2017 10:30:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d6E1s-0007yc-Oy for pgadmin-hackers@arkaria.postgresql.org; Thu, 04 May 2017 10:30:36 +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 1d6E1r-0007x7-CJ for pgadmin-hackers@postgresql.org; Thu, 04 May 2017 10:30:35 +0000 Received: from mail-it0-x22b.google.com ([2607:f8b0:4001:c0b::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d6E1j-0005LE-90 for pgadmin-hackers@postgresql.org; Thu, 04 May 2017 10:30:33 +0000 Received: by mail-it0-x22b.google.com with SMTP id w68so11866566itc.0 for ; Thu, 04 May 2017 03:30:26 -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=eG/zbazGVIvep9THEbX0AWeC0get1EBza00PUMFg680=; b=P4xtaCvsCovz6d+lXIVAFuG5aocYT4o7iM1IUubAhECRwa4g6ot/16+rdLJOgsp2fK M15LV0DgmTAVgPDuh7sF+DEgnSGm2lKS0owI+jWsKcvrec8ZNhY8QneWzbnFeX34Q9lj 5yZRU6vJtd6YmijqIq+ZeTkVJ+OSRvYtXyxHislBJSPEBuwDmynqgAwvMGgRxbiKu30R KTq2idrY6ibozFlCe9NYafirrt4CddCv2NZ8LVbmWv8ECA40DRcUP/T0LIZ3BgpsQOLW ia4pvXqSTJoiRaKXe7MqM1Ky7ElUZI2/XKzBMm9wGvToUNX+MC680IMHUlcaOc4qqgIx jpXg== 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=eG/zbazGVIvep9THEbX0AWeC0get1EBza00PUMFg680=; b=r/d4jq6hnNKe+PW/RZgIZM0goeTOHflQSZ1hHKEX+Hw6MegvnRYxAv/Gby5pD3sksA 1SEwFyTi0OI88GkjxPL6eOmLO7PtWMWVDVEtX/PXwVyK5do1nN3tw+ndwDE1GCW9RM+M pPCI69D4NMTGx+UxaVXHk7KK5MNn8nVNCSQJO3P6G3yaKESD7DeZj8Qvo4kuViyxnBUL mmrRbIP2wn0rmgBWH8nnjAoYyQnw7rngCHeXr+m+bJ+fjWWQK1swoAS5LyBGLxN1voKY vjdJHprX6iX3pl+9qBZbtjuB0XknHVjB/JgfB/Nc9N2bgVjY54xv4JKIMvx/VIUgUsOC usQw== X-Gm-Message-State: AN3rC/6k4eHmk4s5f69EI3VnWr4nW8rdWG6xlJgwpYwU86+lBc3UWBP0 iJBQ+6ieTWOdbEfD7DEokDT/LjTfiw== X-Received: by 10.36.193.134 with SMTP id e128mr948297itg.43.1493893825843; Thu, 04 May 2017 03:30:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.167 with HTTP; Thu, 4 May 2017 03:30:25 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Thu, 4 May 2017 11:30:25 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary=94eb2c08ebd2d39359054eb04280 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 --94eb2c08ebd2d39359054eb04280 Content-Type: text/plain; charset=UTF-8 Hi On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi 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. Thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --94eb2c08ebd2d39359054eb04280 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi

On Thu, May 4, 2017 at 10:29 AM, Akshay Joshi <<= a href=3D"mailto:akshay.joshi@enterprisedb.com" target=3D"_blank">akshay.jo= shi@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 t= hat we need to display the child partitions in the treeview. I don't se= e 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.
- Att= ach/detach partitions: Parent properties dialogue
...
<= br>
That will then 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 following things in parent and child:
=C2= =A0 =C2=A0=C2=A0
=C2=A0 =C2=A0Pare= nt:
  1. View Table data : =C2=A0No need to change any logic, it's working.
  2. Correct jinja template to show correct SQL in SQL pane.=C2=A0
  3. Create partitioned table -=C2=A0
    • Ad= d one switch control ("Partitioned Table?") in General tab of Tab= le dialog.
    • Add new = tab "Partitions".=C2=A0
    • Add one select2 control (Partitio= n Type :Range/List) in "Partitions" tab.
    • Create one subnode control to specify numbe= r 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 e= nabled. Here is the syntax as per documentation=C2=A0[ PARTITION BY = { RANGE | LIST } ( {=C2=A0column_name=C2=A0| (=C2=A0expression=C2=A0) } [ COLLA= TE=C2=A0coll= ation=C2=A0] [=C2=A0opclass=C2=A0] [, ... ] ) ].=C2=A0Design discussion required h= ere for how user will specify expression, collate and opclass.=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 number of partitions. Here is the syntax as per do= cumentation=C2=A0CREATE =C2=A0TABLE =C2=A0table_name=C2=A0PARTITION OF parent_tabl= e [ ( =C2=A0 { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] = =C2=A0 =C2=A0 | table_constraint } =C2=A0 =C2=A0 [, ... ] ) ] FOR VALUES pa= rtition_bound_spec=C2=A0partition_bound_spec=C2=A0is:
      { IN ( { bound_literal | NULL } [, ...] ) |
  FROM ( { bound_liter=
al | UNBOUNDED } [, ...] ) TO ( { bound_literal | UNBOUNDED } [, ...] ) }
      • De= sign discussion required here for how user will specify all the above combi= nations.
  5. Pr= operties dialog "Part= itions" Tab:
    • Par= tition 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/D= rop and we will have separate menu for it.=C2=A0
  6. Drop/ Drop cascade, Truncate: No need to chang= e any logic, it's working.
  7. Attach Pa= rtitions: =C2=A0Create context menu on partitioned table. When user clicks,= open one dialog with some= =C2=A0controls to=C2=A0provide = table(to be attach) and partition_bound_spec.=C2=A0Design=C2=A0<= wbr>discussion required here.
  8. Not able to create constraints ex= cluding check constraint: =C2=A0We will have to disable context menu, remov= e child nodes from browser tree for constraints and disable controls from t= he dialog.=C2=A0
= =C2=A0 =C2=A0 Child:
  1. View Table Data: Add context menu.=C2=A0
  2. Detach partition: Create context menu, when user click pop= ped up confirmation message box. =C2=A0
  3. = View partition scheme in SQL pane: Changes required in jinj= a template. =C2=A0
  4. Create primary= /foreign/.. key constraint: No need to change any logic on GUI, but may nee= d to change queries to fetch the partitioned tables.=C2=A0
  5. Drop/ Drop cascade, Truncate: No need to change any = logic.
=C2=A0 =C2=A0 Apart from above it may be possible that I mi= ss something, so we need 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 initia= l list should be prioritised based on your understanding I think, given the= following criteria:

- Changes that prevent pgAdmi= n 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 indivi= dual partitions independently

Please docu= ment the requirements and initial plan on the pgAdmin Redmine Wiki.

Thanks!

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enter= prise PostgreSQL Company
--94eb2c08ebd2d39359054eb04280--