Re: NOT NULL NOT ENFORCED

On 04.09.25 17:20, jian he wrote:
> On Thu, Sep 4, 2025 at 8:00 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>>
>>> @@ -1272,33 +1294,41 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
>>>         * Reproduce not-null constraints, if any, by copying them.  We do this
>>>         * regardless of options given.
>>>         */
>>> -     if (tupleDesc->constr && tupleDesc->constr->has_not_null)
>>> -     {
>>> -             List       *lst;
>>> +     lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
>>> +                                                                             true);
>>> +     cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
>>>
>>> -             lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
>>> -                                                                                     true);
>>
>>> +     /*
>>> +      * When creating a new relation, marking the enforced not-null constraint as
>>> +      * not valid doesn't make sense, so we treat it as valid.
>>> +     */
>>> +     foreach_node(Constraint, nnconstr, lst)
>>> +     {
>>> +             if (nnconstr->is_enforced)
>>> +             {
>>> +                     nnconstr->skip_validation = false;
>>> +                     nnconstr->initially_valid = true;
>>> +             }
>>> +     }
>>
>> Hmmm, this bit here (making constraints as valid if they're not valid in
>> the source table) looks like a fix for the existing code.  I think it
>> should be a separate patch, perhaps back-patchable to 18.  Or maybe I'm
>> missing something ...?
>>
> 
> it's indeed a bug, which was introduced
>
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
> 
> attached is the fix, also added a test on create_table_like.sql

I have committed this fix.