Re: Adding support for Default partition in partitioning

Hello Amit,

>Could you briefly elaborate why you think the lack global index support
>would be a problem in this regard?
I think following can happen if we allow rows satisfying the new partition
to lie around in the
default partition until background process moves it.
Consider a scenario where partition key is a primary key and the data in
the default partition is
not yet moved into the newly added partition. If now, new data is added
into the new partition
which also exists(same key) in default partition there will be data
duplication. If now
we scan the partitioned table for that key(from both the default and new
partition as we
have not moved the rows) it will fetch the both rows.
Unless we have global indexes for partitioned tables, there is chance of
data duplication between
child table added after default partition and the default partition.

>Scanning it and moving rows to the newly added partition while holding an
>AccessExclusiveLock on the parent will block any and all of the concurrent
>activity on it until the row-movement is finished.
Can you explain why this will require AccessExclusiveLock on parent and
not just the default partition and newly added partition?

Thank you,
Rahila Syed

On Wed, Apr 5, 2017 at 1:22 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> On 2017/04/05 14:41, Rushabh Lathia wrote:
> > I agree about the future plan about the row movement, how that is I am
> > not quite sure at this stage.
> >
> > I was thinking that CREATE new partition is the DDL command, so even
> > if row-movement works with holding the lock on the new partition table,
> > that should be fine. I am not quire sure, why row movement should be
> > happen in the back-ground process.
>
> I think to improve the availability of access to the partitioned table.
>
> Consider that the default partition may have gotten pretty large.
> Scanning it and moving rows to the newly added partition while holding an
> AccessExclusiveLock on the parent will block any and all of the concurrent
> activity on it until the row-movement is finished. One may be prepared to
> pay this cost, for which there should definitely be an option to perform
> the row-movement in the same transaction (also possibly the default
> behavior).
>
> Thanks,
> Amit
>
>
>