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(at)kurilemu(dot)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.