Greg Stark <stark@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