Re: Deadlock risk while inserting directly into partition?

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. 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?

--
Amit Langote
EDB: http://www.enterprisedb.com