Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dBikM-0005Gy-ER for pgadmin-hackers@arkaria.postgresql.org; Fri, 19 May 2017 14:19:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dBikM-00052D-13 for pgadmin-hackers@arkaria.postgresql.org; Fri, 19 May 2017 14:19:14 +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 1dBik6-0004dj-Fo for pgadmin-hackers@postgresql.org; Fri, 19 May 2017 14:18:58 +0000 Received: from mail-it0-x232.google.com ([2607:f8b0:4001:c0b::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dBik2-0007u9-S8 for pgadmin-hackers@postgresql.org; Fri, 19 May 2017 14:18:57 +0000 Received: by mail-it0-x232.google.com with SMTP id a10so23750831itg.1 for ; Fri, 19 May 2017 07:18:54 -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=u2kXHm+v6oaNEkGZvTdXNkgnCsdBVqNUaw1BA+OJRXg=; b=aO5q2/5T6gg8Yt+rNXwzCTYucJoV71puzXoDzoVuBMr4Wpx26rOoQlfFSY5Tb2zzuu hobiNY0MCD+ARK+PubFp+li55G51aVyF0Lwzy86pnmz4jhjcAqWngFtCCZW96HY1tpwu jhsP/+hUsGhk7+gImrQrqX7QlO58/9WYi0+1yG8hd3JBCMQybq3xcKsAvVVcwHehyAE8 /nH0+T+d5cjSzVzK5jhcNxGwLszEj6upWBU3SqQKHoURDUTdr2e0TzXLp+LUCIWcAyEr ew1oND8l/BLd2ntpSaqO9Y8L8HfBtOWrIqRF0hu7Mtzfb2E7Zwt88keUGtGi2j96mVCL HKGQ== 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=u2kXHm+v6oaNEkGZvTdXNkgnCsdBVqNUaw1BA+OJRXg=; b=ZWgoddnP0a/jyREQl6s4kUCym6RENpVtCqY9SyIl4zUXU5rXDHnyqQEsaBPkvJXXg7 SI0tu/nzKYNHAF7twcGoxuAfGGDwQqBzqEQbG26+7bxA8ju4zR7Ktil5Y2zAN1Fs1X0M P3Jx03qAd6IV7dhXUI/2CICwhhLgcUBlnPu1MDbDjSZS3Mz/5LcjbD2CufHfd5rn+aUD IhUyO+ghDTLhxXyxtc8byuAdbh1pod/PrnZvLswAjyHlPxvNc77z2SURzKoNttnkf7ex Z1ffM9RkJ/GSbu0c1NCOHxukNldwlKo5rqrsQJFDahhMRg4oE++qSL39DaMRxGbGwKzG 9yVQ== X-Gm-Message-State: AODbwcAh2phO3xGIpPTQevNWDUK/94G95ft0r+Hz+cIR1NHQ1kFUkcFj ruUkDc60oCriG1SvZ/CNAaBhAW+nZekR6QnRPtG9VchVjQbqHVmGINa+Vb/+mh/neQqpZpwWOO2 7GaThqNUAuiEmL8xdpuRBYKMpiSWeHmcKn68sG2k/Y59u2VzgW1CGZ0UI27cfL0YVLiFlxE/U X-Received: by 10.36.127.200 with SMTP id r191mr10592949itc.91.1495203533886; Fri, 19 May 2017 07:18:53 -0700 (PDT) Received: from mail-it0-f42.google.com (mail-it0-f42.google.com. [209.85.214.42]) by smtp.gmail.com with ESMTPSA id j16sm3840441iod.22.2017.05.19.07.18.53 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 19 May 2017 07:18:53 -0700 (PDT) Received: by mail-it0-f42.google.com with SMTP id o5so119265103ith.1 for ; Fri, 19 May 2017 07:18:53 -0700 (PDT) X-Received: by 10.36.193.134 with SMTP id e128mr9652529itg.43.1495203532971; Fri, 19 May 2017 07:18:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.147 with HTTP; Fri, 19 May 2017 07:18:51 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Fri, 19 May 2017 15:18:51 +0100 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: pgadmin-hackers , Shirley Wang Content-Type: multipart/alternative; boundary="94eb2c08ebd27330ca054fe133e3" X-Pg-Spam-Score: -2.1 (--) 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 --94eb2c08ebd27330ca054fe133e3 Content-Type: text/plain; charset="UTF-8" On Thu, May 18, 2017 at 11:41 AM, Akshay Joshi < akshay.joshi@enterprisedb.com> wrote: > Hi All > > I have started implementation for Declarative Partitioning in pgAdmin4. > Following are the tasks that I have implemented till now: > > - Show partitioned table and it's partitions under the parent table. > Refer Partitioned_Table.png > - To implement above I have created 'partitions' collection node and > 'partition' node under table node which is nothing but table node itself. > To reduce redundant/duplicate code I have made following changes: > - Create new file "*utils.py*" under tables folder. Create a new > class BaseTableView(PGChildNodeView): derived from PGChildNodeView. > TableView and PartitionsView (new class for partition table) is > derived from BaseTableView. > - Move the common logic like dependencies, dependents, reversed > engineered sql, statistics, reset statistics in BaseTableView class > functions and then call that function from derived class like > BaseTableView.get_table_dependencies(self, tid) > - Will move more generic logic as we progress on this task. > - Updated supported nodes list in DataGrid(View Data), > Backup, Maintenance, Restore to show context menu for partitions. > - Make sure dependencies, dependents, statistics, truncate, > delete/drop and Reset Statistics works with partitions. > - Updated jinja template to show correct reversed engineered sql for > partitioned table. Please refer the "List_with_expression.png" for > List partition and "Range_with_column_expression.png" for Range > partition. > - Updated jinja template to show correct sql for partitions of parent > table. Please refer "SQL_Range_Partitions.png" and "SQL > _List_Partitions.png". Some R&D is still require for other syntax too. > > Please let me know above looks good and am I going in right direction. > Certainly looks like it to me. We may want to tweak some things based on the work Shirley is doing, but I think we're on the right path. Good work! > > > On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi < > akshay.joshi@enterprisedb.com> wrote: > >> 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-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 >> <+91%2097678%2088246>* >> > > > > -- > *Akshay Joshi* > *Principal Software Engineer * > > > > *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 > <+91%2097678%2088246>* > -- Dave Page VP, Chief Architect, Tools & Installers EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Blog: http://pgsnake.blogspot.com Twitter: @pgsnake --94eb2c08ebd27330ca054fe133e3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, May 18, 2017 at 11:41 AM, Akshay Joshi <akshay.jos= hi@enterprisedb.com> wrote:
Hi All

I have started implementation f= or Declarative Partitioning in pgAdmin4. Following are the tasks that I hav= e implemented till now:
  • Show partitioned table and it'= ;s partitions under the parent table. Refer Partitioned_Table.png
  • To implement above I hav= e created 'partitions' collection node and 'partition' node= under table node which is nothing but table node itself. To reduce redunda= nt/duplicate code I have made following changes:
    • Create new file "utils.= py" under tables folder. Create a new class=C2=A0BaseTableView= (PGChildNodeView):=C2= =A0derived from=C2=A0PGChildNodeV= iew. TableView and <= span id=3D"m_8606798334747608515:2pq.8">PartitionsView (new=C2=A0cla= ss for partition table) is derived from=C2=A0BaseTableView.
    • Move the common logic like=C2=A0depe= ndencies, dependents, reversed engineered sql, statistics, reset statistics in=C2=A0BaseTableView class functions=C2=A0and then= call that function from derived class=C2=A0= like=C2=A0BaseTableView<= /span>.get_table_dependencies(self, tid)
      • Will move m= ore generic logic as we=C2=A0progress on this task.
  • Updated supporte= d nodes list in DataGrid(V= iew Data), Backup,=C2=A0Maintenance, Restore to show context menu for parti= tions.
  • Make sure=C2=A0dependencies, dependents, statistics,=C2=A0truncate= , delete/drop and Reset Statistics works with partitions.
  • Updated jinja template to show correct re= versed engineered sql for = partitioned table. Please refer the "List_with_expression.png" for List partition and "= ;Range_with_column_expression.png" for Range partition.=C2=A0
  • Updated jinja template to show correct sql for partitions of parent table. Plea= se refer "SQL_Range_P= artitions.png" and &q= uot;SQL_List_Partitions.png". Some R&D is s= till require for other syntax too.
Please let me know above looks=C2=A0g= ood and am I going in right direction.
=
Certainly looks like it to me. We may want to tweak some thi= ngs based on the work Shirley is doing, but I think we're on the right = path.

Good work!
=C2=A0

<= div class=3D"h5">

= On Thu, May 11, 2017 at 7:06 PM, Akshay Joshi <akshay.joshi@en= terprisedb.com> wrote:
Hi Dave=C2=A0

On Thu, May 11, 2= 017 at 6:54 PM, Dave Page <dave.page@enterprisedb.com> wrote:


On Thu, May 11, 2017 at 11:35 AM, Ak= shay Joshi <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 Joshi <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 cle= ar 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 extrem= ely contrived dialogue design.

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

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

=
=C2=A0 =C2=A0 As per my knowledge on Partitioning, I thin= k we will have to implement following things in parent and child:
=C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0= Parent:
  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
    • Add one switch control ("P= artitioned Table?") in General tab of Table dialog.
      • Add new tab "Partitions".=C2=A0
    • Add one select2 control= (Partition Type :Range/List) in "Partitions" tab.
    • Create= one subnode control to specify number of key columns with expr= essions. For List partition only one row will be there + button will be dis= abled, and for Range partition + button will be enabled. Here is the syntax= as per documentation=C2=A0[ PARTITION BY { RANGE | LIST } ( {=C2=A0= column_name=C2=A0| (=C2=A0expression=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
  4. Create N number of partitions:=C2=A0
    • Design one control (su= bnode 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 [ WITH OPTIONS ] [ column_constraint [ ... ] ] =C2=A0 =C2=A0 | = table_constraint } =C2=A0 =C2=A0 [, ... ] ) ] FOR VALUES partition_bound_sp= ec=C2=A0partition_bound_spec=C2= =A0is:
      { 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 &quo= t;Partitions" Tab:
    • Partition Type control must be disabled.=C2=A0
    • User will be able to create/modify existing partit= ions. User won't be able to delete partitions as there are two modes De= tach/Drop and we will have separate menu for it.=C2=A0
  6. Drop/ Drop cascade, Truncate: No need = to change any logic, it's working.
  7. A= ttach Partitions: =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 part= ition_bound_spec.=C2=A0De= sign=C2=A0discussion required here.
  8. Not able to create con= straints excluding check constraint: =C2=A0We will have to disable context = menu, remove child nodes from browser tree for constraints and disable cont= rols from the dialog.=C2=A0
=C2=A0 =C2=A0 Child:
  1. View Tabl= e Data: Add context menu.=C2=A0
  2. Detach p= artition: Create context menu, when user click popped up confirmation messa= ge box. =C2=A0
  3. View partition scheme in = SQL pane: Changes required in jinja template. =C2= =A0
  4. Create primary/foreign/.. key constr= aint: No need to change any logic on GUI, but may need 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 miss something, so we ne= ed 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 sho= uld 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 uni= t
- Changes that add functionality for modifying individual parti= tions independently

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

=C2=A0 =C2=A0 =C2=A0I have upd= ated Redmine Wiki page regarding what needs to be implemented for partition= ing. Can we discuss prioritisation of the task based on above criteria in t= he 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 schedule last Friday as I was on leave. =C2=A0 =C2=A0

--
Dave Page
VP, Chief Archite= ct, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterpris= e PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twi= tter: @pgsnake



--
Akshay Joshi
Principal Software E= ngineer=C2=A0


<= /span>



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

=




--
=
Dave Page=
VP, Chief Architect, Tools & Installers
EnterpriseDB: http://www.enterprisedb.com=
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Tw= itter: @pgsnake
--94eb2c08ebd27330ca054fe133e3--