Re: tablecmds: Deadlock caused by Attach Partition

> On Oct 28, 2026, at 09:29, shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> Hi Chao,
>
> I looked at the other callers of generate_partition_qual() and race
> each one against a recursive ALTER TABLE on the root, like Surya’s test.
>
> pg_get_partition_constraintdef() has the same deadlock. It locks a
> partition and then its ancestors through generate_partition_qual().
> It deadlocks with a recursive ALTER TABLE on the root, psql \d+ uses it.
>
> I think it can be fixed the same way. Call lock_partition_ancestors()
> in get_partition_qual_relid() before it opens the relation.
>
> It may also help to add a comment above generate_partition_qual() so
> the next caller locks the ancestors first.
>
> That could be a different patch or fix here, I am ok for both.
>
> Thanks,
> Shihao
>
> <siblings.spec>

Hi Shihao,

Thanks for reporting a new deadlock scenario. After some investigation, I realized that there are more deadlock scenarios because of the current ALTER TABLE architecture. When altering a partitioned table and its partitions, ALTER TABLE needs to lock them top-down. If a concurrent session performs an operation that locks them bottom-up, a deadlock may occur. Besides generate_partition_qual(), which you pointed out, it seems ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL also locks a partition before its parent, and I believe there are more cases.

This makes me reconsider the fix. I don’t feel we should fix these deadlock scenarios one by one. Instead, I’d pursue a common solution to address this type of deadlock, though I haven’t come up with a clear idea yet.

I don’t think this patch is urgent, because these deadlock problems have been there for many years, and few people have complained about them. In practice, I think these deadlocks should be hard to hit, and even if one occurs, retry should usually work. So, I’d hold off on this patch for a while.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/