Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d5ZDY-0006YN-21 for pgadmin-hackers@arkaria.postgresql.org; Tue, 02 May 2017 14:55:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d5ZDX-0002aY-Im for pgadmin-hackers@arkaria.postgresql.org; Tue, 02 May 2017 14:55:55 +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 1d5ZDW-0002aP-GN for pgadmin-hackers@postgresql.org; Tue, 02 May 2017 14:55:54 +0000 Received: from mail-io0-x236.google.com ([2607:f8b0:4001:c06::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d5ZDS-00065o-Nm for pgadmin-hackers@postgresql.org; Tue, 02 May 2017 14:55:53 +0000 Received: by mail-io0-x236.google.com with SMTP id a103so159122222ioj.1 for ; Tue, 02 May 2017 07:55:50 -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=Tm0eqYwq8wLo+9jedilMczElT2GUBaVYbsgRe0dU2wc=; b=Wd7i1a/2g2Wyx8Fj1oR0b58WMSpip0T6Wht0BUBNimQPZr1YyxZZbk59XaA12FwPSD EtINXEG/K+ty1/R1+SaPNNmkVcGb4IOadp4QOEqFzJpOZ76Xi9jeE2R9KhzViSrD7692 m8//4fNFiGeMUyv2koR2HI37Fp2m7QND4w5RgedkcwqGqaysczV05UI6pU45/P9rFO+o /hk+R0XLHU0aTtbxXF/pDH9x6aCD3HymnGSa9dqw0ehFAGpS4qyJqbnWTc+fn3rHtv4c 56otWIitf8qzmzFGShx2peoWdjRcJDSFbsmCIy+Jj8N/6d+AuFpno3yn4Fum9SaxgEp6 9K0g== 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=Tm0eqYwq8wLo+9jedilMczElT2GUBaVYbsgRe0dU2wc=; b=odmRpwhMmH3g/BEe+Ies2yd1dtkJzSfJHG4n20kjw9z/wncq+wDKXIyUS3cXEgKIz4 CdyB1UlIFcCgjtcP8aR9yLz+BUSUta1Jxybo33tznIN6unC4vZByY+pImQ6R3kdwu6h5 vuNA7IYyuPGKcqdMCwd0CpvTNN1PY04pvoco3KlF2LjMk1XgiPbZF2AG0qHnYHeLaR1/ TmJGFkIpFxzaX581t/uj1AU0QUku7N6HYf7JAU0A0gsPtIsQCke0taiEXuPowK60HRs0 Tci+B7ToYqZ6aGps3rwDDmGEyawTHFfBdYg1X5jxmLrBmZj+0hbBRCkWVnJuYXNxEGaQ PqqQ== X-Gm-Message-State: AN3rC/6o6HEiVUq/9aIxhjEtuZYpRW/HVUzrM0JD7TgTl2wxuINNY0yh beLJl04MUsF8/gyzKJEWuvifm7WzmQ== X-Received: by 10.107.13.16 with SMTP id 16mr28010967ion.144.1493736949212; Tue, 02 May 2017 07:55:49 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.167 with HTTP; Tue, 2 May 2017 07:55:48 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Tue, 2 May 2017 15:55:48 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary=001a1140a3583f17fc054e8bbc57 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 --001a1140a3583f17fc054e8bbc57 Content-Type: text/plain; charset=UTF-8 Hi On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi 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 --001a1140a3583f17fc054e8bbc57 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi

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

To implement Dec= larative Partitioning in existing Table dialog below=C2=A0changes should be=C2=A0implemented:<= /div>
  1. Icon:=C2=A0= As we have=C2=A0separate icon for view and=C2=A0materialised view, we should have for partitio= n 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 s= lightly 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. Th= ey aren't in font awesome etc. We'd need to tweak one of the existi= ng ones.
  1. <= font face=3D"arial, helvetica, sans-serif">Inheritance:
    2. <= ul>
  2. A partition cannot have any parents other than the partitioned tab= le 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 part= itions do not participate in inheritance with regular tables.=
  3. = When user creates regular table then Inherited from table(s) control should= not=C2=A0display partitioned table.
  4. 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 applicabl= e on partitions(of partitioned table) still some R&D=C2=A0require. Can = someone help me here.
    • For regular tables in Foreign Key constraints tab R= eferences control should not=C2=A0list partition tables.
    • Check constraint= s :=C2=A0cannot add NO INHERIT constraint to partitioned table,= so that control is disabled for partition table.
    Advanced Tab:
    • Relati= on works with partition table theirs is an error if "With indexes?&quo= t; is set to Yes, so we need to disabled that for partition table.
    • "Has OIDs?" and "Unlogged?" works but not sure ab= out "Fill factor" and "Of type".
  5. <= b>Parameter Tab:
    • unrecognized parameter "= ;autovacuum_enabled")= for all parameters=C2=A0=C2=A0of Table Tab and workin= g fine for "Toast Table" it's working.=C2=A0
    =
Can you detail what operations someone would l= ikely want (or need) to perform on the parent/child tables; e.g.
=
Parent:

- View stats
- Vi= ew data
- Truncate
- View/create columns
- Bu= lk-create indexes
- Bulk-create foreign keys

=
Child:

- View stats
- View data
- Truncate
- Create indexes
- Create foreign key= s

=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 Pa= rtition_Switch.png<= /li>
  • Will have to add new ta= b "Partition" which will have one select2 control to define its R= ange partition or List=C2=A0partition. Refer=C2=A0Partition_Tab.png
"Partitions"?=C2=A0
  • Design following controls in=C2=A0Part= ition=C2=A0tab:
    • How to add columns in case of Range/List partition?=C2=A0LIST partition key supports only one c= olumn. For RANGE user can specify multiple columns.
    • <= font face=3D"arial, helvetica, sans-serif">How to specify expression, COLLA= TE while adding columns for=C2=A0partition.
    • We need=C2=A0subnode control so that user will add number of partition wi= th there values of the main table. Need lot of R&D for this.
  • We will have to=C2=A0= provide "Create partition", "Attach Partition" and &quo= t;Detech=C2=A0partition&qu= ot; context menu options on Partitions collection node.=C2=A0
OK.

Thanks! This = is a complex one :-(
=C2=A0
<= div dir=3D"ltr">
Let me kno= w if I forgot something to add that we may need to handle/implement.=

On Thu, Apr 27, 2017 at 9:14 PM, Robert Eckhardt <rec= khardt@pivotal.io> wrote:
<= div dir=3D"ltr">
The iss= ues we consistently face:
  • The huge (often thousands s= ometimes tens of thousands) number of partitions makes rendering all of the= partitions painfully slow and frequently not useful.
=
Perhaps, though I doubt that number wo= uld 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 ch= ild partitions, not the parent - and they may differ from partition to part= ition.

Cert= ainly there differences in Postgres and Greenplum and this might 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.
<= br>
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 &= quot;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.=C2=A0
=C2=A0
  • When end users are interested in looking at t= heir partitions they frequently don't want all of them displayed mindle= ssly=C2=A0
    • They are looking at a subset of partitions
    • P= artitions are typically grouped around their inheritance properties.=C2=A0<= /li>
How might yo= u propose grouping them (based on the way they work in Postgres)?=C2=A0
=

Honestly I&= #39;m not sure. We didn't really start thinking about this until the ot= her 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 seein= g if it makes sense from a Postgres perspective.=C2=A0 After that I need to= dive into the Postgres implementation.=C2=A0

-- R= ob



--
Akshay Joshi
Principal Software Engin= eer=C2=A0

<= b>

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




--
--001a1140a3583f17fc054e8bbc57--