Re: Deadlock risk while inserting directly into partition?

On Thu, Jun 24, 2021 at 1:45 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > ... What I mean is that as we add
> > more and more fixes to improve performance of partitioning, that there
> > comes a point where the ability to directly reference partitions is a
> > hindrance rather than something that's useful. Right now that
> > hindrance is the fact that we must lock every single partition in the
> > plan. We only need to do that in case some other backend is doing
> > something that bypasses taking a lock on the parent partitioned table.
>
> TBH, I buy no part of that line of reasoning. 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.
>
> 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. I fail to see how that
> makes anybody's life better.

I agree with David's points above.

Maybe I've missed something but I don't see any benefit in being able
to reference individual partitions by name, as a feature. Maybe as a
temporary performance trick, but app devs just want partitioning to be
invisible to them at the application level. It's a modularity
violation to be able to access parts of a table, just like it would be
if we allowed people to reference individual smgr files.

If that requires that we add a new non-default option, no problem.
Most people will want to use that option, AFAICS.

--
Simon Riggs http://www.EnterpriseDB.com/