On Thu, 24 Jun 2021 at 12:45, Tom Lane <t...@sss.pgh.pa.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
