Re: not null constraints, again

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2025年4月16日周三 03:12写道：

> On 2025-Apr-15, Tender Wang wrote:
>
> > I thought further about the lockmode calling find_inheritance_children
> > in ATPrepAddPrimaryKey.
> > What we do here? We first get oids of children, then check the if the
> > column of children has marked not-null, if not, report an error.
> > No operation here on children. I check other places that call
> > find_inheritance_children, if we have operation on children, we usually
> pass
> > Lockmode to find_inheritance_children, otherwise pass NoLock.
>
> Hmm, I'm wary of doing this, although you're perhaps right that there's
> no harm. If we do need to add a not-null constraint on the children,
> surely we'll acquire a stronger lock further down the execution chain.
> In principle this sounds a good idea though. (I'm not sure about doing
> SearchSysCacheAttName() on a relation that might be dropped
> concurrently; does dropping the child acquire lock on its parent? I
> suppose so, in which case this is okay; but still icky. What about
> DETACH CONCURRENTLY?)
>

Yes, I'm also wary of doing this.
Although NoLock may fix this issue, I feel it will trigger other problems,
such as the scenario you listed above.

> However, I've also been looking at this and realized that this code can
> have different structure which may allows us to skip the
> find_inheritance_children() altogether. The reason is that we already
> scan the parent's list of columns searching for not-null constraints on
> each of them; we only need to run this verification on children for
> columns where there is none in the parent, and then only in the case
> where recursion is turned off.

> So I propose the attached patch, which also has some comments to
> hopefully explain what is going on and why. I ran Tom's test script a
> few hundred times in a loop and I see no deadlock anymore.
>

No objection from me. The comments may need a little polishing.

--
Thanks,
Tender Wang