Re: support virtual generated column not null constraint

Hi,

forget to add hackers to cc.

Xuneng Zhou <xunengzhou(at)gmail(dot)com> 于2025年3月8日周六 12:10写道：

>
>
> Navneet Kumar <thanit3111(at)gmail(dot)com> 于2025年3月8日周六 02:09写道：
>
>>
>>
>>> This scenario fails
>>> 1. CREATE TABLE person (
>>> id INT GENERATED BY DEFAULT AS IDENTITY,
>>> first_name VARCHAR(50) NOT NULL,
>>> last_name VARCHAR(50) NOT NULL
>>> );
>>>
>>> 2. INSERT INTO person (first_name, last_name)
>>> VALUES ('first', 'last');
>>>
>>> 3. ALTER TABLE person
>>> ADD COLUMN full_name VARCHAR(100) GENERATED ALWAYS AS (first_name || ' '
>>> || last_name) VIRTUAL;
>>>
>>
>> Forgot to mention NOT NULL constraint in above query.
>>
>> 3. ALTER TABLE person
>> ADD COLUMN full_name VARCHAR(100) NOT NULL GENERATED ALWAYS AS
>> (first_name || ' ' || last_name) VIRTUAL;
>>
>> ERROR: column "full_name" of relation "person" contains null values
>>
>>
>
> I did some debugging for this error. It is reported in this function:
>
> */**
>
> * * ATRewriteTable: scan or rewrite one table*
>
> * **
>
> * * A rewrite is requested by passing a valid OIDNewHeap; in that case,
> caller*
>
> * * must already hold AccessExclusiveLock on it.*
>
> * */*
>
> static void
>
> *ATRewriteTable*(AlteredTableInfo **tab*, Oid *OIDNewHeap*)
>
> {
>
>
>
> .......
>
>
> * /* Now check any constraints on the possibly-changed tuple */*
>
> econtext->ecxt_scantuple = insertslot;
>
>
> foreach(l, notnull_attrs)
>
> {
>
> int attn = lfirst_int(l);
>
>
> if (*slot_attisnull*(insertslot, attn + 1))
>
> {
>
> Form_pg_attribute attr = *TupleDescAttr*(newTupDesc,
> attn);
>
>
> ereport(ERROR,
>
> (*errcode*(ERRCODE_NOT_NULL_VIOLATION),
>
> *errmsg*("column \"%s\" of relation \"%s\"
> contains null values",
>
> NameStr(attr->attname),
>
> RelationGetRelationName(oldrel)),
>
> *errtablecol*(oldrel, attn + 1)));
>
> }
>
> }
>
> .......
>
> }
>
>
> If this error is unexpected, I think the issue is that when adding a NOT
> NULL constraint to a regular column, pg scans the table to ensure no NULL
> values exist. But for virtual columns, there are no stored values to scan.
> Maybe we should add some condition like this? Then checking not null at
> runtime.
>
>
> * /* Skip NOT NULL validation for virtual generated columns during table
> rewrite */*
>
> if (TupleDescAttr(newTupDesc, attn)->attgenerated ==
> ATTRIBUTE_GENERATED_VIRTUAL)
>
> continue;
>