Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d8o5B-0005o4-M7 for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:24:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d8o5B-0005gq-83 for pgadmin-hackers@arkaria.postgresql.org; Thu, 11 May 2017 13:24:41 +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 1d8o4v-0004q7-N2 for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:24:25 +0000 Received: from mail-it0-x236.google.com ([2607:f8b0:4001:c0b::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d8o4s-0000GL-I6 for pgadmin-hackers@postgresql.org; Thu, 11 May 2017 13:24:24 +0000 Received: by mail-it0-x236.google.com with SMTP id o5so43536393ith.1 for ; Thu, 11 May 2017 06:24:22 -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=T9M0xpDZBnTStR+bzuAql7FAbEFuoa5MykcLCl3IBrQ=; b=uGseku6gjm4SwkD9n2eYJEVu6jAzHzSpHenbmIL1KvVEColwtbtIbf/t5gL49OpBPO 8k4JEoXHXIUzGtT4STM2h50NgDdOYZrFe+ZbQaNcmHxJi63A+SJUO+z80ZZuXiWqnvT2 Nzs0NpwEdwoStbIREnCGDXre4y1WJz6CoJPMBAj8ePUKn8yNoH73xCopGaYEU46DmHnz G8qU4gX5OXvJctSkRYU7YWtbUIUM/I0SSXcm+KA6xQPNCFXiCAcJW741PuueVtjLjE/I QfOrLI1waPrDtPLX+UzPzanbKHVKgFjhIL2j5V4aKaG2vM58UPzNpO20UojPKttPQsr9 zHYw== 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=T9M0xpDZBnTStR+bzuAql7FAbEFuoa5MykcLCl3IBrQ=; b=Fspg2HXYCwQuNsnGhWPv9REtPnT/8JpK0L8DNRYNZt2c2d656FqEgasbvE6+iFSjjM h47RNVBQ3m2a1uyUs+AWj7b11XSlFQOJkhu96wtEBDPeHWdWOKRrYf1AzpoOMuImeGwp 8QTPxn6tazvypF2Gjk8s2ZzLBbaCSmw9+eKlRVyTx7O9Eopgn6KJQLjTl0jVvZMR7OjO 17P4mL1a1/M19MLn/jSg0GH6uBi9o25TAJvwivNnU0iUVGJoPIdN5g9dxFM3hi7Unrqx tkDXFbn3X7aih6qG6kZ7W9eQtcGVSLplJPyyA4KXcySLDLeoOuE4hvYfQZulW948SSKA VUKA== X-Gm-Message-State: AODbwcDKZqbSFr8AXuwWsAwkEQh8Iq8+EkCh5kumf+jTH9ejsu6VS3eT r9LzZJnE6BnXccfdnRRxrbXNqKEnUr2YvzaAdfkhzEoHnVP2U2S9uuhS+kesFYMAZUK5d9dhyIe SgTSpMlpBkdsvPgKHvIxVIqUqrWHN99KGZ3JzJifxhBqOlkME4D6HndVlvf7sM4IRI5GEh3dG X-Received: by 10.36.206.199 with SMTP id v190mr1002183itg.2.1494509061541; Thu, 11 May 2017 06:24:21 -0700 (PDT) Received: from mail-io0-f182.google.com (mail-io0-f182.google.com. [209.85.223.182]) by smtp.gmail.com with ESMTPSA id t136sm228234ita.22.2017.05.11.06.24.20 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 11 May 2017 06:24:20 -0700 (PDT) Received: by mail-io0-f182.google.com with SMTP id f102so21034027ioi.2 for ; Thu, 11 May 2017 06:24:20 -0700 (PDT) X-Received: by 10.107.3.165 with SMTP id e37mr342688ioi.232.1494509060547; Thu, 11 May 2017 06:24:20 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.167 with HTTP; Thu, 11 May 2017 06:24:19 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Thu, 11 May 2017 14:24:19 +0100 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: Shirley Wang , pgadmin-hackers Content-Type: multipart/alternative; boundary="001a113ed3beab0bc8054f3f819b" 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 --001a113ed3beab0bc8054f3f819b Content-Type: text/plain; charset="UTF-8" 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 --001a113ed3beab0bc8054f3f819b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, May 11, 2017 at 11:35 AM, Akshay Joshi <akshay.jos= hi@enterprisedb.com> wrote:
Hi=C2=A0

On Thu, May 4, 2017 at 4:00 PM, Dave Page <= span dir=3D"ltr"><dpage@pgadmin.org> wrote:
Hi

On Thu, May 4, 2017 at 10:29 AM, Aksha= y Joshi <akshay.joshi@enterprisedb.com> wro= te:
Hi All

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

I thi= nk it's clear that we need to display the child partitions in the treev= iew. I don't see any other sensible way of enabling those operations wi= thout an extremely contrived dialogue design.

Plea= se now document how those features will be implemented; e.g, for each one:<= /div>

- View table data: Parent and partition context me= nu.
- Attach/detach partitions: Parent properties dialogue
<= div>...

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 Parti= tioning, I think we will have to implement following things in parent and c= hild:
=C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0Parent:
  1. View Table data : =C2=A0No need to change any logic, it= 's working.
  2. Correct <= span id=3D"m_-8038329380486848692gmail-m_-4117111627174800998m_403206878667= 8779424:2ts.3">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 &q= uot;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 expressio= ns. 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 p= er documentation=C2=A0[ PARTITION BY { RANGE | LIST } ( {=C2=A0column_name=C2=A0| (=C2=A0expression=C2=A0) } [ COLLATE=C2= =A0collation=C2=A0] [=C2=A0= opclass=C2=A0] [, ... ] ) ].=C2=A0Design discussion 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 (subno= de control) so that user will add N number of partitions. Here is th= e syntax as per documentation=C2=A0CREATE =C2=A0TABLE =C2=A0<= em class=3D"m_-8038329380486848692gmail-m_-4117111627174800998m_40320687866= 78779424gmail-replaceable" style=3D"color:rgb(0,0,0);font-size:12px;backgro= und-color:rgb(247,247,247)">table_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_spec=C2=A0partition_bound_spec=C2=A0is:
      { IN ( { bound_literal | NULL } [, ...] ) |
  FROM ( { bound_literal | UNBOUNDED } [, =
...] ) TO ( { bound_literal | UNBOUNDED } =
[, ...] ) }
    • De= sign discussion required here for how user will specify all the above combi= nations.
  5. Pr= operties dialog "Partitions" Tab:
  6. Partition Type control must = be disabled.=C2=A0
  7. User w= ill 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 sepa= rate menu for it.=C2=A0
  8. Drop/ Drop cascade, Truncate: No need to change any logic, it's wor= king.
  9. Attach Partitions: =C2=A0Create co= ntext menu on partitioned table. When user clicks, open one =C2=A0= controls to=C2=A0provide table(to be attach) and partition_bound_spec.=C2=A0Design=C2=A0d= iscussion required here.
  10. 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 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 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 initial list should be prioritised based on your understanding I thin= k, given the following criteria:

- Changes that pr= event pgAdmin breaking
- Changes that prevent pgAdmin showing inc= orrect data/info
- Changes that enable pgAdmin to show correct in= fo
- Changes that add functionality for creating/dropping partiti= oned tables as one unit
- Changes that add functionality for modi= fying individual partitions independently

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

=C2=A0 = =C2=A0 =C2=A0I have updated Redmine Wiki page regarding what needs to be im= plemented 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. =C2=A0

Which meeting?=C2=A0

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

Blog: http://pgsnake.blogspot.com=
Twitter: @pgsnake
--001a113ed3beab0bc8054f3f819b--