public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Tue, 2 May 2017 19:16:45 +0530
Message-ID: <CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@mail.gmail.com> (raw)
In-Reply-To: <CAAtBm9UHyp+bkxcyYL+1qb9knps_cdh6N0tvwMy5uY-eVjWcPg@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
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.
2. *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.
3. *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.
4. *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".
5. *Parameter Tab*:
- Gives error (unrecognized parameter "autovacuum_enabled") for all
parameters of Table Tab and working fine for "Toast Table" it's
working.
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
- 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.
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-9517Mobile: +91 976-788-8246*
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[image/png] Partition_Switch.png (98.2K, 3-Partition_Switch.png)
download | view image
[image/png] Partition_Tab.png (68.5K, 4-Partition_Tab.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]
Subject: Re: Declarative partitioning in pgAdmin4
In-Reply-To: <CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@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