public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Shirley Wang <[email protected]>
Cc: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Wed, 3 May 2017 10:13:51 +0530
Message-ID: <CANxoLDcR9RQvNsWVPe16nf9p06Chvkk_pAUdgJXuiV9JCPznzQ@mail.gmail.com> (raw)
In-Reply-To: <CAPG3WN6hnKW+61OoBON68qZbHSo1p1_Mbr9Oq0fPXGh-_vDqHQ@mail.gmail.com>
References: <CANxoLDcZND0pjXtrDKRip2xjddzjWiMgY2AMmrqqFE_Yu4+tHw@mail.gmail.com>
<CA+OCxowUuaNRX9jHmEVFpqT7JCbjn6vaxw+JJ6yrvVp69FZscg@mail.gmail.com>
<CAPG3WN5NY-Xsa_+6HUQ3NMU_n7jRgJ8L6rjHfyzSLSHS=zZC0Q@mail.gmail.com>
<CA+OCxoy1v+mq2P4ZL2v7mmyHmjwQmL=v8RR8CSRra_SV96nJDQ@mail.gmail.com>
<CANxoLDeBGRmq_kUUNNySXimzJO2Ebj0aQBdjNM+0JvP3_Yr9Dw@mail.gmail.com>
<CAAtBm9Ve2FX4_jY9tv11UqK2BhNoLn118aeT4y=TieSAovL+AA@mail.gmail.com>
<CA+OCxozkEdTmVUtJEBdHT97EbiUK_+cwW+rv21tuHyxSnN3HOg@mail.gmail.com>
<CAAtBm9UHyp+bkxcyYL+1qb9knps_cdh6N0tvwMy5uY-eVjWcPg@mail.gmail.com>
<CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@mail.gmail.com>
<CA+OCxowpGBLT1q2DzL9VpRG5So8zYssP9SAdd=3Mc6dk8_-p7A@mail.gmail.com>
<CAPG3WN6hnKW+61OoBON68qZbHSo1p1_Mbr9Oq0fPXGh-_vDqHQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi Shirley
On Wed, May 3, 2017 at 3:31 AM, Shirley Wang <[email protected]> wrote:
>
>
> On Tue, May 2, 2017 at 10:56 AM Dave Page <[email protected]> wrote:
>
>> Hi
>>
>> On Tue, May 2, 2017 at 2:46 PM, Akshay Joshi <
>> [email protected]> 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"?
>>
>
> Is there a reason why 'Partition' needs to open in a new tab? If there's
> only one field, we should include it on the same page since the tabs don't
> dictate necessary steps in a sequential order. Users will be able to find
> what they need without navigating to another part of the dialog.
>
There are lots of controls yet to design like column(s), number of
partitions, partition schemes will be part of "Partitions" Tab. We will
need that.
>
> Example:
> [image: tablepartition-1.png]
> [image: tablepartition-2.png]
>
>
>>> - 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 <[email protected]>
>>> 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 ([email protected])
>>> 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*
Attachments:
[image/png] tablepartition-2.png (33.4K, 3-tablepartition-2.png)
download | view image
[image/png] tablepartition-1.png (33.2K, 4-tablepartition-1.png)
download | view image
view thread (77+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Declarative partitioning in pgAdmin4
In-Reply-To: <CANxoLDcR9RQvNsWVPe16nf9p06Chvkk_pAUdgJXuiV9JCPznzQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox