Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d8lRA-0003QG-EE for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 10:35:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d8lR9-0001bQ-P7 for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 10:35:11 +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 1d8lR8-0001ZT-5v for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 10:35:10 +0000 Received: from mail-pg0-x22a.google.com ([2607:f8b0:400e:c05::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d8lR0-0005KA-MY for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 10:35:08 +0000 Received: by mail-pg0-x22a.google.com with SMTP id 64so12643879pgb.3 for ; Thu, 11 May 2017 03:35:02 -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=3EM9puJfEYARj8Brjafo32ACFBe5LXYhx3ivrLTOs3U=; b=16VdyqwQPYH+bGFqhZhYzy+DKmYRNXlny4DeNKUAy7Sekb02rZFLXnaCYc1T/leBYg GovCM+Dez9lhW7ibnXnX2bcfbTq2oA8UgeGyU1rFVcJcv4oAisNa/2B8fbWQRUFiMgLX vDeykTx4LWr2kvp47D2I0Q7cIBMcPsCuEmctFa0JXnDBJ2FkSEEy7v2tp1r0R9xHkq9k cod397lBh9oN3F6zxZT2LU0QV+P8w8vQ7e/5Es0u8z3UPcvQAhN2LsI2X8dYBVYFPBIP Cx2LH/yUlklvue4DmbD5D3IiGiOYAx4EKNObJXzNKqDAGlHsaT98hgPniOApRBK/bGm3 dmmQ== 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=3EM9puJfEYARj8Brjafo32ACFBe5LXYhx3ivrLTOs3U=; b=DAPBzW0MbEBQSeuYc+QggWrY0q5MvDl3N9k42KCEfudY3odsCMfz8Pdg2527QayWSk 5oH5g5n0y5TJ16M8m1UV4micpabuSKSPxW2mUHzXTN2yjy+UEr+EIIHqRTISODmwGK5/ p/B5LtFPoPaaIDJGRvxtIN6t9GRdTQAkZEJS+uLyQpNB7Y8T6VoBqLS+lty9ie1CCIMj aWFNn1CGwTwZWIw74QbHh6bdKQf6LCxc+xfXAi2Cl0jSD0+B1t4DsVIwkrwNRkSe84IF NSGZMDqtR/HXzt+a0xzSpr4QOvKmtMeiyWAUKiDI2PHIZsXDV74sk9znax+5vJyulgsc 7v5w== X-Gm-Message-State: AODbwcAmYzEl4zus/8yWvidG6qMVVamKRDvgmmEPkxV0sJ97ZIJ+Gl82 xQBa07P5GjkGHeYt9mtohJxrVd7mvrAggFg= X-Received: by 10.99.53.207 with SMTP id c198mr11773583pga.182.1494498901540; Thu, 11 May 2017 03:35:01 -0700 (PDT) MIME-Version: 1.0 Received: by 10.100.170.75 with HTTP; Thu, 11 May 2017 03:35:00 -0700 (PDT) In-Reply-To: References: From: Akshay Joshi Date: Thu, 11 May 2017 16:05:00 +0530 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Shirley Wang Cc: pgadmin-hackers , Dave Page Content-Type: multipart/alternative; boundary=94eb2c19355024e1ae054f3d240b 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 --94eb2c19355024e1ae054f3d240b Content-Type: text/plain; charset=UTF-8 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. > > Thanks! > > -- > 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-9517Mobile: +91 976-788-8246* --94eb2c19355024e1ae054f3d240b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi=C2=A0

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

On Thu, M= ay 4, 2017 at 10:29 AM, Akshay Joshi <akshay.joshi@enterprised= b.com> wrote:
Hi All

On Wed, May 3, 2017 at 5:35 PM, Dave Page <dpage@pgad= min.org> wrote:
G= reat, thanks.

I think it's clear that we need to dis= play the child partitions in the treeview. I don't see any other sensib= le way of enabling those operations without an extremely contrived dialogue= design.

Please now document how those features wi= ll be implemented; e.g, for each one:

- View table= data: Parent and partition context menu.
- Attach/detach partiti= ons: Parent properties dialogue
...

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 imp= lement following things in parent and child:
=C2=A0 =C2=A0=C2=A0<= /div>
=C2=A0 =C2=A0Parent:
<= div>
  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. Creat= e partitioned table -=C2=A0
    • Add one sw= itch control ("Partitioned Table?") in General tab of Table dialog.
    • Add new tab "Partitions".=C2=A0
    • Add one selec= t2 control (Partition Type :Range/List) in "Partitions" tab.
      • =
    • Create one subnode control to specify number of key columns with expre= ssions. For List partition only one row will be there + button will be disa= bled, and for Range partition + button will be enabled. Here is the syntax = as per documentation=C2=A0[ PARTITION BY { RANGE | LIST } ( {=C2=A0<= /span>column_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 specify expressio= n, collate and opclass.=C2=A0=C2=A0 =C2=A0
  4. Create N number of partitions:=C2=A0
    • Design one control (<= span id=3D"gmail-m_-4117111627174800998m_4032068786678779424:2ts.10">subnod= e control) so that user will add N number of partitions. Here is the= syntax as per documentation=C2=A0CREATE =C2=A0TABLE =C2=A0table_name=C2=A0PARTITION OF parent_table [ ( =C2=A0 { column_name [ W= ITH OPTIONS ] [ column_constraint [ ... ] ] =C2=A0 =C2=A0 | table_constrain= t } =C2=A0 =C2=A0 [, ... ] ) ] FOR VALUES partition_bound_spec=C2=A0partition_bound_spec=C2=A0is:
      { IN ( { =
bound_literal | NULL } [, ...] ) |
  FROM ( { bound_literal | UNBOUNDED } [, ...] ) TO ( { bo=
und_literal | UNBOUNDED } [, ...] ) }
    • Design discussion required here for how user will specify all the ab= ove combinations.
  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 part= itions as there are two modes Detach/Drop and we will have separate menu fo= r it.=C2=A0
  6. Drop/ Dr= op cascade, Truncate: No need to change any logic, it's working.
    7. Attach Partitions: =C2=A0Create context menu o= n 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=A0discussion required here.
  7. Not able to cr= eate constraints excluding check constraint: =C2=A0We will have to disable = context menu, remove child nodes from browser tree for constraints and disa= ble controls from the dialog.=C2=A0
=C2=A0 =C2=A0 Child:
  1. View Table D= ata: Add context menu.=C2=A0
  2. Detach part= ition: Create context menu, when user click popped up confirmation message = box. =C2=A0
  3. View partition scheme in SQL pane: Changes required in jinja template. =C2=A0
  4. Create primary/foreign/.. key constraint: No need to change a= ny logic on GUI, but may need to change queries to fetch the partitioned ta= bles.=C2=A0
  5. Drop/ Drop cascade, Trun= cate: 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 cover that too.=C2= =A0

OK, good. So now, let's break that down into a list of tasks, that w= e 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 tha= t prevent pgAdmin showing incorrect data/info
- Changes that enab= le pgAdmin to show correct info
- Changes that add functionality = for creating/dropping partitioned tables as one unit
- Changes th= at add functionality for modifying individual partitions independently

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

=C2=A0 =C2=A0 =C2=A0I have updated Redmine Wiki page regarding wha= t needs to be implemented for partitioning. Can we discuss prioritisation o= f the task based on above criteria in the our meeting. Meanwhile I have sta= rted working on showing correct SQL for partitioned table.=C2=A0=C2=A0

Thanks!

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

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



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


=
Pho= ne: +91 20-3058-9517
Mobile: +91 976-788-8246
--94eb2c19355024e1ae054f3d240b--