Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d5t29-0001vR-65 for pgadmin-hackers@arkaria.postgresql.org; Wed, 03 May 2017 12:05:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d5t28-0002N9-7L for pgadmin-hackers@arkaria.postgresql.org; Wed, 03 May 2017 12:05:28 +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 1d5t27-0002Mw-5B for pgadmin-hackers@postgresql.org; Wed, 03 May 2017 12:05:27 +0000 Received: from mail-it0-x22a.google.com ([2607:f8b0:4001:c0b::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d5t1y-0004wz-Nl for pgadmin-hackers@postgresql.org; Wed, 03 May 2017 12:05:25 +0000 Received: by mail-it0-x22a.google.com with SMTP id o5so32089019ith.1 for ; Wed, 03 May 2017 05:05:18 -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=dJ9vDDFPlKPmCVtBW04N4sY6bLBgWzVpDhyWykEEOuI=; b=XqYwGyKVoDRblxHwYJFKWkMb2pfNM/Jznv1PVHRw4eQR3NlOGVAbtaQDhuyJUwQtA2 kI8upf1mITj3l2BT27RSWSgvSVghYZVLaKGQup+tK64JbUvSAywmrmiGBTvFbHqCC4Cq +tt29ZF+XZWZqUFSkZzK+oQ7M4ZdQwjJ95ZMOH/hLWIxa5WBKCqrVDR+5BMEQ81XVCIf J3dTXCDIufGaB/6+9g6tUH6UBuXX5HNiAFF3M2+xDfrvjB6xkdEQS2DzFudb8gTf9xjv LvA+G8D8WZNOwIQHjMUreuINFPbO4V09ivullUIkEvArH2JXVLb2Ud7UvXcOIbaZeFwG Uh7A== 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=dJ9vDDFPlKPmCVtBW04N4sY6bLBgWzVpDhyWykEEOuI=; b=Q9ou/Oc5saZ5qzngvg3VB/CimnabWBYt3zs8HgkkX58ZZk5N2JMStAbxdOlvhgRq/B wYLm2KjG620NKJEQOXeEADzoZw37qz/W3LlzXH3U/ZX5HUXwSH7eTHCvhU4xPZYocExs 9ub+q47o9hYdNBpzP3e3DqaZ5TwFd4ZvHqyrDLSavEP129wyMnpwcln0JxF1C2wX60DS iPzDa9X/JCTQ9hzkU/LnFTitJm/n9jMDI463RwY2Z/pMCgo0Lys9CacJRpGlCLu3NZQC +L7u1UA6tpA6vvYa0YrUczhmF4VbD51Bh43xhjJN/3X0M5KR8WxAIYCzwvhaooMvQceO 3sFg== X-Gm-Message-State: AN3rC/57LblHb6hSnIn4+1c0P0LaxqNLPw1RG2lJv/Oauw6G4DbwJZJB ODUwGkEPzHEAYmeJ44y/vkPsQFI0Zw== X-Received: by 10.36.152.196 with SMTP id n187mr8455242itd.28.1493813117807; Wed, 03 May 2017 05:05:17 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.167 with HTTP; Wed, 3 May 2017 05:05:17 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Wed, 3 May 2017 13:05:17 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary=94eb2c05e9a23fa0ea054e9d785b X-Pg-Spam-Score: -2.6 (--) 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 --94eb2c05e9a23fa0ea054e9d785b Content-Type: text/plain; charset=UTF-8 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. On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi wrote: > Hi Dave > > As per my understanding below operations required > > Parent: > > - View table data. > - View stats. > - Create regular/partitioned table > - Create N number of partitions. > - Drop/ Drop cascade, Truncate. > - Attach/Detach Partitions. > - Not able to create constraints excluding check constraint. > > Child: > > - View Table Data. > - View stats. > - View partition scheme in SQL pane > - Create primary/foreign/.. key constraint. > - Drop/ Drop cascade, Truncate > > > On Tue, May 2, 2017 at 8:25 PM, Dave Page wrote: > >> Hi >> >> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi < >> akshay.joshi@enterprisedb.com> wrote: >> >>> Hi All >>> >>> To implement Declarative Partitioning in existing Table dialog >>> below changes should be implemented: >>> >>> 1. *Icon: *As we have separate icon for view and materialised view, >>> we should have for partition table. I didn't find any in font awesome. >>> >>> They are really different object types though (even having their own >> collections), which isn't the case here. I'm not against having a slightly >> modified icon, but I don't think it's necessary. Note that the object icons >> come from pgAdmin III, and were custom designed for us. They aren't in font >> awesome etc. We'd need to tweak one of the existing ones. >> >>> >>> 1. *Inheritance*: >>> - A partition cannot have any parents other than the partitioned >>> table it is a partition of, nor can a regular table inherit from a >>> partitioned table making the latter its parent. That means partitioned >>> tables and partitions do not participate in inheritance with regular tables. >>> - When user creates regular table then Inherited from table(s) >>> control should not display partitioned table. >>> 2. *Constraints*: >>> - Primary/Foreign/Unique/Exclusion constraints are not supported >>> on partitioned table. In that case respective controls should be disabled >>> for partitioned table. >>> - We will have to check which constraints are applicable on >>> partitions(of partitioned table) still some R&D require. Can someone help >>> me here. >>> - For regular tables in Foreign Key constraints tab References >>> control should not list partition tables. >>> - Check constraints : cannot add NO INHERIT constraint to >>> partitioned table, so that control is disabled for partition table. >>> 3. *Advanced Tab*: >>> - Relation works with partition table theirs is an error if "With >>> indexes?" is set to Yes, so we need to disabled that for partition table. >>> - "Has OIDs?" and "Unlogged?" works but not sure about "Fill >>> factor" and "Of type". >>> 4. *Parameter Tab*: >>> - Gives error (unrecognized parameter "autovacuum_enabled") for >>> all parameters of Table Tab and working fine for "Toast Table" >>> it's working. >>> >>> Can you detail what operations someone would likely want (or need) to >> perform on the parent/child tables; e.g. >> >> Parent: >> >> - View stats >> - View data >> - Truncate >> - View/create columns >> - Bulk-create indexes >> - Bulk-create foreign keys >> >> Child: >> >> - View stats >> - View data >> - Truncate >> - Create indexes >> - Create foreign keys >> >> >> >>> Apart from above we will have to do following: >>> >>> - Required switch control to specify whether it is a regular table >>> or partitioned table. I have added it on General tab. Please refer >>> Partition_Switch.png >>> - Will have to add new tab "Partition" which will have one select2 >>> control to define its Range partition or List partition. Refer >>> Partition_Tab.png >>> >>> "Partitions"? >> >>> >>> - Design following controls in *Partition* tab: >>> - How to add columns in case of Range/List partition? LIST >>> partition key supports only one column. For RANGE user can specify multiple >>> columns. >>> - How to specify expression, COLLATE while adding columns >>> for partition. >>> - We need subnode control so that user will add number of >>> partition with there values of the main table. Need lot of R&D for this. >>> - We will have to provide "Create partition", "Attach Partition" and >>> "Detech partition" context menu options on Partitions collection >>> node. >>> >>> OK. >> >> Thanks! This is a complex one :-( >> >> >>> Let me know if I forgot something to add that we may need to >>> handle/implement. >>> >>> On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt >>> wrote: >>> >>>> The issues we consistently face: >>>>>> >>>>>> - The huge (often thousands sometimes tens of thousands) number >>>>>> of partitions makes rendering all of the partitions painfully slow and >>>>>> frequently not useful. >>>>>> >>>>>> Perhaps, though I doubt that number would be common in Postgres. The >>>>> problem though, is that there are both stats and sub-objects (indexes and >>>>> triggers for example) that are part of the child partitions, not the parent >>>>> - and they may differ from partition to partition. >>>>> >>>> >>>> Certainly there differences in Postgres and Greenplum and this might >>>> very well be one of those places. >>>> >>>> >>>>> I don't see that we have any choice but to display them so users can >>>>> work with them. >>>>> >>>> >>>> We don't want to hide them, I do think we want to make accessing them a >>>> useful experience. If we rephrase this statement as "How might we display >>>> partitioned tables so that users are able to work with and modify the >>>> pieces they need?", this opens us up to different opportunities in how we >>>> display them. >>>> >>>> Even with a simple case of 90 days of data partitioned by day, a drop >>>> down showing 90 tables that are all mostly the same is a little >>>> overwhelming. >>>> >>>> >>>>> >>>>>> - When end users are interested in looking at their partitions >>>>>> they frequently don't want all of them displayed mindlessly >>>>>> - They are looking at a subset of partitions >>>>>> - Partitions are typically grouped around their inheritance >>>>>> properties. >>>>>> >>>>>> How might you propose grouping them (based on the way they work in >>>>> Postgres)? >>>>> >>>> >>>> Honestly I'm not sure. We didn't really start thinking about this until >>>> the other day so we are starting to look into the pains that Greenplum >>>> customers have. Sharing that pain we discover back to the pgAdmin community >>>> and seeing if it makes sense from a Postgres perspective. After that I >>>> need to dive into the Postgres implementation. >>>> >>>> -- Rob >>>> >>> >>> >>> >>> -- >>> *Akshay Joshi* >>> *Principal Software Engineer * >>> >>> >>> >>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>> 976-788-8246 <+91%2097678%2088246>* >>> >>> >>> -- >>> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgadmin-hackers >>> >>> >> >> >> -- >> 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-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 > <+91%2097678%2088246>* > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --94eb2c05e9a23fa0ea054e9d785b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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.

On Wed, May 3, 2017 at 1:00 PM, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi Dave

As per my un= derstanding below operations required

Parent:
=
  • View table data.
  • View stats.
  • Create regular/partiti= oned table
  • Create N number of partitions.
  • Drop/ Drop cascad= e, Truncate.
  • Attach/Detach Partitions.
  • Not able to create c= onstraints excluding check constraint.
Child:
  • Vi= ew Table Data.
  • View stats.
  • View partition scheme in SQL pan= e
  • Create primary/foreign/.. key constraint.
  • Drop/ Drop = cascade, Truncate

On Tue,= May 2, 2017 at 8:25 PM, Dave Page <dpage@pgadmin.org> wrote= :
Hi

On Tue, May 2, 2017 at 2:46= PM, Akshay Joshi <akshay.joshi@enterprisedb.com><= /span> wrote:
Hi All=C2=A0

To implement Declarative Partitioning in exist= ing Table dialog below=C2=A0changes should be=C2=A0implem= ented:
  1. I= con:=C2=A0As we have=C2=A0separate icon for view and=C2=A0materialised view, we should have for partition table. I didn&#= 39;t find any in font awesome.
They= are really different object types though (even having their own collection= s), which isn't the case here. I'm not against having a slightly mo= dified icon, but I don't think it's necessary. Note that the object= icons come from pgAdmin III, and were custom designed for us. They aren= 9;t in font awesome etc. We'd need to tweak one of the existing ones.
  1. Inheritance:
      2. <= li>A partition cannot have any parents other than the partitioned table i= t is a partition of, nor can a regular table inherit from a partitioned tab= le making the latter its parent. That means partitioned tables and partitio= ns do not participate in inheritance with regular tables.
      =
    • When= user creates regular table then Inherited from table(s) control should not= =C2=A0display partitioned table.
  2. Constraints:
    • Primary/Foreign/Unique/Exclusion constraints are not supported on par= titioned table. In that case respective controls should be disabled for par= titioned table.
    • We will have to check which constraints are applicable on= partitions(of partitioned table) still some R&D=C2=A0require. Can some= one help me here.
    • For regular tables in Foreign Key constraints tab Refer= ences control should not=C2=A0list partition tables.
    • Check constraints := =C2=A0cannot add NO INHERIT constraint to partitioned table, so= that control is disabled for partition table.
  3. <= span style=3D"font-family:arial,helvetica,sans-serif;color:rgb(51,51,51);fo= nt-variant-ligatures:normal;white-space:pre-wrap">Advanced Tab:
    • Relation = works with partition table theirs is an error if "With indexes?" = is set to Yes, so we need to disabled that for partition table.
      • =
    • "Has OIDs?" and "Unlogged?" works but= not sure about "Fill factor" and "Of type".
  4. Parameter Tab:
    • Gives error (unrecognized par= ameter "autovacuum_enabled") for all parameters= =C2=A0=C2=A0of Table Tab and working fine for "To= ast Table" it's working.=C2=A0
Can you detail what operations someone would likely want (= or need) to perform on the parent/child tables; e.g.

Parent:

- View stats
- View data
- Truncate
- View/create columns
- Bulk-create in= dexes
- Bulk-create foreign keys

Child:<= /div>

- View stats
- View data
- Tru= ncate
- Create indexes
- Create foreign keys

=C2=A0
Apart from above we will = have to do following:
  • Required switch control to specify whether it is a regular table = or partitioned table. I have added it on General tab. Please refer Partitio= n_Switch.png
  • Will have to add new tab "Partition" which will ha= ve one select2 control to define its Range partition or List=C2=A0partition= . Refer=C2=A0Partition_Tab.png
"Partitions"?=C2=A0
  • Design following cont= rols in=C2=A0Partition=C2=A0tab:
    • How to add columns in case of Range/List partition?=C2= =A0LIST partition key= supports only one column. For RANGE user can specify multiple columns.
    • How to speci= fy expression, COLLATE while adding columns for=C2=A0partition.
      • =
    • We need=C2=A0sub= node control so that user will add number of partition with there va= lues of the main table. Need lot of R&D for this.
  • <= font face=3D"arial, helvetica, sans-serif">We will have to=C2=A0provide &qu= ot;Create partition", "Attach Partition" and "Detech=C2=A0partition" context menu options on Partitions co= llection node.=C2=A0
O= K.

Thanks! This is a complex one :-(
=C2= =A0
Let me know if I forgot something to a= dd that we may need to handle/implement.

On Thu, Apr 27, 2017 at 9:14 PM, Robert E= ckhardt <reckhardt@pivotal.io> wrote:
The issues we consistently face:
  • The huge (oft= en thousands sometimes tens of thousands) number of partitions makes render= ing all of the partitions painfully slow and frequently not useful.
Perhaps, though I doubt = that number would be common in Postgres. The problem though, is that there = are both stats and sub-objects (indexes and triggers for example) that are = part of the child partitions, not the parent - and they may differ from par= tition to partition.

Certainly there differences in Postgres and Greenplum and this mi= ght very well be one of those places.=C2=A0
=C2=A0
I don't see that we have any choice but to= display them so users can work with them.

We don't want to hide them, I do thi= nk we want to make accessing them a useful experience. If we rephrase this = statement as "How might we display partitioned tables so that users ar= e able to work with and modify the pieces they need?", this opens us u= p to different opportunities in how we display them.

Even with a simple case of 90 days of data partitioned by day, a drop do= wn showing 90 tables that are all mostly the same is a little overwhelming.= =C2=A0
=C2=A0
<= span>
  • When end users are interested = in looking at their partitions they frequently don't want all of them d= isplayed mindlessly=C2=A0
    • They are looking at a subset of parti= tions
    • Partitions are typically grouped around their inheritance pro= perties.=C2=A0
How might you propose grouping them (based on the way they work in Postg= res)?=C2=A0

<= div>Honestly I'm not sure. We didn't really start thinking about th= is until the other day so we are starting to look into the pains that Green= plum customers have. Sharing that pain we discover back to the pgAdmin comm= unity and seeing if it makes sense from a Postgres perspective.=C2=A0 After= that I need to dive into the Postgres implementation.=C2=A0

=
-- Rob



-= -
Akshay Josh= i
Principal Software Engineer=C2=A0


 --
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-ha= ckers




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


<= /div>



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

EnterpriseDB UK: http://www.enterprised= b.com
The Enterprise PostgreSQL Company
--94eb2c05e9a23fa0ea054e9d785b--