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...@lab.ntt.co.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 > > >