public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shirley Wang <[email protected]>
To: Dave Page <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Tue, 02 May 2017 22:01:29 +0000
Message-ID: <CAPG3WN6hnKW+61OoBON68qZbHSo1p1_Mbr9Oq0fPXGh-_vDqHQ@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxowpGBLT1q2DzL9VpRG5So8zYssP9SAdd=3Mc6dk8_-p7A@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
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.
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
>
Attachments:
[image/png] tablepartition-1.png (33.2K, 3-tablepartition-1.png)
download | view image
[image/png] tablepartition-2.png (33.4K, 4-tablepartition-2.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: <CAPG3WN6hnKW+61OoBON68qZbHSo1p1_Mbr9Oq0fPXGh-_vDqHQ@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