Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d5sxQ-0001hH-Sd for pgadmin-hackers@arkaria.postgresql.org; Wed, 03 May 2017 12:00:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d5sxP-00023X-UQ for pgadmin-hackers@arkaria.postgresql.org; Wed, 03 May 2017 12:00:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d5sx9-0001eP-KU for pgadmin-hackers@postgresql.org; Wed, 03 May 2017 12:00:19 +0000 Received: from mail-pg0-x22b.google.com ([2607:f8b0:400e:c05::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d5sx4-0006eB-3X for pgadmin-hackers@postgresql.org; Wed, 03 May 2017 12:00:18 +0000 Received: by mail-pg0-x22b.google.com with SMTP id o3so71993269pgn.2 for ; Wed, 03 May 2017 05:00:13 -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=ro1sOGbOf8Yl6VseWdAnx2WPU6UNTzGNGg2RN7kRWPI=; b=hY2xoiZjsJIiYUjkBaBxZt05pQO5pkG8wNZhtQP5EH/zKjOiy3ujNnxwWDEFZSM94x wimXllouutbSiJjGRGMeAYEAQbZ/DxL3SHPxgzC1JrBMtrz9SM6IkXIvB2xw2XTy/5Ju +aOwbM0DQ22nNSaSlandPkZzJHl9BB15fOuT6RDv4fFsk++2ZETQUdHVr4FmTZQ7/uJE 3USNU66ackr83TDSX055zNVWqaZLT+q1nZZKXgaK82HhJTfPau+HJaT6h8Q6KGDqrVmg VP4KQrH1HYpt6/BtvEBb8p/0XKkaR4hrXgZUNnOVt5R59eEbRUveIDN+y8+s/z5OETZB dukg== 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=ro1sOGbOf8Yl6VseWdAnx2WPU6UNTzGNGg2RN7kRWPI=; b=qfYwBsEJrLbR1KhBljauA5ZTnDlujkf7YF5Ckdb/W/SJpcuWDOtKc1LEkrLDhIm6aE i0sjee18cD9YbG9qDVz4aeLnM3PkGiGXR93b6F3eT3ySJRRGYDufPtRif39Qey9wACEm +XE7LUqLkEA29a3ipLSXsgMtFs75Fv2eDeMkFY8yhq0ebrtf7f0DKknD1AZ+BT6XjRS9 5chDB8WuzrfSN39hMVlI/EGdfkRKqheT0WQabKbOefCcDrXxdB4OJkpNvZG1yvlJs6d7 +nJUCtkCLfHR+eDKtcJWq75hvJQBKg9+W/YVhIPd5YfnpsDnykZ/+WvmOQPvexgF4+FM fpRw== X-Gm-Message-State: AN3rC/4yDCKS1vNZ2bruZpSD6kNtvy7gPstKwsMh7p3Kzn6FPOYslhjK 6aBxQnXAicQbao/08jJdZPPeiAw7gZEps8g= X-Received: by 10.99.116.2 with SMTP id p2mr8708733pgc.74.1493812811270; Wed, 03 May 2017 05:00:11 -0700 (PDT) MIME-Version: 1.0 Received: by 10.100.176.206 with HTTP; Wed, 3 May 2017 05:00:10 -0700 (PDT) In-Reply-To: References: From: Akshay Joshi Date: Wed, 3 May 2017 17:30:10 +0530 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary=f403045c63a2fa2b89054e9d6535 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 --f403045c63a2fa2b89054e9d6535 Content-Type: text/plain; charset=UTF-8 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-9517Mobile: +91 976-788-8246* --f403045c63a2fa2b89054e9d6535 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Dave

As per my understanding below o= perations 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 excludin= g check constraint.
Child:
  • View Table Data.
    • =
  • View stats.
  • View partition scheme in SQL pane
  • Create pr= imary/foreign/.. key constraint.
  • Drop/ Drop cascade, Truncate

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

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

To implement Dec= larative Partitioning in existing Table dialog below=C2=A0changes should be=C2= =A0implemented:
  1. Icon:=C2=A0As we have=C2=A0separate icon for view and=C2=A0material= ised view, we should have for partition table. I didn't find any= in font awesome.
They are really d= ifferent 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 f= rom pgAdmin III, and were custom designed for us. They aren't in font a= wesome etc. We'd need to tweak one of the existing ones.
  1. Inheritance:
      2. 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 u= ser 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 "U= nlogged?" works but not sure about "Fill factor" and = "Of type".
  4. Parameter Tab:
      5. Gives error (unrecognized parameter "autovacuum_enabled") for all p= arameters=C2=A0=C2=A0of Table Tab and working fine for= "Toast Table" it's working.=C2=A0
Can you detail what operations someone would like= ly 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
<= div>- Truncate
- 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. P= lease refer Partition_Switch.png
  • Will have to add new tab "Partition" which will have= one select2 control to define its Range partition or List=C2=A0partition. = Refer=C2=A0Partition_Tab.png
"Partitions"?=C2=A0
  • Design following controls in=C2=A0= Partition=C2=A0tab:
    • How to add columns in case of Range/List partition?=C2=A0LIST partition key supports onl= y one column. For RANGE user can specify multiple columns.
    • How to specify expression= , COLLATE while adding columns for=C2=A0partition.
    • We need=C2=A0subnode control so that user wi= ll add number of partition with there values of the main table. Need lot of= R&D for this.
  • We will have to=C2=A0provide "Create partition", "At= tach Partition" and "Detech=C2=A0partition" 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, Ro= bert Eckhardt <reckhardt@pivotal.io> wrote:
The issues we consistently face:
  • The h= uge (often thousands sometimes tens of thousands) number of partitions make= s 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 tha= t there are both stats and sub-objects (indexes and triggers for example) t= hat 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.=C2=A0
=C2=A0<= /div>
I don't see that we have any choic= e 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 rephra= se this statement as "How might we display partitioned tables so that = users are able to work with and modify the pieces they need?", this op= ens 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 overw= helming.=C2=A0
=C2=A0
<= div dir=3D"ltr">
=
  • When end users are inte= rested in looking at their partitions they frequently don't want all of= them displayed mindlessly=C2=A0
    • They are looking at a subset o= f partitions
    • Partitions are typically grouped around their inherita= nce properties.=C2=A0
How might you propose grouping them (based on the way they work i= n Postgres)?=C2=A0

<= /span>
Honestly I'm not sure. We didn't really start thinking a= bout this until the other day so we are starting to look into the pains tha= t Greenplum customers have. Sharing that pain we discover back to the pgAdm= in community 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 Joshi
Principal So= ftware Engineer=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




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

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


--
Akshay Joshi


<= b>Phone: +91 20-3058-9517
Mobile: +91 976-788= -8246
--f403045c63a2fa2b89054e9d6535--