Re: Deadlock risk while inserting directly into partition? - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Deadlock risk while inserting directly into partition?
Date
Msg-id CANbhV-EoGk_R5_saWqvfspOvMixCDiA9N=ePbYKuhOsgsBSYbw@mail.gmail.com
Whole thread Raw
In response to Re: Deadlock risk while inserting directly into partition?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Thu, Jun 24, 2021 at 1:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.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/



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Using each rel as both outer and inner for JOIN_ANTI
Next
From: John Naylor
Date:
Subject: Re: a path towards replacing GEQO with something better