Re: Deadlock risk while inserting directly into partition?

On Mon, Jun 28, 2021 at 9:50 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> On Thu, Jun 24, 2021 at 7:27 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > On Wed, 23 Jun 2021 at 21:07, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > I noticed that while inserting directly into a partition table we
> > > compute the PartitionCheckExpr by traversing all the parent partitions
> > > via ExecPartitionCheck()->RelationGetPartitionQual()->generate_partition_qual().
> > > We take AccessShareLock on parent tables while generating qual.
> > >
> > > Now, on the other hand, while dropping constraint on a partitioned
> > > table, we take the lock from parent to all the child tables.
> > >
> > > I think taking locks in opposite directions can lead to deadlock in
> > > these operations.
> >
> > I wonder if it's possible to do any better here? Surely when
> > traversing from child to parent we must lock the child before checking
> > what the parent relation is.
>
> I remember there was a discussion where I proposed to document the
> deadlock hazard that exists when performing DML directly on
> partitions.
>

+1. I think it is better if we can also write in code comments or
README about this. How about adding something to README/code about
locking of partitions for different operations? Unless I am missing
it, I think some of this information is there in bits and pieces but
it would be great if we can have it consolidated at someplace.

> The proposal didn't get enough attention, perhaps because
> it was in the middle of a long reply about other concerns:
>
> https://www.postgresql.org/message-id/16db1458-67cf-4add-736e-31b053115e8e%40lab.ntt.co.jp
>
> Maybe a good idea to add a line or 2 in 5.11. Table Partitioning?
>

Sounds reasonable, but I think it would be better if can mention the
scenarios/cases where there is a possibility of deadlocks.

--
With Regards,
Amit Kapila.