Greg Stark <st...@mit.edu> writes: > If I understand correctly you're going to insert into the local index > for the partition using the normal btree uniqueness implementation. > Then while holding an exclusive lock on the index do lookups on every > partition for the new key. Effectively serializing inserts to the > table?
... not to mention creating a high probability of deadlocks between concurrent insertions to different partitions. If they each ex-lock their own partition's index before starting to look into other partitions' indexes, it seems like a certainty that such cases would fail. The rule of thumb about locking multiple objects is that all comers had better do it in the same order, and this isn't doing that. That specific issue could perhaps be fixed by having everybody examine all the indexes in the same order, inserting when you come to your own partition's index and otherwise just checking for conflicts. But that still means serializing insertions across all the partitions. And the fact that you need to lock all the partitions, or even just know what they all are, is going to play hob with a lot of assumptions we've made about different partitions being independent, and about what locks are needed for operations like ALTER TABLE ATTACH PARTITION. (I wonder BTW what the game plan is for attaching a partition to a partitioned table having a global index. Won't that mean having to check every row in the new partition against every one of the existing partitions? So much for ATTACH being fast.) I still think this is a dead end that will never get committed. If folks want to put time into perhaps finding an ingenious way around these problems, okay; but they'd better realize that there's a high probability of failure, or at least coming out with something nobody will want to use. regards, tom lane