On 2017/04/06 0:19, Robert Haas wrote: > On Wed, Apr 5, 2017 at 5:57 AM, Rahila Syed <rahilasye...@gmail.com> wrote: >>> 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. > > Yes, I think it would be completely crazy to try to migrate the data > in the background: > > - The migration might never complete because of a UNIQUE or CHECK > constraint on the partition to which rows are being migrated. > > - Even if the migration eventually succeeded, such a design abandons > all hope of making INSERT .. ON CONFLICT DO NOTHING work sensibly > while the migration is in progress, unless the new partition has no > UNIQUE constraints. > > - Partition-wise join and partition-wise aggregate would need to have > special case handling for the case of an unfinished migration, as > would any user code that accesses partitions directly. > > - More generally, I think users expect that when a DDL command > finishes execution, it's done all of the work that there is to do (or > at the very least, that any remaining work has no user-visible > consequences, which would not be the case here).
OK, I realize the background migration was a poorly thought out idea. And a *first* version that will handle the row-movement should be doing that as part of the same command anyway. > IMV, the question of whether we have efficient ways to move data > around between partitions is somewhat separate from the question of > whether partitions can be defined in a certain way in the first place. > The problems that Keith refers to upthread already exist for > subpartitioning; you've got to detach the old partition, create a new > one, and then reinsert the data. And for partitioning an > unpartitioned table: create a replacement table, insert all the data, > substitute it for the original table. The fact that we have these > limitation is not good, but we're not going to rip out partitioning > entirely because we don't have clever ways of migrating the data in > those cases, and the proposed behavior here is not any worse. > > Also, waiting for those problems to get fixed might be waiting for > Godot. I'm not really all that sanguine about our chances of coming > up with a really nice way of handling these cases. In a designed > based on table inheritance, you can leave it murky where certain data > is supposed to end up and migrate it on-line and you might get away > with that, but a major point of having declarative partitioning at all > is to remove that sort of murkiness. It's probably not that hard to > come up with a command that locks the parent and moves data around via > full table scans, but I'm not sure how far that really gets us; you > could do the same thing easily enough with a sequence of commands > generated via a script. And being able to do this in a general way > without a full table lock looks pretty hard - it doesn't seem > fundamentally different from trying to perform a table-rewriting > operation like CLUSTER without a full table lock, which we also don't > support. The executor is not built to cope with any aspect of the > table definition shifting under it, and that includes the set of child > tables with are partitions of the table mentioned in the query. Maybe > the executor can be taught to survive such definitional changes at > least in limited cases, but that's a much different project than > allowing default partitions. Agreed. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers