Re: Adding support for Default partition in partitioning

>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.

>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(at)omniti(dot)com> wrote:

> On Thu, Mar 2, 2017 at 9:40 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> On Wed, Mar 1, 2017 at 6:29 AM, Rahila Syed <rahilasyed90(at)gmail(dot)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.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)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
>