Re: Deadlock risk while inserting directly into partition?

On Thu, 24 Jun 2021 at 12:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I don't think that the
> ability to access partitions directly is a material problem here;
> I doubt that we need to lock every partition in the plan when run-time
> routing is working (surely we only need to lock the partition mapping);
> and most especially I don't see why an operation on a child table that
> doesn't lock the parent would cause a problem for queries that do not
> need to access that child. Perhaps we've got some implementation issues
> to fix, but I see no fundamental problem there.

Not quite sure I know what you mean by "lock the partition mapping".

We do unfortunately need to lock all partitions in the plan before
run-time pruning completes. For example, if someone drops an index
from one of the partitions that's used in the plan, then we must take
the lock before execution so that we properly invalidate the plan and
get another one. I'm not sure I see how that could be done during
execution, We might have already started returning rows to the client
by that time.

> It is true that this design can lead to deadlocks between operations that
> start from the parent vs ones that start from the child and then discover
> that they need to lock the parent. But the latter should be darn rare.
> In any case, your solution seems to amount to prohibiting not only the
> latter class of operations altogether, but *also* prohibiting operations
> on the child that don't need to lock the parent.

Again, I'm not saying we need to go and make partitioning work this
way. I'm saying that the problem wouldn't exist if it did work that
way and that there appears to be no solution to fix it without making
it work that way.

> I fail to see how that
> makes anybody's life better.

Well, if you ignore the perfectly valid use case that I mentioned
then, yeah. Or do you not think that doing a single-row lookup on a
partitioned table with a prepared query is a case worth worrying
about?

I grabbed a profile from a generic plan being executed on a
partitioned table with 100 partitions. It's completely dominated by
lock management and looks like this:

22.42% postgres postgres [.] hash_search_with_hash_value
9.06% postgres postgres [.] hash_bytes
4.14% postgres postgres [.] LockAcquireExtended
3.90% postgres postgres [.] AllocSetAlloc
3.84% postgres postgres [.] hash_seq_search
3.77% postgres postgres [.] LockReleaseAll

I don't think 100 partitions is excessive.

David