Re: support virtual generated column not null constraint - Mailing list pgsql-hackers

Navneet Kumar <thanit3111@gmail.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;