Bruce Momjian <br...@momjian.us> writes:
> On Tue, Nov 29, 2022 at 06:13:56PM -0500, Tom Lane wrote:
>> ... 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.
> I am not sure why they would need to exclusive lock anything more than
> the unique index entry they are adding, just like UPDATE does.
Assuming that you are inserting into index X, and you've checked
index Y to find that it has no conflicts, what prevents another
backend from inserting a conflict into index Y just after you look?
AIUI the idea is to prevent that by continuing to hold an exclusive
lock on the whole index Y until you've completed the insertion.
Perhaps there's a better way to do that, but it's not what was
described.
I actually think that that problem should be soluble with a
slightly different approach. The thing that feels insoluble
is that you can't do this without acquiring sufficient locks
to prevent addition of new partitions while the insertion is
in progress. That will be expensive in itself, and it will
turn ATTACH PARTITION into a performance disaster.
regards, tom lane
