Re: [HACKERS] Adding support for Default partition in partitioning - Mailing list pgsql-hackers
| From | Rahila Syed |
|---|---|
| Subject | Re: [HACKERS] Adding support for Default partition in partitioning |
| Date | |
| Msg-id | CAH2L28sgdRw=Zm0DWU2dV9p=p9WsdGR_h97FbcxAMkdM6uDfFA@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] Adding support for Default partition in partitioning (Keith Fiske <keith@omniti.com>) |
| List | pgsql-hackers |
>I agree that adding a new partition should not move any data out of the default. It's easy enough to set up a monitor to watch for data existing in the >default. Perhaps also adding a column to pg_partitioned_table that contains the oid of the default partition so it's easier to identify from a system >catalog perspective and make that monitoring easier.
Wont it incur overhead of scanning the default partition for matching rows each time a select happens on any matching partition?
This extra scan would be required until rows satisfying the newly added partition are left around in default partition.
Wont it incur overhead of scanning the default partition for matching rows each time a select happens on any matching partition?
This extra scan would be required until rows satisfying the newly added partition are left around in default partition.
>I don't even see a need for it to fail either and not quite sure how that would even work? If they can't add a necessary child due to data being in the >default, how can they ever get it out? Just leave it to the user to keep an eye on the default and fix it as necessary.
This patch intends to provide a way to insert data that does not satisfy any of the existing partitions. For this patch, we can disallow adding a new partition when a default partition with conflicting rows exist. There can be many solutions to the problem of adding a new partition. One is to move the relevant tuples from default to the new partition or like you suggest keep monitoring the default partition until user moves the rows out of the default.
Thank you,
Rahila Syed
On Tue, Mar 7, 2017 at 10:00 PM, Keith Fiske <keith@omniti.com> wrote:
On Thu, Mar 2, 2017 at 9:40 PM, Robert Haas <robertmhaas@gmail.com> wrote:On Wed, Mar 1, 2017 at 6:29 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
> 3. Handling adding a new partition to a partitioned table
> with default partition.
> This will require moving tuples from existing default partition to
> newly created partition if they satisfy its partition bound.
Considering that this patch was submitted at the last minute and isn't
even complete, I can't see this getting into v10. But that doesn't
mean we can't talk about it. I'm curious to hear other opinions on
whether we should have this feature. On the point mentioned above, I
don't think adding a partition should move tuples, necessarily; seems
like it would be good enough - maybe better - for it to fail if there
are any that would need to be moved.--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers I'm all for this feature and had suggested it back in the original thread to add partitioning to 10. I agree that adding a new partition should not move any data out of the default. It's easy enough to set up a monitor to watch for data existing in the default. Perhaps also adding a column to pg_partitioned_table that contains the oid of the default partition so it's easier to identify from a system catalog perspective and make that monitoring easier. I don't even see a need for it to fail either and not quite sure how that would even work? If they can't add a necessary child due to data being in the default, how can they ever get it out? Just leave it to the user to keep an eye on the default and fix it as necessary. This is what I do in pg_partman.
--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com
pgsql-hackers by date: